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
