CODEWITHSUNDEEP
c#ms-access
lentz
lentz

Reputation: 337

Update Access table row with C#

I have a program that checks a table in an Access DB for 2 rows and allow the user to make changes to the ComplexityFactor column via textBox. If the rows do not exists when the application loads the 2 row are created (all the fields are the same except for the OHorUG field). Based on which row they have selected the user can then change the ComplexityFactor field of the selected row. I have been able to successfully create and read the rows but I cannot update the 1 field.

Here is the update code:

private void button1_Click(object sender, EventArgs e)
    {
        if (listView1.SelectedItems.Count != 0)
        {
            ListViewItem selected = listView1.SelectedItems[0];
            OleDbCommand updateCmd = new OleDbCommand();
            myConnection.Open();

            updateCmd.CommandType = CommandType.Text;
            updateCmd.CommandText = "UPDATE Contractors SET ComplexityFactor = @ComplexityFactor" +
               " WHERE Division = @Division AND ComplexityFactorCode = @ComplexityFactorCode AND ContractNumber = @ContractNumber AND ContractorCode = @ContractorCode AND ContractorName = @ContractorName AND OHorUG = @OHorUG)";

            updateCmd.Parameters.AddWithValue("@ComplexityFactor", textBox1.Text.ToString());//FYI this field is a Number in Access
            updateCmd.Parameters.AddWithValue("@Division", "SV");
            updateCmd.Parameters.AddWithValue("@ComplexityFactorCode", "X01");
            updateCmd.Parameters.AddWithValue("@ContractNumber", "0");
            updateCmd.Parameters.AddWithValue("@ContractorCode", "SSI");
            updateCmd.Parameters.AddWithValue("@ContractorName", "SunStream Inc");
            updateCmd.Parameters.AddWithValue("@OHorUG", selected.SubItems[6].Text.ToString());
            updateCmd.Connection = myConnection;
            updateCmd.ExecuteNonQuery();
            myConnection.Close();
        }
        else
        {
            MessageBox.Show("Please select a row to update...");


        }

I forgot to add the Exception:

System.Data.OleDb.OleDbException was unhandled
  Message=Extra ) in query expression 'Division = @Division AND ComplexityFactorCode = @ComplexityFactorCode AND ContractNumber = @ContractNumber AND ContractorCode = @ContractorCode AND ContractorName = @ContractorName AND OHorUG = @OHorUG)'.
  Source=Microsoft JET Database Engine
  ErrorCode=-2147217900
  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 WindowsFormsApplication1.Form1.button1_Click(Object sender, EventArgs e) in c:\Users\s153720\AppData\Local\Temporary Projects\WindowsFormsApplication1\Form1.cs:line 199
       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(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(Form mainForm)
       at WindowsFormsApplication1.Program.Main() in c:\Users\s153720\AppData\Local\Temporary Projects\WindowsFormsApplication1\Program.cs:line 18
       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()
  InnerException:

Upvotes: 1

Views: 3333

Answers (2)

Greg Finzer
Greg Finzer

Reputation: 7054

You might want to try:

  1. Convert your string to a number cint or cdec
  2. Pass the number into the SQL directly as long as you prevent SQL injection attacks.

Upvotes: 0

Conrad Lotz
Conrad Lotz

Reputation: 8818

Try and remove the closing bracket ) at the end of the update statement

updateCmd.CommandText = "UPDATE Contractors SET ComplexityFactor = @ComplexityFactor" +
" WHERE Division = @Division AND ComplexityFactorCode = @ComplexityFactorCode AND ContractNumber = @ContractNumber AND ContractorCode = @ContractorCode AND ContractorName = @ContractorName AND OHorUG = @OHorUG";

Upvotes: 1

Related Questions