Syntax error operator missing in expression

[wilsonchama]

Hi guys,

I am trying to build an applicatio to keep student records for a small schoo. I have a table in acces 2010 and the application is with vb.net 2008(Visual studio).

The table has eight fields (stNumber, stDOB, stGrade, stClass, stAddress, stGender and stPhoto. and the commant button on the form has the following code.

Private Sub btnSaveRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveRecord.Click
            Dim cn As New OleDb.OleDbConnection
            cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "/Schooling.mdb"
            cn.Open()
            Try
                Dim arrImage() As Byte
                Dim strImage As String
                Dim myMs As New IO.MemoryStream

                If Not IsNothing(Me.picStudent.Image) Then
                    Me.picStudent.Image.Save(myMs, Me.picStudent.Image.RawFormat)
                    arrImage = myMs.GetBuffer
                    strImage = "?"
                Else
                    arrImage = Nothing
                    strImage = "NULL"
                End If

                Dim myCmd As New OleDb.OleDbCommand
                myCmd.Connection = cn
                myCmd.CommandText = "INSERT INTO tblStudents([stNumber],[stNames],[stDOB],[stGrade],[stClass],[stAddress],[stGender],stPhoto)" & _
                                  " VALUES(" & Me.txtStNumber.Text & ",'" & _
                                  Me.txtNames.Text & "'," & _
                                  Me.DateBirth.Text & "," & _
                                  Me.txtGrade.Text & "," & _
                                  Me.txtClass.Text & "," & _
                                  Me.txtAddress.Text & "," & _
                                  Me.cmbGender.Text & strImage & ")"
                If strImage = "?" Then
                    myCmd.Parameters.Add(strImage, OleDb.OleDbType.Binary).Value = arrImage
                End If
                myCmd.ExecuteNonQuery()
                cn.Close()
                MsgBox("Data save successfully!")
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
    End Class

The code is intended to insert the records that are entered on form controls. But unfortunately, when I click the button, the application generates an error that reads = syntax error(missing operator) in expression '22 lufutizi street'.
'22 lufutizi street' is an address entered in the address textbox (txtAddress) on the form which intended to be inserted in the table field called stAddress.
It seems that the line "Me.txtAddress.Text & "," & _" might have a problem. But I do not know how to fix that and what the cause of an error is.

I need someone's help.
Thanks in advance.

[djjeavons]

Hi

When using inline SQL you need to enclose each of your field types in the correct characters. For example, text data needs to be enclosed in ', dates (for Access) need to be enclosed with # and numbers do not need to be enclosed in anything.

So in your SQL statement, you are not enclosing student number in anything (which is correct) and you are enclosing Names in ' which I assume is correct. All other fields however are not being enclosed with anything. I would assume stDOB is a date so needs to use # and stAddress is text so needs to use '

Alternatively, consider using parameters for the whole statement to avoid the need to worry about these conventions:

myCmd.CommandText = "INSERT INTO tblStudents([stNumber],[stNames],[stDOB],[stGrade],[stClass],[stAddress],[stGender],stPhoto) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
myCmd.Parameters.AddWithValue(?, txtStNumber.Text)
myCmd.Parameters.AddWithValue(?, txtNames.Text)
'etc...

Make sure that you supply the parameters in the same order as you have specified them in the INSERT statement.

HTH

[wilsonchama]

Hi djjeavons, I tried to use parameters previously, the rest of the data was inserting, but the image was not, so if you can give me an insight on such.

[djjeavons]

This code snippet demonstrates how to write an image to an Ole Object field.