How to write the query for auto-generated ID

Question

I get an error when I input the drop down list parameter as data, I could not add the parameter data into the invoice table,the parameter data for subtotal, tax and total got their own value, but it only work well for manually input the data.

Drop down list parameter:

using (var cmd = con.CreateCommand())
{
cmd.CommandText = @"insert into Invoice(subtotal,tax,total) values (@subtotal,@tax,@total); select SCOPE_IDENTITY() as invoiceID;";
cmd.Parameters.AddWithValue("@subtotal", subtotal);
cmd.Parameters.AddWithValue("@tax", tax);
cmd.Parameters.AddWithValue("@total", total);
object OBJinvoiceID = cmd.ExecuteScalar();
}

Manually input:

using (var cmd = con.CreateCommand())
{
cmd.CommandText = @"insert into Invoice(subtotal,tax,total) values (2,2,2); select SCOPE_IDENTITY() as invoiceID;";
object OBJinvoiceID = cmd.ExecuteScalar();
}

If the identity column is set, then you cannot specify a value. Omit it from the insert and it will work. — Kami, Apr 01 '15 at 10:01
Am I missing something here, or are you inserting into a different table than the one you're creating? — komodosp, Apr 01 '15 at 10:06

score 3 · Accepted Answer · answered Apr 01 '15 at 10:02

3

Just remove the field from your values-listing:

insert into Invoice(subtotal,tax,total) values (@subtotal,@tax,@total); select SCOPE_IDENTITY() as invoiceID;

Also you can use the OUTPUT-Clause to get the value using:

into Invoice(subtotal,tax,total) OUTPUT invoiceID values (@subtotal,@tax,@total);

In the case you actually want to set the identity-column manually, you can use SET IDENTITY_INSERT as described here in MSDN

answered Apr 01 '15 at 10:02

Patrik

1,355
12
22

yes, I have tried this before, but I get an error saying "Cannot insert the value NULL into column 'invoiceID', table ; column does not allow nulls. INSERT fails." – qwe Apr 02 '15 at 00:55
the error occur at line "object OBJinvoiceID = cmd.ExecuteScalar();" – qwe Apr 02 '15 at 01:00
Is the table named `Invoice` or `Order1`? Is there a difference? When your table has the column `invoiceID` as identity-column, it cannot be inserted so it must be left out. Please check the table-configuration and the IDENTITY_INSERT setting. – Patrik Apr 02 '15 at 06:49
there have 2 table, 1 is invoice another 1 is Order1. It works well for Order1 because I input the data manually. But I get the error when I set it as auto-increment, the difference of this table is the data in Order1 table is manually input, but the data in Invoice is I get the parameter from the drop down list – qwe Apr 02 '15 at 06:58
because I got the parameters "cmd.Parameters.AddWithValue("@subtotal", subtotal);" in invoice table, so I get the error? how to solve with this? – qwe Apr 02 '15 at 08:44
It's hard to understand you here, please use more proper English grammar. I see you edited your initial post. If it is that way, you need to specify a value for the invoiceID-column. The initial post showed the Order1-Table which had the corresponding column as IDENTITY. And which column(s) do you mean by "data"? – Patrik Apr 02 '15 at 09:06

score 0 · Answer 2 · answered Apr 01 '15 at 10:02

0

Don't set the invoiceID

insert into Invoice(subtotal,tax,total) values (@subtotal,@tax,@total);

answered Apr 01 '15 at 10:02

komodosp

3,316
2
30
59

yes, I have tried this before, but I get an error saying "Cannot insert the value NULL into column 'invoiceID', table ; column does not allow nulls. INSERT fails." – qwe Apr 02 '15 at 00:56
the error occur at line "object OBJinvoiceID = cmd.ExecuteScalar();" – qwe Apr 02 '15 at 01:01
I see you have edited your post to show us the code for creating the invoice table. It doesn't have `invoiceID` as an Identity column which is why you're getting that error. – komodosp Apr 02 '15 at 07:12

score 0 · Answer 3 · answered Apr 01 '15 at 10:02

0

If you have it Incrementing in database you dont need to Alter it again in the code. Just remove the invoiceID from your statement

answered Apr 01 '15 at 10:02

vLr

75
1
11

yes, I have tried this before, but I get an error saying "Cannot insert the value NULL into column 'invoiceID', table ; column does not allow nulls. INSERT fails." – qwe Apr 02 '15 at 00:56
the error occur at line "object OBJinvoiceID = cmd.ExecuteScalar();" – qwe Apr 02 '15 at 01:00
Rebuild you database implementation so its something like: invoiceID int NOT NULL and make sure it is set like identity. Check this [link](http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012) so you can easily do it. – vLr Apr 03 '15 at 14:38

score 0 · Answer 4 · answered Apr 01 '15 at 10:09

0

I'm not a .NET guy, but by googling I think you have to not mention the identity field in your insert query:

using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = @"insert into Invoice(subtotal,tax,total) values (@subtotal,@tax,@total);
// the rest
            }

Plz take a look in this simple tutorial: w3schools

answered Apr 01 '15 at 10:09

54l3d

3,913
4
32
58

yes, I have tried this before, but I get an error saying "Cannot insert the value NULL into column 'invoiceID', table ; column does not allow nulls. INSERT fails." – qwe Apr 02 '15 at 00:56
the error occur at line "object OBJinvoiceID = cmd.ExecuteScalar();" – qwe Apr 02 '15 at 01:01

score 0 · Answer 5 · answered Apr 01 '15 at 10:21

0

insert into Invoice(subtotal,tax,total) values (@subtotal,@tax,@total); select SCOPE_IDENTITY() as invoiceID;

answered Apr 01 '15 at 10:21

Prasanna Gulhane

70
1
8

yes, I have tried this before, but I get an error saying "Cannot insert the value NULL into column 'invoiceID', table ; column does not allow nulls. INSERT fails." – qwe Apr 02 '15 at 00:56
the error occur at line "object OBJinvoiceID = cmd.ExecuteScalar();" – qwe Apr 02 '15 at 01:00

How to write the query for auto-generated ID

5 Answers5