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

At line 80 add the following

Debug.WriteLine(cmdUpdate.CommandText)

and post the output here. One further comment, a GOTO is a rarely used feature. There are only a small number of cases where a GOTO is warranted. This isn't one of them.

Replace

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

with

If conn.State <> ConnectionState.Open 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)
End If

If cmbCourseLevel.SelectedItem = "100 Level" Then

Thanks for the hint. still had the same error message

However heres the output after putting the debug.WriteLine(cmdUpdate.CommandText).

The output below is from the immediate window.

UPDATE 100Level1stSemester SET MatNo = 'SCN10024', FName = 'Marshall', MName = 'G', SName = 'Maduka', STA110 = 'A', MTH110 = 'D', MTH112 = 'A', ACC111 = 'B', GST111 = 'A', GST112 = 'B', GST123 = 'A', [STA110-SCORE] = '89', [MTH110-SCORE] = '45', [MTH112-SCORE] = '78', [ACC111-SCORE] = '65', [GST111-SCORE] = '78', [GST112-SCORE] = '64', [GST123-SCORE] = '89', [Tot-Grade-Point] = '30', [Tot-Credit-Load] = '18', [1stSemesterGPA] = '1.67', Remarks = ' ', Flag = 'True' WHERE MatNo = 'SCN10024'
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>e-CGPA Stat.vshost.exe</AppDomain><Exception><ExceptionType>System.Data.OleDb.OleDbException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Data type mismatch in criteria expression.</Message><StackTrace> at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp;amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp;amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp;amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at e_CGPA_Stat.ScoreEditForm.save1stsemester() in C:\CGPA\e-CGPA Stat\e-CGPA Stat\ScoreEditForm.vb:line 5565
at e_CGPA_Stat.ScoreEditForm.cmd2ndSave_Click(Object sender, EventArgs e) in C:\CGPA\e-CGPA Stat\e-CGPA Stat\ScoreEditForm.vb:line 5595
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at e_CGPA_Stat.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp;amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp;amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp;amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at e_CGPA_Stat.ScoreEditForm.save1stsemester() in C:\CGPA\e-CGPA Stat\e-CGPA Stat\ScoreEditForm.vb:line 5565
at e_CGPA_Stat.ScoreEditForm.cmd2ndSave_Click(Object sender, EventArgs e) in C:\CGPA\e-CGPA Stat\e-CGPA Stat\ScoreEditForm.vb:line 5595
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp;amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at e_CGPA_Stat.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>

Try removing the single quotes from around the numeric fields. You should only use single quotes for string type database values.

Woww thanks a million. It worked. I removed the single quotes from all the numeric fields and Boolean Field as well.

I ran the application and the updates reflected in the database. I'm very grateful.

Although i've been advised to learn the parameterized method, which i find a bit uneasy.

Thanks again Reverend Jim

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.