VB.NET database data passing error

Question

I get the error message

"Error while inserting record o the table… Incorrect syntax near the keyword ‘return’.Incorrect syntax near ‘,’ ."

Public Class Form10
    Dim con As New SqlClient.SqlConnection
    Dim cmd, com As New SqlClient.SqlCommand
    Dim sqlda As New SqlClient.SqlDataAdapter
    Dim ds As New DataSet


 Private Sub Form10_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            con.ConnectionString = "data source=.\sqlexpress;initial catalog =pharmacy;Integrated Security =true"
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Connection Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error)
        End Try
    End Sub

     Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclear.Click
        txtdcode.Text = ""
        txtrinvo.Text = ""
        txtcreg.Text = ""
        txtprice.Text = ""
        txtqty.Text = ""
        txttamount.Text = ""
        DateTimePicker1.Text = ""
    End Sub

     Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
        con.Open()
        Try
            sqlda = New System.Data.SqlClient.SqlDataAdapter("SELECT * FROM return where r_invoice_no='" & txtrinvo.Text & "'", con)
            ds.Clear()
            sqlda.Fill(ds, "return")
            txtdcode.Text = ds.Tables("return").Rows(0).Item(0)
            txtcreg.Text = ds.Tables("return").Rows(0).Item(1)
            txtprice.Text = ds.Tables("return").Rows(0).Item(2)
            txtqty.Text = ds.Tables("return").Rows(0).Item(3)
            txttamount.Text = ds.Tables("return").Rows(0).Item(4)
            DateTimePicker1.Text = ds.Tables("return").Rows(0).Item(5)

            ProgressBar1.Visible = True
            ProgressBar1.Minimum = 0
            ProgressBar1.Maximum = 10000
            ProgressBar1.Value = 0

            Dim I As Integer
            For I = ProgressBar1.Minimum To ProgressBar1.Maximum
                ProgressBar1.Value = I
            Next

            ProgressBar1.Visible = False

        Catch ex As IndexOutOfRangeException
            MsgBox("Type correct Return Invoice Number to search.." & ex.Message, MsgBoxStyle.Critical, "TRY AGAIN")
        Finally
            con.Close()
        End Try
    End Sub



Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
        con.Open()
        Try
            If txtdcode.Text = "" Or txtrinvo.Text = "" Or txtcreg.Text = "" Or txtprice.Text = "" Or txtqty.Text = "" Or txttamount.Text = "" Or DateTimePicker1.Text = "" Then
                MsgBox("You must have to fill all the Book details.")
            Else

                Dim strInst As String = "INSERT INTO return(drug_code,c_reg_no,Sale_price,qty,Tot_amount,date,r_invoice_no)VALUES('" & txtdcode.Text & "','" & txtcreg.Text & "','" & txtprice.Text & "','" & txtqty.Text & "','" & txttamount.Text & "','" & DateTimePicker1.Text & "','" & txtrinvo.Text & "')"
                Dim cmd_Insert As New System.Data.SqlClient.SqlCommand(strInst, con)
                cmd_Insert.ExecuteNonQuery()

                ProgressBar1.Visible = True
                ProgressBar1.Minimum = 0
                ProgressBar1.Maximum = 10000
                ProgressBar1.Value = 0

                Dim I As Integer
                For I = ProgressBar1.Minimum To ProgressBar1.Maximum
                    ProgressBar1.Value = I
                Next

                MsgBox("New Recored has been Added", MsgBoxStyle.Information)

                ProgressBar1.Visible = True

                txtdcode.Text = ""
                txtrinvo.Text = ""
                txtcreg.Text = ""
                txtprice.Text = ""
                txtqty.Text = ""
                txttamount.Text = ""
                DateTimePicker1.Text = ""

            End If
        Catch ex As Exception

            MessageBox.Show("Error while inserting record on table..." & ex.Message, "Error Inserting")
        Finally
            con.Close()
        End Try

    End Sub



Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        Try
            con.Open()
            com.Connection = con
            com.CommandText = "UPDATE return SET drug_code ='" & txtdcode.Text & "',c_reg_no='" & txtcreg.Text & "',Sale_price='" & txtprice.Text & "',qty='" & txtqty.Text & "',Tot_amount='" & txttamount.Text & "',date='" & DateTimePicker1.Text & "'"
            com.ExecuteNonQuery()

            ProgressBar1.Visible = True
            ProgressBar1.Minimum = 0
            ProgressBar1.Maximum = 10000
            ProgressBar1.Value = 0

            Dim I As Integer
            For I = ProgressBar1.Minimum To ProgressBar1.Maximum
                ProgressBar1.Value = I
            Next

            MsgBox("Your rocord has been Updated", MsgBoxStyle.Information, "Update Records")

            ProgressBar1.Visible = False

            txtdcode.Text = ""
            txtrinvo.Text = ""
            txtcreg.Text = ""
            txtprice.Text = ""
            txtqty.Text = ""
            txttamount.Text = ""
            DateTimePicker1.Text = ""

        Catch ex As Exception
            MessageBox.Show("Error while updating password on table..." & ex.Message, "Insert Records")

        Finally
            con.Close()
        End Try
    End Sub



Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        Try
            con.Open()

            com.Connection = con
            com.CommandText = "Delete From return Where r_invoice_no=" & txtrinvo.Text
            com.ExecuteNonQuery()

            ProgressBar1.Visible = True
            ProgressBar1.Minimum = 0
            ProgressBar1.Maximum = 10000
            ProgressBar1.Value = 0

            Dim I As Integer
            For I = ProgressBar1.Minimum To ProgressBar1.Maximum
                ProgressBar1.Value = I
            Next

            MsgBox("Records are deleted with Return Invoice Number..." & txtrinvo.Text, MsgBoxStyle.Information, "Record  Deleted.")

            ProgressBar1.Visible = False

            txtdcode.Text = ""
            txtrinvo.Text = ""
            txtcreg.Text = ""
            txtprice.Text = ""
            txtqty.Text = ""
            txttamount.Text = ""
            DateTimePicker1.Text = ""

        Catch ex As InvalidCastException
            MessageBox.Show("Error while retrieving records on table..." & ex.Message, "Load Records")

        Finally
            con.Close()
        End Try
    End Sub

End Class

You should definetly concider SqlParameters. msdn.microsoft.com/en-us/library/… — the_lotus
– the_lotus, Commented May 8, 2013 at 16:38
return is a reserved word in SQL Server. Try putting square brackets around it in your SQL. [return] — George Mastros
– George Mastros, Commented May 8, 2013 at 16:41

George Mastros · Accepted Answer · 2013-05-08 16:58:39Z

Change this line:

Dim strInst As String = "INSERT INTO return(drug_code,c_reg_no,Sale_price,qty,Tot_amount,date,r_invoice_no)VALUES('" & txtdcode.Text & "','" & txtcreg.Text & "','" & txtprice.Text & "','" & txtqty.Text & "','" & txttamount.Text & "','" & DateTimePicker1.Text & "','" & txtrinvo.Text & "')"

To this:

Dim strInst As String = "INSERT INTO [return](drug_code,c_reg_no,Sale_price,qty,Tot_amount,date,r_invoice_no)VALUES('" & txtdcode.Text & "','" & txtcreg.Text & "','" & txtprice.Text & "','" & txtqty.Text & "','" & txttamount.Text & "','" & DateTimePicker1.Text & "','" & txtrinvo.Text & "')"

Notice the square brackets around the table name. Return is a reserved word in SQL Server, which is causing your error. I would recommend that you change the name of the table. If this is not possible, then you are stuck adding the square brackets every time you use the table.

Collectives™ on Stack Overflow

VB.NET database data passing error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related