Syntax Error in Query Expression "Order ID" Blah Blah

[guente]

Please im having a syntax error in my code. Please Help
When i click on the add button it give me syntax error in query expression "order Id" blah bla

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim cmd As New OleDb.OleDbCommand
If con.State = ConnectionState.Closed Then
            'open connection if it is not yet open
            con.Open()
        End If

        cmd.Connection = con

        'Checks whether you want to add new or update records
        If OrdID.Tag & "" = "" Then
            'Add new 
            'Add data to table
            Try
                cmd.CommandText = "INSERT INTO Sales(Order_Id, Order_Category, Order_Date, Order_Rate, Order_Quantity, Order_Amount) " & _
                " VALUES(" & OrdID.Text & ",'" & OrdCat.Text & "','" & _
                CStr(OrdDate.Text) & "','" & OrdRte.Text & "','" & _
                OrdQty.Text & "','" & OrdAmt.Text & "')"
                cmd.ExecuteNonQuery()
                MsgBox("Record Sucessfully Created", MsgBoxStyle.Information)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
        'Update data in table
        Try
            cmd.CommandText = "UPDATE Sales " & _
                        " SET Order_Id=" & OrdID.Text & _
                        ", Order_Category='" & OrdCat.Text & "'" & _
                        ", Order_Date='" & CStr(OrdDate.Text) & "'" & _
                        ", Order_Rate='" & OrdRte.Text & "'" & _
                        ", Order_Quantity='" & OrdQty.Text & "'" & _
                        ", Order_Amount='" & OrdAmt.Text & "'" & _
                        " WHERE Order_Id='" & OrdID.Tag
            cmd.ExecuteNonQuery()
            MsgBox("Record Sucessfully Updated", MsgBoxStyle.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


        'Refresh data in list
        RefreshDataGrid()

        'Clear form
        btnClear.PerformClick()
        'Close connection
        con.Close()

    End Sub

[M.Waqas Aslam]

could please provide error message text.

[pritaeas]

syntax error in query expression "order Id

Output cmd.CommandText, there is an error in your query.

Your error is here: Order_Id=" & OrdID.Text & "

It needs to be this: Order_Id='" & OrdID.Text &"'

You simply missed quotes for Order_Id

[Reverend Jim]

That would be true if OrdID is a non-numeric field. It is probably numeric and does not require single quotes. But I can't say for sure without seeing the table definition.

 cmd.CommandText = "UPDATE Sales " & _
    " SET Order_Id=" & OrdID.Text & _
    ", Order_Category='" & OrdCat.Text & "'" & _
    ", Order_Date='" & CStr(OrdDate.Text) & "'" & _
    ", Order_Rate=" & OrdRte.Text & _
    ", Order_Quantity=" & OrdQty.Text & _
    ", Order_Amount=" & OrdAmt.Text & _
    " WHERE Order_Id=" & OrdID.Tag

Would be more appropriate. We still need to see the actual query string. And, of course, parameterized queries should be used.

[guente]

This is my how my Sales table look like.

Field Name Data Type
Order_Id Number
Order_Category Text
Order_Date Text
Order_Rate Text
Order_Quantity Text
Order_Amount Number

After clicking my add button, it shows 'syntax error in string in query expression 'Order_Id="

[Reverend Jim]

We have to see the query before we can tell you what is wrong with it.

Is this how query is supposed to complete: WHERE Order_Id='" & OrdID.Tag

I think it should be: WHERE Order_Id=" & OrdID.text""

[guente]

I dont know which is query.
I just posted the whole code in my add button. Please tell me where I can find the query. Im still learning vb.net

After taking a few corrections from this page, the update button now works without giving errors but my Add button still gives me that error.

Please help

[Reverend Jim]

If you aren't going to read what we post then we can't help you. pritaeas told you what to output.

[guente]

You have to take it eeasy with me bro. Im still learning vb and dont understand what pritaeas said. Kindly explain to me and I will be glad to follow. Thanks

[Reverend Jim]

You posted

 cmd.CommandText = "UPDATE Sales " & _
    " SET Order_Id=" & OrdID.Text & _
    ",    Order_Category='" & OrdCat.Text & "'" & _
    ",    Order_Date='" & CStr(OrdDate.Text) & "'" & _
    ",    Order_Rate='" & OrdRte.Text & "'" & _
    ",    Order_Quantity='" & OrdQty.Text & "'" & _
    ",    Order_Amount='" & OrdAmt.Text & "'" & _
    "  WHERE Order_Id='" & OrdID.Tag

Pritaeas asked you to post the value of cmd.CommandText. You are creating a SQL query by combining static text (literals) with dynamic text (textboxes). That query is then passed to the DBMS. In order to debug your query we have to see the actual query that is passed to the DBMS. So add the following line of code

Debug.WriteLine(cmd.CommandText)

and post the results here. Once you have the correct query we can show you how to do the same query using parameters. Using parameterized queries has several advantages. It

• helps avoid SQL injection attacks
• frees you from having to worry about when to use single quotes
• automatically corrects parameters that have embedded quotes
• makes the code easier to read and debug