my update c# code is not working,can i update two relational table at once?

Question

i was trying to update two tables at once, but i got some syntax error on update code could u give me some idea? the insert code works perfect and i tried to copy the insert code and edit on update button clicked

here is my code

    private void button2_Click(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data source= C:\Users\user\Documents\Visual Studio 2010\Projects\WindowsFormsApplication1\WindowsFormsApplication1\crt_db.accdb";

        try
        {


            conn.Open();
            String Name = txtName.Text.ToString();

            String AR = txtAr.Text.ToString();
            String Wereda = txtWereda.Text.ToString();
            String Kebele = txtKebele.Text.ToString();
            String House_No = txtHouse.Text.ToString();
            String P_O_BOX = txtPobox.Text.ToString();
            String Tel = txtTel.Text.ToString();
            String Fax = txtFax.Text.ToString();
            String Email = txtEmail.Text.ToString();
            String Item = txtItem.Text.ToString();
            String Dep = txtDep.Text.ToString();
            String k = "not renwed";


            String Remark = txtRemark.Text.ToString();

            String Type = txtType.Text.ToString();
            String Brand = txtBrand.Text.ToString();
            String License_No = txtlicense.Text.ToString();
            String Date_issued = txtDate.Text.ToString();
            String my_querry = "update crtPro set  Name='" + Name + "',AR='" + AR + "',Wereda='" + Wereda + "',Kebele='" + Kebele + "',House_No='" + House_No + "',P_O_BOX='" + P_O_BOX + "',Tel='" + Tel + "',Fax='" + Fax + "',Email='" + Email + "',Item='" + Item + "',Dep='" + Dep + "','" + k + "',Remark='" + Remark + "' where Name='" + Name + "' ";
            OleDbCommand cmd = new OleDbCommand(my_querry, conn);
            cmd.ExecuteNonQuery();


            String my_querry1 = "SELECT max(PID) FROM crtPro";
            OleDbCommand cmd1 = new OleDbCommand(my_querry1, conn);

            string var = cmd1.ExecuteScalar().ToString();





            String ki = txtStatus.Text.ToString();
            String my_querry2 = "update crtItemLicense set PID=" + var + ",Type='" + Type + "',Brand='" + Brand + "',License_No='" + License_No + "',Date_issued='" + Date_issued + "' where PID=" + var + "";
            OleDbCommand cmd2 = new OleDbCommand(my_querry2, conn);
            cmd2.ExecuteNonQuery();
            MessageBox.Show("Message added succesfully");

        }
        catch (Exception ex)
        {
            MessageBox.Show("Failed due to" + ex.Message);
        }
        finally
        {
            conn.Close();

        }

Not specifically related to your question but read about [SQL Injection](http://www.troyhunt.com/2010/05/owasp-top-10-for-net-developers-part-1.html). — DGibbs, Mar 31 '14 at 08:24

score 0 · Answer 1 · edited May 23 '17 at 11:53

The most likely problem based on the little information given (what database are you using for example - SQL Server 2012?), is that the datatype you are providing in the concatenated dynamic sql does not match the datatype of the column in the database. You've surrounded each value with quotes - which means it will be interpreted as a varchar. If you've got a date value in the wrong format (ie if Date_Issued is a date column) or if it is a number column, then it will error.

The solution is to replace your dynamic SQL with a parameterized query eg:

String my_querry = "update crtPro set Name=@name, AR=@ar, Wereda=@Wereda, etc ...";
OleDbCommand cmd = new OleDbCommand(my_querry, conn);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@name", Name);
cmd.Parameters.AddWithValue("@myParam", Convert.ToDateTime(txtDate.Text.Trim()));
...
cmd.ExecuteNonQuery();

You can read about it further here

PS Make sure your parameters are in the same order as they are used in the SQL, because oledbcommand doesn't actually care what you call them. see here

my update c# code is not working,can i update two relational table at once?

1 Answers1