CODEWITHSUNDEEP
c#
Jaser MB
Jaser MB

Reputation: 45

Updating columns in Database Access c#

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");

Upvotes: 0

Views: 419

Answers (1)

Soner Gönül
Soner Gönül

Reputation: 98750

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;

Upvotes: 1

Related Questions