locked
Cannot insert data type datatime into database. RRS feed

  • 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!

    Thursday, June 6, 2013 1:38 PM

Answers

  • 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...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 6, 2013 2:21 PM

All replies

  • 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...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 6, 2013 2:21 PM
  • 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!

    Friday, June 7, 2013 11:02 PM