Problem Updating/Editing Records to Access Database in VB.Net 2008

See more:

VB

MS-Access

I'm having a problem updating records from my VB.Net application to the database (Access Database). I've tried all sorts of update technique and i keep getting either "syntax error in update statement" or "data mismatch in criteria expression".

heres the code i'm using:

Public Function save1stsemester()
    'this code updates 1st semester records to database
    Dim ds As New DataSet
    Dim dbSource As String
    Dim conn As New OleDb.OleDbConnection
    Dim dbcursor As Integer = 0
    Dim da As New OleDb.OleDbDataAdapter
    Dim cmdUpdate As New OleDb.OleDbCommand
    Dim msg1 As String
    Dim style As MsgBoxStyle
    Dim result As MsgBoxResult
    Dim title, remarks As String
    Dim totalgrade As Integer = CInt(txtTotalMK.Text)
    Dim totalload As Integer = CInt(txtTotalLoad.Text)
    Dim gpadecimal As Decimal
    gpadecimal = CDec(lblGPA1.Text)
    title = "Success"
    style = MsgBoxStyle.Information
    Call allremarks() ' this function gathers all the remarks to a single variable.
    remarks = allremarks()
    Dim failflag As Boolean
    If checkflag100() = True Then 'True means all courses were passed then turn on Flag
    failflag = True
    Else
    failflag = False
    End If
    If conn.State = ConnectionState.Open Then
    GoTo cont
    Else
    If conn.State = ConnectionState.Closed Then
    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source =C:\CGPA\e-CGPA Stat\e-CGPA Stat\bin\Debug\Scoredata.mdb;Persist Security Info=False"
    conn.ConnectionString = dbProvider & dbSource
    conn.Open()
    da.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM 100Level1stSemester WHERE MatNO like '%" + cmbMATNO.Text + "%'")
    da.SelectCommand.Connection = conn
    Dim mycomand As New OleDb.OleDbCommandBuilder(da)
    da.Fill(ds, "100Level1stSemester")
    cont: If cmbCourseLevel.SelectedItem = "100 Level" Then
    ' Try
    cmdUpdate.CommandText = "UPDATE 100Level1stSemester " & _
    "SET MatNo = '" & cmbMATNO.Text & "'" & _
    ", FName = '" & cmbStatFName.Text & "'" & _
    ", MName = '" & cmbStatInitial.Text & "'" & _
    ", SName = '" & cmbStatSname.Text & "'" & _
    ", STA110 = '" & txtGR1.Text & "'" & _
    ", MTH110 = '" & txtGR2.Text & "'" & _
    ", MTH112 = '" & txtGR3.Text & "'" & _
    ", ACC111 = '" & txtGR4.Text & "'" & _
    ", GST111 = '" & txtGR5.Text & "'" & _
    ", GST112 = '" & txtGR6.Text & "'" & _
    ", GST123 = '" & txtGR7.Text & "'" & _
    ", [STA110-SCORE] = '" & txtSC1.Text & "'" & _
    ", [MTH110-SCORE] = '" & txtSC2.Text & "'" & _
    ", [MTH112-SCORE] = '" & txtSC3.Text & "'" & _
    ", [ACC111-SCORE] = '" & txtSC4.Text & "'" & _
    ", [GST111-SCORE] = '" & txtSC5.Text & "'" & _
    ", [GST112-SCORE] = '" & txtSC6.Text & "'" & _
    ", [GST123-SCORE] = '" & txtSC7.Text & "'" & _
    ", [Tot-Grade-Point] = '" & totalgrade & "'" & _
    ", [Tot-Credit-Load] = '" & totalload & "'" & _
    ", [1stSemesterGPA] = '" & gpadecimal & "'" & _
    ", Remarks = '" & remarks & "'" & _
    ", Flag = '" & failflag & "'" & _
    " WHERE MatNo = '" & cmbMATNO.Text & "'"
    cmdUpdate.CommandType = Data.CommandType.Text
    cmdUpdate.Connection = conn
    cmdUpdate.ExecuteNonQuery()
    cmdUpdate.Dispose()
    conn.Close()
    msg1 = "100 Level 1st semester exam score and grades updated successfully."
    result = MsgBox(msg1, style, title)
    cmdVerifySem1.Enabled = False
    Grp1stSEM.Enabled = True
    'Catch
    MessageBox.Show("An error occured while updating the student's 100 Level scores! Duplicate entry was detected in the database. Ensure that the student's scores has not been entered before and try again.")
    'End Try
    End If
    End If
    End If
    End Function

I'm using Visual Studio.Net 2008 (VB.Net) and Access 2003.

I commented the try and catch statement to enable me trace where the error is comming from.
The error flags at the code: (cmdUpdate.ExecuteNonQuery)

Please help.

Thanks