1,105,340 Community Members

SQL Problem - System.Data.OleDb.OleDbException was unhandled

Member Avatar
Tobyjug2222
Light Poster
28 posts since Dec 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Sponsor
 
0
 

Hey Guys, I'm an A level student studdying Computer science, and hence using VB.NET for my coursework.
Therefore I'm very novice at VB!

While trying to update Data in a Microsoft Office, Access table (Office 2010) - I get this error. (In VB)
Bare in mind, I've read all this data from said table already, and that works perfectly.

I've tried stepping through my program and also checked the syntax on the SQL String, although all seems to be good!

Please, spare a moment to review the code below and see if you can find the problem with it. If needs be I can even upload my basic program for you to download, just request it please :)

Microsoft Access Table:

Untitled98
From LoginForm:

Private Sub LoginForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'This function sets up database path, when the form is being loaded
        ConnectionMaster.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Accounts.accdb" 'Sets connection string
        RefreshDataLogin() 'Calls refresh data
    End Sub

    Private Sub RefreshDataLogin()

        ''''''''''''''''''''''DataSetMaster.Tables("TblStaff").Clear() 'Clear data set, incase already values inside
        If Not ConnectionMaster.State = ConnectionState.Open Then
            ConnectionMaster.Open() 'Opens connection
        End If
        DataAdapterLogin = New OleDb.OleDbDataAdapter("Select * FROM TblStaff", ConnectionMaster) 'Selects all records from table
        DataAdapterLogin.Fill(DataSetMaster, "TblStaff") 'Fills dataset
        NumberOfUsers = DataSetMaster.Tables("TblStaff").Rows.Count - 1 'Sets total number of records
        ConnectionMaster.Close() 'Closes connection
    End Sub

From MainForm:

      Private Sub RefreshDataUser() 'For user edit/ delete

        LoginForm.DataSetMaster.Tables("TblStaff").Clear() 'Clear data set, incase already values inside
        If LoginForm.ConnectionMaster.State = ConnectionState.Open Then
            DataAdapterUser = New OleDb.OleDbDataAdapter("Select * FROM TblStaff", LoginForm.ConnectionMaster) 'Select all records from table
            DataAdapterUser.Fill(LoginForm.DataSetMaster, "TblStaff") 'Fill data set
            NumberOfUsers = LoginForm.DataSetMaster.Tables("TblStaff").Rows.Count - 1 'Set total number of users
            CurrentUser = 0
            UserToFill = ComboModifyUserUsername.SelectedIndex 'When changed selection, change data in combo box
            If UserToFill = -1 Then 'If variable = -1 then set to 0
                UserToFill = 0
            End If
            ComboModifyUserUsername.Items.Clear() 'Clears combo box
            Do While CurrentUser <= NumberOfUsers 'Loops to fill combo box
                ComboModifyUserUsername.Items.Add(LoginForm.DataSetMaster.Tables("TblStaff").Rows(CurrentUser).Item(0)) 'Displays data to combo box
                CurrentUser = CurrentUser + 1 'Adds 1 to variable
            Loop
            ModifyUsernameOrignal = LoginForm.DataSetMaster.Tables("TblStaff").Rows(UserToFill).Item(0) 'Displays data to textbox
            TxtModifyUserUsername.Text = ModifyUsernameOrignal
            TxtModifyUserPassword1.Text = LoginForm.DataSetMaster.Tables("TblStaff").Rows(UserToFill).Item(1) 'Displays data to textbox
            TxtModifyUserPassword2.Text = TxtModifyUserPassword1.Text 'Displays data to textbox
            CheckBoxModifyUserAdmin.Checked = LoginForm.DataSetMaster.Tables("TblStaff").Rows(UserToFill).Item(2) 'Displays data to Check box
            TxtModifyUserEmail.Text = LoginForm.DataSetMaster.Tables("TblStaff").Rows(UserToFill).Item(3) 'Displays data to textbox
            TxtModifyUserMobileNumber.Text = LoginForm.DataSetMaster.Tables("TblStaff").Rows(UserToFill).Item(4) 'Displays data to textbox
            CheckBoxModifyUserDiscontinued.Checked = LoginForm.DataSetMaster.Tables("TblStaff").Rows(UserToFill).Item(5) 'Displays data to Check box
            LoginForm.ConnectionMaster.Close() 'Closes connection
        Else
            LoginForm.ConnectionMaster.Open() 'Opens connection
            RefreshDataUser()
        End If

    End Sub

  Private Sub MakeSQLUser(ByVal SQLStringUserEdit As String) 'This function will apply SQL to databse, string built by other functions

        Dim CmdUser As New OleDb.OleDbCommand 'Set up a new command
        If Not LoginForm.ConnectionMaster.State = ConnectionState.Open Then 'Checks if connection already open
            LoginForm.ConnectionMaster.Open() 'Opens connection
        End If
        CmdUser.Connection = LoginForm.ConnectionMaster 'Set the connection for command
        CmdUser.CommandText = SQLStringUserEdit 'Set the command text to be arguments sent with function call
        CmdUser.ExecuteNonQuery() 'Apply the commands to the database
        LoginForm.ConnectionMaster.Close() 'Close connection
        CurrentUser = 0 'Display first user
        RefreshDataUser() 'Calls refresh data sub
    End Sub

    Private Sub Cmd_ModifyUserSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cmd_ModifyUserSave.Click 'For edit


        Dim Answer As Integer = MsgBox("Have you edited the current record?", MsgBoxStyle.YesNo, "Request") 'Asks if record has been updated or not
        If Answer = 6 Then 'If record has been updated then
            If Not TxtModifyUserPassword1.Text = TxtModifyUserPassword2.Text Then 'Check passwords match eachother
                MsgBox("Record has not been updated!" & vbNewLine & "The two passwords do not match!", MsgBoxStyle.Information, "Record not updated") 'Error for passwords not matching
                Exit Sub 'Cancel sub routine
            Else 'If passwords are matching then
                Dim SQLSend As String = "UPDATE TblStaff SET Username='" & TxtModifyUserUsername.Text & "', Password='sdfsdsdf'" & " WHERE Username='" & ModifyUsernameOrignal & "'"
                'Dim SQLSend As String = "UPDATE TblStaff SET Username='" & TxtModifyUserUsername.Text & "', Password='" & TxtModifyUserPassword1.Text & "', Admin=" & CheckBoxModifyUserAdmin.Checked & ", Email='" & TxtModifyUserEmail.Text & "', MobileNumber='" & TxtModifyUserMobileNumber.Text & "', Discontinued=" & CheckBoxModifyUserDiscontinued.Checked & " WHERE Username='" & ModifyUsernameOrignal & "'"
                MsgBox(SQLSend)
                MakeSQLUser(SQLSend) 'Calls SQL function with above string as parameter
                MsgBox("Record has been updated!", MsgBoxStyle.Information, "Record updated") 'Message box that record has been updated
            End If

        Else 'If record has not been updated then
            MsgBox("Record has not been updated!", MsgBoxStyle.Information, "Record not updated") 'Record not updated due to no change
        End If
    End Sub

When trying to step through the program to find the error, I get this returned:

OleDbException was unhandled
Syntax error in UPDATE statement.

And if I press "Copy exception detail to the clipboard"... I get:

System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147217900
  Message=Syntax error in UPDATE statement.
  Source=Microsoft Access Database Engine
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at StockControlSystem.MainForm.MakeSQLUser(String SQLStringUserEdit) in E:\School Work\Computing Work\Visual Basic Programs\CG4.DataSetMerge\StockControlSystem\MainForm.vb:line 201
       at StockControlSystem.MainForm.Cmd_ModifyUserSave_Click(Object sender, EventArgs e) in E:\School Work\Computing Work\Visual Basic Programs\CG4.DataSetMerge\StockControlSystem\MainForm.vb:line 228
       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& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr 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 Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at StockControlSystem.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly 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, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Many thanks, Toby.

Attachments Untitled.png 167.67KB
Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
1
 

Try this, as you are using reserved words for field names

 Dim SQLSend As String = "UPDATE TblStaff SET [Username]='" & TxtModifyUserUsername.Text & "', [Password]='sdfsdsdf'" & " WHERE [Username]='" & ModifyUsernameOrignal & "'"

I'm sure about password, but I'm not sure about username. Anyway brackets won't harm if it's not.

Member Avatar
Tobyjug2222
Light Poster
28 posts since Dec 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Sponsor
 
0
 

Works a treat, thanks very much! - Howcome the [ ] are required around the headers in the SQL string?

Thanks again.

Question Answered as of 1 Year Ago by adam_k
Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
1
 

db treats the contents of the brackets ([]) as field names and doesn't recognise the reserved words. Same applies when you've got spaces in the field names.

Please mark this as solved.

Member Avatar
Tobyjug2222
Light Poster
28 posts since Dec 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Sponsor
 
0
 

I see, I was never taught this! I was told aslong as I use the same words as my Acess table item/ column names, then it would work fine. I will definitely remember that for next time!

Thanks

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: