syntax error (missing operator) while saving my data

[master_2013]

i am getting an error message while saving my data to ms access database in vb.net
syntax error (missing operator) in query expression

here is the code

Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim a As Integer
        a = MsgBox("Do you want to Save the details", MsgBoxStyle.OkCancel, Me.Text)
        If a = vbOK Then

            If TextBox2.Text = "" Then
                MsgBox("Please enter Product Id", MsgBoxStyle.Exclamation, Me.Text)
            ElseIf TextBox6.Text = "" Then
                MsgBox("Please enter Customer Name", MsgBoxStyle.Exclamation, Me.Text)
            ElseIf ComboBox2.Text = "" Then
                MsgBox("Please select a product", MsgBoxStyle.Exclamation, Me.Text)
            ElseIf DateTimePicker1.Text = "" Then
                MsgBox("Please enter correct date ", MsgBoxStyle.Exclamation, Me.Text)
            ElseIf TextBox3.Text = "" Then
                MsgBox("Please enter no of quantities", MsgBoxStyle.Exclamation, Me.Text)
            ElseIf TextBox4.Text = "" Then
                MsgBox("Please enter correct amount", MsgBoxStyle.Exclamation, Me.Text)

            Else

                Try
                    Dim cmd As New OleDbCommand("Insert into products Values(" & Label9.Text & ",'" & TextBox2.Text & ",'" & TextBox6.Text & ",'" & ComboBox2.Text & "','" & DateTimePicker1.Text & "','" & TextBox3.Text & ",'" & TextBox4.Text & ",'" & TextBox5.Text & "')", om)
                    om.Open()
                    Dim dr As OleDbDataReader = cmd.ExecuteReader
                    While dr.Read

                        TextBox2.Text = dr(0)
                        TextBox6.Text = dr(1)
                        ComboBox2.Text = dr(2)
                        DateTimePicker1 = dr(3)
                        TextBox3.Text = dr(4)
                        TextBox4.Text = dr(5)
                        TextBox5.Text = dr(6)
                        Label9.Text = dr(7)
                    End While
                    cmd.ExecuteNonQuery()
                    MsgBox("Data Successfully Saved!", MsgBoxStyle.Information, Me.Text)
                    om.Close()

                    Me.Close()

                    Dim o As New Admission
                    o.MdiParent = Welcome.ActiveForm
                    o.Show()

                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Critical, Me.Text)
                    If om.State = ConnectionState.Open Then
                        om.Close()
                    End If
                End Try
            End If
        End If
    End Sub

 

[IanRyder]

Hi,

You need to learn how to implement basic debugging techniques. In this case there is something that Access does not like about your SQL string so you need to look at what you are creating. Here is an interpretation of your string:-

MsgBox(("Insert into products Values(" & "Test" & ",'" & "Test" & ",'" & "Test" & ",'" & "Test" & "','" & "Test" & "','" & "Test" & ",'" & "Test" & ",'" & "Test" & "')"))

All I have done here is replace all your controls with the literal "Test" so that it can be seen in a Message box. So, you tell me, what's wrong with it?

Once you understand where you went wrong you will realise that this is a great example of why you should never use string concatenation when building your SQL queries. Aside from this specific reason, there are other specific reasons why you should be using Parameters rather than string concatenation when building SQL queries. Have a read of this blog by jmcilhinney:-

John McIlhinney's .NET Developer Blog: Using Parameters in ADO.NET

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

I also have to question why you would be using a data reader with an INSERT statement. The whole point of a data reader is to read the data from the result set of a query but you have no query so there's no result set to read. If you're executing a SQL statement that is not a query then you should be calling ExecuteNonQuery and not trying to read a non-existent result set.