Problem with SQL Insert

I have this insert command:
string command = "Insert into tbl_books (ID, Title, Series, [Last Name - 1], [First Name - 1], " +
"[Last Name - 2], [First Name - 2], [Last Name- 3], [First Name - 3], [Last Name - 4], " +
"[First Name - 4], [Corporate Author], Edition, Publisher, Copyright, [ISBN Number], " +
"[Call Number - Complete], [Subject Headding - 1], [Subject Heading - 2], [Subjec Heading -3], " +
"Format, Copies, Donor, [In Virgo?], [Number in WorldCat]) " +
"VALUES " +
"(((SELECT MAX(ID) from tbl_books)+1), '" + title + "', '" + series + "', '" + lastName1 + "', '" + firstName1 + "', '" + lastName2 + "', '" +
firstName2 + "', '" + lastName3 + "', '" + firstName3 + "', '" + lastName4 + "', '" + firstName4 + "', '" +
corpAuthor + "', '" + edition + "', '" + publisher + "', '" + copyright + "', '" + isbnNumber + "', '" +
callNumber + "', '" + subject1 + "', '" + subject2 + "', '" + subject3 + "', '" + format + "', '" +
copies + "', '" + donor + "', '" + inVirgo + "', " + worldcat + ")";

SqlDataSource1.InsertCommand = command;
SqlDataSource1.Insert();

But it says that I cannot do subqueries to get the next element of the ID. I dont think that there is automatic numbering on the ID column in the table. What is another way that I could do this? Thank you very much for your help!

you will need to set the ID column as identity column and when you do the insert do not insert any thing in that column, the new ID will generated automatically

|||Thanks, I will try to do that.|||

On another point - i have always been told that if you are building a big query like that then it is more efficient to use a StringBuilder to do so (from the System.Text namespace)

StringBuilder sb = new StringBuilder();

sb.Append("<first line of query>");

etc

then pass sb.ToString(); to get the string back

|||use string builder is good practice for a large web site (a lot of user at the same time). but if this is just a few user, and number of strings you try to concatnate is less then 25, string should be faster then string builder.|||

mm2ha:

I have this insert command:
string command = "Insert into tbl_books (ID, Title, Series, [Last Name - 1], [First Name - 1], " +
"[Last Name - 2], [First Name - 2], [Last Name- 3], [First Name - 3], [Last Name - 4], " +
"[First Name - 4], [Corporate Author], Edition, Publisher, Copyright, [ISBN Number], " +
"[Call Number - Complete], [Subject Headding - 1], [Subject Heading - 2], [Subjec Heading -3], " +
"Format, Copies, Donor, [In Virgo?], [Number in WorldCat]) " +
"VALUES " +
"(((SELECT MAX(ID) from tbl_books)+1), '" + title + "', '" + series + "', '" + lastName1 + "', '" + firstName1 + "', '" + lastName2 + "', '" +
firstName2 + "', '" + lastName3 + "', '" + firstName3 + "', '" + lastName4 + "', '" + firstName4 + "', '" +
corpAuthor + "', '" + edition + "', '" + publisher + "', '" + copyright + "', '" + isbnNumber + "', '" +
callNumber + "', '" + subject1 + "', '" + subject2 + "', '" + subject3 + "', '" + format + "', '" +
copies + "', '" + donor + "', '" + inVirgo + "', " + worldcat + ")";

SqlDataSource1.InsertCommand = command;
SqlDataSource1.Insert();

But it says that I cannot do subqueries to get the next element of the ID. I dont think that there is automatic numbering on the ID column in the table. What is another way that I could do this? Thank you very much for your help!

Please don't build your query in that manner! Never concatenate UI-supplied values to a SQL string that will be executed. I have a standard list of articles that I recommend on this:

Here's the why:

Please, please, please, learn about injection attacks!

And here's the how:

How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

Also, please read this Patterns & Practices:

Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication

in particular, chapter 12:

Data Access Security