Reputation: 113
Is it possible to create a SQL select max(id)
as a variable inside a query?
This doesn't work:
command.CommandText = "INSERT INTO ordreid (ordrenr,ordreid) SELECT @ordrenr, @ordreid";
command.Parameters.AddWithValue("@ordrenr", nyordre);
command.Parameters.AddWithValue("@ordreid", ("MAX(ordreid)+1 FROM ordreid"));
command.ExecuteScalar();
Here is a photo of what I'd like to do. 5 customers have added items to their orders. Customer 1 has 3 items, customer 2 has 4 items, customer 3 has 1 item, and so on...
This way I have 1 ordreid
even through the order could consist of 30 items.
Upvotes: 0
Views: 1293
Reputation: 84825
command.Parameters.AddWithValue("@ordreid", ("MAX(ordreid)+1 FROM ordreid"));
The method name AddWithValue
hints at the reason why this won't work: Parameters contain data, that is: values. They cannot represent a part of a SQL statement.
Your intention appears to be that @ordreid
should be replaced with that piece of SQL. If this is so, then there's no reason to even have a parameter. Simply perform the substitution manually and change the CommandText
:
command.CommandText = @"INSERT INTO ordreid (ordrenr, ordreid)
VALUES (@ordrenr, (SELECT MAX(ordreid)+1 FROM ordreid));";
Note that I changed four things (apart from spreading the command text across two lines, for legibility's sake, using C#'s @"…"
string syntax). Only the first two points are crucial:
I moved the MAX(…)
SQL directly into your CommandText
. This makes the (invalid) @ordreid
parameter obsolete.
To determine the value of MAX(ordreid) FROM ordreid
, you need a sub-query; thus the added SELECT
before MAX
. Otherwise, the syntax wouldn't be valid.
I replaced your SELECT
with a VALUES
table value constructor. (Otherwise, because of the previous point, we'd have two SELECT
s very close to each other, which would look somewhat confusing.)
I added a ;
at the end of your query. Current versions of SQL Server don't yet require such a statement terminator, but Microsoft has hinted that they might become compulsory in future versions of T-SQL. I therefore believe that it's a good habit to get into now.
That all being said, you should probably turn the ordreid
column into an IDENTITY
column and let SQL Server choose the value to be inserted (making the SELECT MAX(…)
business obsolete). Otherwise, if two processes or threads execute the same INSERT
command at the same time, you might end up with several rows having the same value for ordreid
.
Upvotes: 1
Reputation: 61975
Query parameters cannot include SQL verbatim.
This is how such prepared statements prevent SQL Injection as they are not directly inserted. Rather only the corresponding data is used in the query - in this case that is the string that contains SQL, and results in invalid SQL syntax.
The SQL text then needs to look similar to the following, although this probably does not do what is desired. (Asking how to do the higher level task will lead to actually useful queries/approaches.)
@"INSERT INTO ordreid (ordrenr, ordreid)
SELECT @ordrenr, MAX(ordreid)+1
FROM ordreid"
Upvotes: 2