CODEWITHSUNDEEP
c#mysqlselectinsert
marios
marios

Reputation: 261

C# Insert And Select in one string query

I use select query to read the values and then reinsert(Not update) them with some updates with the code below. I am trying to run this query with insert and select the same time but is not working and I don't take any errors.

string docdetls = "Insert into DocDetls (DocStatus,  DocType, DocNo,SeqNo,ItemCode,Quantity,Price,Disc,DiscAmount,VAT,ExpDate)"+
                    "(SELECT 2 as DocStatus,  DocType, @newdocno as DocNo,SeqNo,ItemCode,Quantity,Price,Disc,DiscAmount,VAT,ExpDate FROM docdetls where  DocStatus=@stat and DocNo=@docno)";
MySqlCommand cmd4 = new MySqlCommand(docdetls, con);
cmd4.Parameters.AddWithValue("stat", "1");
cmd4.Parameters.AddWithValue("newdocno", DocNoTxtBox.Text);
cmd4.Parameters.AddWithValue("DocNo",no );
con.Open();
cmd4.ExecuteNonQuery();
con.Close();

If I run the same query on mysql workbench with some values is working. Is this possible?How i can make this work?

UPDATE after the answer

       string docdetls = "Insert into DocDetls (DocStatus,  DocType, DocNo,SeqNo,ItemCode,Quantity,Price,Disc,DiscAmount,VAT,ExpDate)"+
                        "(SELECT 2 as DocStatus,  DocType, @newdocno as DocNo,SeqNo,ItemCode,Quantity,Price,Disc,DiscAmount,VAT,ExpDate FROM docdetls where  DocStatus=@stat and DocNo=@docno)";
                    MySqlCommand cmd4 = new MySqlCommand(docdetls, con);
                    cmd4.Parameters.AddWithValue("stat", "1");
                    //cmd4.Parameters.AddWithValue("newdocno", DocNoTxtBox.Text);
                    cmd4.Parameters.AddWithValue("DocNo",no );
                    cmd4.Parameters.Add("@newdocno", MySqlDbType.Int16).Value = DocNoTxtBox.Text;
                    con.Open();
 cmd4.ExecuteNonQuery();
                    con.Close();

Still not working..

Upvotes: 0

Views: 1636

Answers (2)

marios
marios

Reputation: 261

I have done it with the code below 

 string docdetls = "Insert into DocDetls (DocStatus,  DocType, DocNo,SeqNo,ItemCode,Quantity,Price,Disc,DiscAmount,VAT,ExpDate)"+
                        "(SELECT 2 as DocStatus,  DocType, @newdocno as DocNo,SeqNo,ItemCode,Quantity,Price,Disc,DiscAmount,VAT,ExpDate FROM docdetls where  DocStatus=@stat and DocNo=@docno)";
                    MySqlCommand cmd4 = new MySqlCommand(docdetls, con);
                    cmd4.Parameters.AddWithValue("stat", "1");
                    //cmd4.Parameters.AddWithValue("newdocno", DocNoTxtBox.Text);
                    cmd4.Parameters.AddWithValue("DocNo",no );
                    cmd4.Parameters.Add("@newdocno", MySqlDbType.Int16).Value = DocNoTxtBox.Text;
                    con.Open();
                    cmd4.ExecuteNonQuery();
                    con.Close();

Upvotes: 0

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

Reputation: 98740

I see a few things;

You can't parameterize your column names. You can only parameterize your values. That's why @newdocno as DocNo should not work. If you want to take this column as an input, you really do some strong validation before you concatenate it in your string or use a white list.

For being more clear, you should define your parameter names in your command and your parameter collection identical. That means, if you have

where  DocStatus=@stat and DocNo=@docno

in your command, your parameter collection should be as;

cmd4.Parameters.AddWithValue("@stat", "1");
cmd4.Parameters.AddWithValue("@DocNo",no );

As a best practice, don't use AddWithValue method. It might generates unexpected and surprising results. Use .Add() method to specify your parameter type and it's size instead.

And use using statement to dispose your database connections and commands automatically instead of calling Close methods manually.

Upvotes: 1

Related Questions