CODEWITHSUNDEEP
sql-servervb.netdatetime
Hakim Johari
Hakim Johari

Reputation: 13

How to insert a date into SQL Server using vb.net

This is my code that will insert data into a datagridview first then into database.

When I run the code, I get the error

operand type clash bit is incompatible with date

Can anyone help me?

Thanks in advance

    populate(txtRejectID.Text, comboBoxCardType.Text, txtEmbossName.Text, txt6CardNum.Text, txt4CardNum.Text, txtQuantity.Text, comboBoxErrorDesc.Text, embossDate.Text, txtStatus.Text)

    For Each row As DataGridViewRow In DataGridView1.Rows
            Dim query As String = "INSERT INTO dbo.RejectCard VALUES (@RejectID, @Card_Type, @Emboss_Name, @Card_Number, @Quantity, @Error_Description, @Emboss_Date, @Status)"

        Using conn As New SqlConnection(connString)
            Using cmd As New SqlCommand(query, conn)

                cmd.Parameters.AddWithValue("@RejectID", row.Cells("rejectid").Value)
                cmd.Parameters.AddWithValue("@Card_Type", row.Cells("cardtype").Value)
                cmd.Parameters.AddWithValue("@Emboss_Name", row.Cells("embossname").Value)
                cmd.Parameters.AddWithValue("@Card_Number", row.Cells("cardnumber").Value)
                cmd.Parameters.AddWithValue("@Quantity", row.Cells("quantity").Value)
                cmd.Parameters.AddWithValue("@Error_Description", row.Cells("errordescription").Value)
                cmd.Parameters.AddWithValue("@Emboss_Date", row.Cells("emboss_Date").Value = embossDate.Value.Date.ToString("dd/MM/yyyy"))
                cmd.Parameters.AddWithValue("@Status", row.Cells("status").Value)

                Try
                    conn.Open()
                    cmd.Connection = conn
                    cmd.ExecuteNonQuery()

                Catch ex As Exception
                    MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
                Finally
                    conn.Close()
                End Try
            End Using
        End Using
    Next

    MessageBox.Show("Records inserted.")
End Sub

Upvotes: 0

Views: 1689

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54417

You insert dates exactly the same way as you insert anything else. The problem is that you're not inserting a Date. You're inserting a Boolean. Look at your code. This is the value your inserting:

row.Cells("emboss_Date").Value = embossDate.Value.Date.ToString("dd/MM/yyyy")

That's an equality comparison. The result of an equality comparison is always a Boolean, i.e. True if the values are equal and False if they're not. If you want to insert a Date then provide a Date, not a comparison between a value from a grid row and a String.

Why aren't you just doing for that parameter exactly what you're doing for all the rest? You clearly think you're achieving something there but I can assure you that you're not. If what you're trying to do is drop the time from your DateTime value then you do that by getting the Date property, not by converting the Date to a String.

Upvotes: 2

Related Questions