Updating columns in Database Access c#

Question

I have 2 tables in database, tblIPAddress and tblDepartment.

On click of update button, I managed to update everything except for for 1 column(DepartmentID) because of the way I add items on the combobox(cmbDepartment).

DepartmentID column is numbers only. I am identifying each department by ID Number.

Example: 1=IT, 2=Accounts etc.

My question is how to update the column (DepartmentID) with numbers equal to the selected department in combobox?

Code I use to add items on combobox (cmbDepartment)

            string query = "select ID, Department from tblDepartment";
            OleDbDataAdapter da = new OleDbDataAdapter(query, myConn);

            DataSet dsdpt = new DataSet();
            da.Fill(dsdpt, "tblDepartment");
            cmbDepartment.DataSource = dsdpt.Tables["tblDepartment"];
            cmbDepartment.ValueMember = "ID";
            cmbDepartment.DisplayMember = "Department";

Code I use to update table(tblIPAddress)

            OleDbCommand command = new OleDbCommand();
            command.Connection = myConn;
            string query = "";
            query = "update tblIPAddress set E_Name=@E_Name, DepartmentID=@DepartmentID , E_Username=@E_Username, E_Password=@E_Password, E_Extension=@E_Extension, E_MobileNo=@E_MobileNo, Remarks=@Remarks, Modified_by=@Modified_by, Modified_on=@Modified_on where IP_Address=@IP_Address";

            command.CommandText = query;
            command.Parameters.AddWithValue("@E_Name", this.txtname.Text);              
            command.Parameters.AddWithValue("@E_Username", this.txtusern.Text);
            command.Parameters.AddWithValue("@E_Password", this.txtpwd.Text);
            command.Parameters.AddWithValue("@E_Extension", this.txtext.Text);
            command.Parameters.AddWithValue("@E_MobileNo", this.txtmobile.Text);
            command.Parameters.AddWithValue("@Remarks", this.txtrmk.Text);
            command.Parameters.AddWithValue("@Modified_by", Loginfrm.userlogged);
            command.Parameters.AddWithValue("@Modified_on", DateTime.Today.ToShortDateString());
            command.Parameters.AddWithValue("@IP_Address", this.txtip.Text);
            command.Parameters.AddWithValue("@DepartmentID", this.cmbDepartment.Text);
            command.ExecuteNonQuery();
            MessageBox.Show("IP Details Updated");

The value (ID) of the selected item in the combobox is available through the property SelectedValue (cast to integer) — Steve, Dec 10 '15 at 08:17
This actually works, instead of .text just make it .SelectedValue. — Jaser MB, Dec 10 '15 at 09:15
Yes but the answer from Soner Gonul is more complete and suggest you best practices. Please try to follow his advices — Steve, Dec 10 '15 at 09:20

score 1 · Accepted Answer · edited Jul 29 '20 at 18:28

From OleDbCommand.Parameters property

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Since you didn't provide your parameter values with the same order in your command, this generate a problem. Change your parameter values with the same order that you defined in your command.

Also as Steve mentioned, you might need to add @DepartmentID value as (int)cmbDepartment.SelectedValue instead of Text property.

A few things more;

Do not store your DateTime values as a string. It is a bad habit. Change your Modified_on column some datetime type and pass your DateTime.Today value directly. Read: Bad habits to kick : choosing the wrong data type
Don't use AddWithValue as much as you can. It may generate unexpected and surprising results sometimes. Use Add method overloads to specify your parameter type and it's size.
Use using statement to dispose your connection, command and adapter automatically as well.
Do not store your passwords as a plain text. Read: Best way to store password in database

Thank you for the tips. It will be very helpful since I'm just a newbie. I will bear it in mind. — Jaser MB, Dec 10 '15 at 09:14

Updating columns in Database Access c#

1 Answers1