In c#, how to assign a sql query result within a variable?

Question

 SqlConnection connection = new SqlConnection(connString.ToString());
    string select = "SELECT (CASE WHEN MAX(page_no) IS NULL  THEN 1 ELSE MAX(page_no)+1 END) FROM dbo.BOOK";
    string insert = "INSERT INTO dbo.BOOK (book_id,select) VALUES (121,4)";
    SqlCommand sqlCommand = new SqlCommand(insert,connection);
    insert.ExecuteNonQuery();

Here I got exception where the insert contains invalid string select. Please tell me how assign sub query within the insert?

did you try your query on SQL Query browser? If not try it once. If it works there it will definitely work with my code — Raghuveer
– Raghuveer, Commented Sep 15, 2012 at 5:38
I think now you would understood why I'm using select statement. (I had edited the select statement). — Praveen
– Praveen, Commented Sep 15, 2012 at 5:41
What are you trying to achieve with sub query inside Insert query — Raghuveer
– Raghuveer, Commented Sep 15, 2012 at 5:47
exactly. insert = "INSERT INTO dbo.BOOK (book_id,SELECT (CASE WHEN MAX(page_no) IS NULL THEN 1 ELSE MAX(page_no)+1 END) FROM dbo.BOOK) VALUES (121,4)" ------ this works but through select variable I cant. I need Help. — Praveen
– Praveen, Commented Sep 15, 2012 at 5:49

Anant Dabhi · Accepted Answer · 2012-09-15 06:54:28Z

1

you cannot use a select statement like this if you want to use a sub query it has to be in single statement

but in above statement you write in different different statement for selection and insert query .

so cmd.ExecuteNonquery() execute only insert text statement so SQL engine unable to find SELECT(and SELECT is a Reserved keyword) so it gives you a error

if you go with subquery try this

SqlConnection connection = new SqlConnection(connString.ToString());

string select = "SELECT 121, (CASE WHEN MAX(page_no) IS NULL  THEN 1 ELSE MAX(page_no)+1 END) FROM dbo.BOOK";
string insert = "INSERT INTO dbo.BOOK (book_id,[select]) "+select;

SqlCommand sqlCommand = new SqlCommand(insert,connection);

sqlCommand.ExecuteNonQuery();

edited Sep 15, 2012 at 6:54

answered Sep 15, 2012 at 5:39

Anant Dabhi

11.2k3 gold badges33 silver badges51 bronze badges

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

5 Comments

Praveen Over a year ago

How can I do the select within an insert query using c#?

Anant Dabhi Over a year ago

so you want select and insert in single query ?

Praveen Over a year ago

insert = "INSERT INTO dbo.BOOK (book_id,SELECT (CASE WHEN MAX(page_no) IS NULL THEN 1 ELSE MAX(page_no)+1 END) FROM dbo.BOOK) VALUES (121,4)" ------ this works but through select variable I cant. I need Help.

Praveen Over a year ago

the code that you have give showed an exception : 'Invalid cloumnname select'

Praveen Over a year ago

insert.ExecuteQuery()-- wrong. It should be sqlCommand.ExecuteQuery(). --please edit your answer. I changed it but showed exception.

Nikhil Agrawal · Accepted Answer · 2012-09-15 05:37:20Z

1

Your query results will return a DataTable. So use a DatAdapter to fill a DataTable.

answered Sep 15, 2012 at 5:37

Nikhil Agrawal

48.8k25 gold badges134 silver badges212 bronze badges

Comments

Raghuveer · Accepted Answer · 2012-09-15 05:57:24Z

1

You are doing it wrong, you have to Execute query on SQLCommand object not on string object try this

using(SqlConnection connection = new SqlConnection(connString.ToString())){
string insert = "Insert Query";
using (SqlCommand sqlCommand = new SqlCommand(insert,connection))
{
 con.Open();
 int i = sqlCommand.ExecuteNonQuery();
}
}

Update:

 var selectQuery = "SELECT (CASE WHEN MAX(page_no) IS NULL THEN 1 ELSE MAX(page_no)+1 END) FROM dbo.BOOK";
 var insertQuery = string.format("INSERT INTO dbo.BOOK (book_id,{0}) VALUES (121,4)",selectQuery);

edited Sep 15, 2012 at 5:57

answered Sep 15, 2012 at 5:25

Raghuveer

2,6273 gold badges31 silver badges61 bronze badges

12 Comments

Praveen Over a year ago

can you explain why to have new variable i?

Raghuveer Over a year ago

That's your wish actually you can use it or not, that will have the return value of ExecuteNonQuery() method.

Raghuveer Over a year ago

@AnantDabhi Why you are getting that doubt

Praveen Over a year ago

the updated ans showed error :"Index (zero based) must be greater than or equal to zero and less than the size of the argument list".

Raghuveer Over a year ago

you will get that error if you did something wrong in string.format() can you show your string.format() code

|

Collectives™ on Stack Overflow

In c#, how to assign a sql query result within a variable?

3 Answers 3

5 Comments

Comments

Update:

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Update:

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related