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:
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.
