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
– Steve, Commented Dec 10, 2015 at 8:17
This actually works, instead of .text just make it .SelectedValue. — Jaser MB
– Jaser MB, Commented Dec 10, 2015 at 9:15
Yes but the answer from Soner Gonul is more complete and suggest you best practices. Please try to follow his advices — Steve
– Steve, Commented Dec 10, 2015 at 9:20

Community · Accepted Answer · 2020-07-29 18:28:43Z

1

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

edited Jul 29, 2020 at 18:28

CommunityBot

11 silver badge

answered Dec 10, 2015 at 8:20

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jaser MB Over a year ago

Thank you for the tips. It will be very helpful since I'm just a newbie. I will bear it in mind.

Collectives™ on Stack Overflow

Updating columns in Database Access c#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related