Cannot insert data type datatime into database.

Question

User1916569363 posted

Hi, I have a problem with data datetime formate. Whenever i execute command Insert, there's error

"Conversion failed when converting datetime from character string."

My aim is want to insert data into table product (tbl_product) and at the same time insert into table stockin(tbl_stockin), however I have a problem with the datetime as above error:

Here is my code:

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        conn.Open()


        If is_blank() Then
            MsgBox("Please enter all require fields firest")
            Exit Sub
        Else
            sql = "INSERT INTO tbl_product VALUES('" & Int32.Parse(txtProID.Text) & "','" & txtProName.Text & "','" & Int32.Parse(txtProQty.Text) & "','" & Format(Me.DateTimePicker1.Value, "mm/dd/yyyy hh:mm:ss") & "')"
            MsgBox(Me.DateTimePicker1.Value)

            sql1 = "INSERT INTO tbl_stockin VALUES('" & Int32.Parse(txtProID.Text) & "','" & Format(Now, "mm/dd/yyy") & "','" & Int32.Parse(txtProQty.Text) & "','" & txtProductPrice.Text & "','" & txtTotalPrice.Text & "','" & txtProductCost.Text & "','" & txtTotalCost.Text & "')"
            MsgBox(Now)

            cmd = New SqlCommand(sql, conn)
            cmd1 = New SqlCommand(sql1, conn)
            da.InsertCommand = cmd
            da1.InsertCommand = cmd1

            If cmd.ExecuteNonQuery > 0 Then


                MsgBox("data inserted, thank you")
                If cmd1.ExecuteNonQuery > 0 Then
                    MsgBox("DATA1 ALSO INSERTED")

                End If
                conn.Close()
                clearAll()
                Frm_itemMaster_Load(Nothing, Nothing)




            Else
                MsgBox("no data inserted")

            End If
        End If


    End Sub

I'm not so much knowledge about VB.Net and SQL Server,

Thank you!

Answer 1

User551462331 posted

first of all u should not pass values to sql query like that... u should use the sqlparameter

anyways, regarding this issue, u can try this

sql = "INSERT INTO tbl_product VALUES('" & Int32.Parse(txtProID.Text) & "','" & txtProName.Text & "','" & _
 Int32.Parse(txtProQty.Text) & "','" & Format(Me.DateTimePicker1.Value, "dd/MM/yyyy hh:mm:ss") & "')"

sql1 = "INSERT INTO tbl_stockin VALUES('" & Int32.Parse(txtProID.Text) & "','" & _ 
Format(Now, "dd/MM/yyyy") & "','" & Int32.Parse(txtProQty.Text) & "','" & txtProductPrice.Text & "','" & txtTotalPrice.Text & "','" & txtProductCost.Text & "','" & txtTotalCost.Text & "')"

hope this helps...

Answer 2

User1916569363 posted

Every thank you for your reply, I already tested and it work fine! :D

Anyways, I still need your explanation what the two are different, I meant why use Format(Me.DateTimePicker1.Value, "dd/MMyyyy hh:mm:ss")  is different from Format(Now, "dd/MM/yyyy")?

And what is the best method that always work well with datetime? Because it is so troublesome with working with datetime data type, and I am quite new to Vb.NET and SQL Server.

Thanks!