C# Insert And Select in one string query

Question

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..

The question is about inserting with select. Not about insert and select. — ilias iliadis
– ilias iliadis, Commented May 6, 2019 at 5:54

Soner Gönül · Accepted Answer · 2015-07-06 08:45:55Z

1

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.

edited Jul 6, 2015 at 8:45

answered Jul 6, 2015 at 8:43

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

5 Comments

marios Over a year ago

And what about @newdocno? i need it.. Is there a way put it?

marios Over a year ago

Is the only option white list? Can you show me an example with my code how to do white list?

Soner Gönül Over a year ago

@marios I mentioned it in my answer. You try to parameterize your column name which is not allowed. You should use string concatenation for your column but before you do it, you need to use really strong validation before you put it in your sql or create a whitelist which it includes valid column names.

marios Over a year ago

I dont need validation for this textbox because i generate the number from my code and put it there and i set enable=false..

marios Over a year ago

Your answer didnt help me to solve my problem! I cant accept it

marios · Accepted Answer · 2015-07-06 12:32:52Z

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

Collectives™ on Stack Overflow

C# Insert And Select in one string query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related