Insert into table 1 update table 2 on button click in c# using access

Question

Ok, here is my problem. I am making a new GUI for work. Nothing fancy, just something to enter information to be printed out on labels. I have managed to get the code working to insert the values into the MainLabel table on button click, but I also need these to UPDATE just the first row in the Label table.

For some reason it will insert but not do the update. how can I make that happen? Here is what I have Any help would be greatly appreciated.

Updated still not working

     OleDbConnection con = new OleDbConnection();
        con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jim\Desktop\WindowsFormsApplication1\WindowsFormsApplication1\labels1.mdb;Persist Security Info=False";

        OleDbCommand cmd = new OleDbCommand();
        OleDbCommand cmd1 = new OleDbCommand();
        cmd.CommandType = CommandType.Text;
        cmd1.CommandType = CommandType.Text;
        cmd1.CommandText = "UPDATE Label SET SaleOrderNumber = @SaleOrderNumber, NsN = @NsN, NsNBarcode = @NsNBarcode, PartNumber = @PartNumber, Qty = @Qty, Description = @Description, CustomerPo = @CustomerPo, CustomerPoBarcode = @CustomerPoBarcode, PackingCode = @PackingCode, Weight = @Weight, Clin = @Clin, SaleOrderDate = @SaleOrderDate, MCM = @MCM, Cage = @Cage ";

        cmd.CommandText = "INSERT into MainLabel ([SaleOrderNumber], [NsN], [NsNBarcode], [PartNumber], [Qty], [Description], [CustomerPo], [CustomerPoBarcode], [PackingCode], [Weight], [Clin], [SaleOrderDate], [MCM], [Cage]) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,? ,?)";



        cmd.Parameters.AddWithValue("@SaleOrderNumber", label1.Text);
        cmd.Parameters.AddWithValue("@NsN", label6.Text);
        cmd.Parameters.AddWithValue("@NsNBarcode", label6.Text);
        cmd.Parameters.AddWithValue("@PartNumber", label2.Text);
        cmd.Parameters.AddWithValue("@Qty", label7.Text);
        cmd.Parameters.AddWithValue("@Description", label3.Text);
        cmd.Parameters.AddWithValue("@CustomerPo", label8.Text);
        cmd.Parameters.AddWithValue("@CustomerPoBarcode", label8.Text);
        cmd.Parameters.AddWithValue("@PackingCode", label4.Text);
        cmd.Parameters.AddWithValue("@Weight", label9.Text);
        cmd.Parameters.AddWithValue("@Clin", label5.Text);
        cmd.Parameters.AddWithValue("@SaleOrderDate",label12.Text);
        cmd.Parameters.AddWithValue("@MCM", label10.Text);
        cmd.Parameters.AddWithValue("@Cage", label11.Text);
        cmd.Connection = con;




        con.Open();

        cmd.ExecuteNonQuery();
        cmd1.ExecuteNonQuery();
        con.Close();

it goes down to cmd1.executenonquery(); and I get ExecuteNonQuery: Connection property has not been initialized. — xgonnahaveitx
– xgonnahaveitx, Commented Nov 8, 2013 at 7:50
it is surley because you have not initilaised your oleDBCommand object cmd1 with any connection object do as below: cmd1.Connection=con; before executing the cmd1. — Sudhakar Tillapudi
– Sudhakar Tillapudi, Commented Nov 8, 2013 at 8:25

Sriram Sakthivel · Accepted Answer · 2013-11-08 06:43:42Z

1

You're not executing cmd1.

cmd1.ExecuteNonQuery();

Side note, use all IDisposable objects in using statement.

answered Nov 8, 2013 at 6:43

Sriram Sakthivel

73.8k7 gold badges118 silver badges200 bronze badges

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

Comments

Sudhakar Tillapudi · Accepted Answer · 2013-11-08 08:20:35Z

Reason for Failure: You are only executing the OleDBCommand object cmd but not executing the cmd1 where the update command is given.

Solution 1: you have to execute the OleDBCommand object cmd1(update command) as below:

cmd1.ExecuteNonQuery();

Solution 2:

You can combine both insert and update commands into one string and execute once as below.

String command="";
command= "INSERT into MainLabel ([SaleOrderNumber], [NsN], [NsNBarcode], [PartNumber], [Qty], [Description], [CustomerPo], [CustomerPoBarcode], [PackingCode], [Weight], [Clin], [SaleOrderDate], [MCM], [Cage]) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,? ,?);";
command+="UPDATE Label SET SaleOrderNumber = @SaleOrderNumber, NsN = @NsN, NsNBarcode = @NsNBarcode, PartNumber = @PartNumber, Qty = @Qty, Description = @Description, CustomerPo = CustomerPo, CustomerPoBarcode = @CustomerPoBarcode, PackingCode = @PackingCode, Weight = @Weight, Clin = @Clin, SaleOrderDate = @SaleOrderDate, MCM = @MCM, Cage = @Cage ";

cmd.CommanText=command;

cmd.ExecuteNonQuery();

Note : Add Semicolon inbetween two commands.

Solution 3: Create a function to execute your sql commands - which will take care of creation and disposal of your OleDB Connection and command objects .

private  void RunMyCommand(String SQLCommand)
        {
            using (OleDbConnection con = new OleDbConnection())
            {
                con.ConnectionString =
                    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jim\Desktop\WindowsFormsApplication1\WindowsFormsApplication1\labels1.mdb;Persist Security Info=False";
                con.Open();
                using (OleDbCommand cmd = new OleDbCommand())
                {

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = SQLCommand;
                    cmd.Parameters.AddWithValue("@SaleOrderNumber", label1.Text);
                    cmd.Parameters.AddWithValue("@NsN", label6.Text);
                    cmd.Parameters.AddWithValue("@NsNBarcode", label6.Text);
                    cmd.Parameters.AddWithValue("@PartNumber", label2.Text);
                    cmd.Parameters.AddWithValue("@Qty", label7.Text);
                    cmd.Parameters.AddWithValue("@Description", label3.Text);
                    cmd.Parameters.AddWithValue("@CustomerPo", label8.Text);
                    cmd.Parameters.AddWithValue("@CustomerPoBarcode", label8.Text);
                    cmd.Parameters.AddWithValue("@PackingCode", label4.Text);
                    cmd.Parameters.AddWithValue("@Weight", label9.Text);
                    cmd.Parameters.AddWithValue("@Clin", label5.Text);
                    cmd.Parameters.AddWithValue("@SaleOrderDate", label12.Text);
                    cmd.Parameters.AddWithValue("@MCM", label10.Text);
                    cmd.Parameters.AddWithValue("@Cage", label11.Text);
                    cmd.Connection = con;
                    cmd.ExecuteNonQuery();
                }

            }

        }

call the above function as below:

 RunMyCommand("UPDATE Label SET SaleOrderNumber = @SaleOrderNumber, NsN = @NsN, NsNBarcode = @NsNBarcode, PartNumber = @PartNumber, Qty = @Qty, Description = @Description, CustomerPo = @CustomerPo, CustomerPoBarcode = @CustomerPoBarcode, PackingCode = @PackingCode, Weight = @Weight, Clin = @Clin, SaleOrderDate = @SaleOrderDate, MCM = @MCM, Cage = @Cage ");
 RunMyCommand("INSERT into MainLabel ([SaleOrderNumber], [NsN], [NsNBarcode], [PartNumber], [Qty], [Description], [CustomerPo], [CustomerPoBarcode], [PackingCode], [Weight], [Clin], [SaleOrderDate], [MCM], [Cage]) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,? ,?)");

Sudhakar, I have tried this and I get this error {"Characters found after end of SQL statement."}
did you add the semicolon in string after insert command as shown in my answer ?
yes and that is when it threw that error. I copied your answer and pasted it. then I also tried to revert back the other way and execute cmd1 and got the other error
@xgonnahaveitx: please see my updated answer with sloution 3: Hope it helps :)

Collectives™ on Stack Overflow

Insert into table 1 update table 2 on button click in c# using access

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related