Member Avatar for 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.

Edited by wilsonchama
  • 2 Contributors
  • 3 Replies
  • 312 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by djjeavons

Dani AI

Generated

The error shown by ("syntax error (missing operator) in expression '22 lufutizi street'") happens because Access is seeing the raw address token without string delimiters. Inline SQL requires text values to be quoted (single quotes) and dates to be delimited with # — which is why ' parameter recommendation is the correct direction. Parameters eliminate the quoting/format headaches and protect against injection.

Practical checklist and gotchas for Access + OleDb:

  • Use a parameterized INSERT with positional placeholders (OleDb uses ?). The order of Parameters.Add must exactly match the ? order.
  • Create the image byte array with MemoryStream.ToArray() (not GetBuffer()) so the array length is exact.
  • Add the image parameter as OleDbType.Binary and set the Size to the byte array length; if there is no image pass DBNull.Value (not Nothing).
  • For dates pass a DateTime value (DateTimePicker.Value or DateTime.TryParse on the text) and use OleDbType.Date. For numeric fields pass the correct numeric type.
  • If concatenating SQL (not recommended) make sure to escape single quotes (replace "'" with "''") and wrap text/date values correctly.

Example (VB.NET) pattern to follow:

Using cmd As New OleDb.OleDbCommand("INSERT INTO tblStudents([stNumber],[stNames],[stDOB],[stGrade],[stClass],[stAddress],[stGender],[stPhoto]) VALUES (?,?,?,?,?,?,?,?)", cn)
    cmd.Parameters.Add(New OleDb.OleDbParameter("p1", OleDb.OleDbType.VarChar, 50)).Value = txtStNumber.Text
    cmd.Parameters.Add(New OleDb.OleDbParameter("p2", OleDb.OleDbType.VarChar, 100)).Value = txtNames.Text
    cmd.Parameters.Add(New OleDb.OleDbParameter("p3", OleDb.OleDbType.Date)).Value = DateTime.Parse(DateBirth.Text)
    cmd.Parameters.Add(New OleDb.OleDbParameter("p4", OleDb.OleDbType.Integer)).Value = Integer.Parse(txtGrade.Text)
    cmd.Parameters.Add(New OleDb.OleDbParameter("p5", OleDb.OleDbType.VarChar, 50)).Value = txtClass.Text
    cmd.Parameters.Add(New OleDb.OleDbParameter("p6", OleDb.OleDbType.VarChar, 200)).Value = txtAddress.Text
    cmd.Parameters.Add(New OleDb.OleDbParameter("p7", OleDb.OleDbType.VarChar, 10)).Value = cmbGender.Text
    If imgBytes IsNot Nothing Then
        cmd.Parameters.Add(New OleDb.OleDbParameter("p8", OleDb.OleDbType.Binary, imgBytes.Length)).Value = imgBytes
    Else
        cmd.Parameters.Add(New OleDb.OleDbParameter("p8", OleDb.OleDbType.Binary)).Value = DBNull.Value
    End If
    cmd.ExecuteNonQuery()
End Using

Extra notes: double-check parameter order, prefer DateTimePicker.Value over .Text, dispose streams with Using, and remember Access OLE Object fields may bloat .mdb files — for larger apps store images on disk and save paths in the DB.

Recommended Answers

All 3 Replies

Member Avatar for 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

Edited by djjeavons
Member Avatar for 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.

Member Avatar for djjeavons

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.