Adding a record in SQL with an autonumber

[CoffeeMug]

Hi guys,

I'm trying to add a record in an SQL statement through an SqlCommand into an Sql table. This table has an Autonumber (ID) and is returning an error as soon as I try to add the record through ASP.Net (C#)

"INSERT INTO indicators(client_id, language_id, type, " +
"location, sub_type, sub_type_desc, sub_type_category, used) " +
"VALUES ('" + txtCust.Text + "','" + txtLanguage.SelectedValue + 
"','" + txtType.SelectedValue + "','" + txtLocation.SelectedValue + 
"','" + txtSubType.SelectedValue + "','" + txtSubDesc.Text + 
"','" + txtSubCat.Text + "','" + txtUsed.SelectedValue + "')" +
" SELECT * FROM indicators"

This is generating the following error:

Violation of PRIMARY KEY constraint 'PK_indicators'. Cannot insert duplicate key in object 'indicators'. The statement has been terminated.

My SQL knowledge is very basic so can anyone please help me on this one?

Thanks in advance!

Vincent

[Shuja Ali]

As the field is an AutoNumber field, you do not have to explicitly mention it in your SQL INSERT Statement.. The field will be incremented by the SQL Server automatically..

You need to remove the field that is set to Autonumber from your Query..

[Pinky98]

because the ID is automatically populated, you don't need to populate it yourself.
so, your statement would be:

INSERT INTO indicators(language_id, type, location, sub_type, sub_type_desc, sub_type_category, used)
VALUES ('" + txtLanguage.SelectedValue + "','" +
             txtType.SelectedValue + "','" + txtLocation.SelectedValue + "','" +
             txtSubType.SelectedValue + "','" + txtSubDesc.Text + "','" +
             txtSubCat.Text + "','" + txtUsed.SelectedValue + "')

 SELECT * FROM indicators"

Make sense? The autonumber field will be automatically populated


BTW:
This is not a good way to use SQL statements. Using this technique your database can be very easily compremised. It is called SQL Insertion. Consider when somebody enters the following text into one of your text boxes:

blah '); DROP indicators // "

this text will be put as is into your string. The statement will then be interpreted as follows:
The single quote (after blah) will end the string, and the insert will throw an error... HOWEVER, more problematic is, the new statment (DROP indicators) will delete your table. and everything after this will be treated as a comment.

You should rather use stored procedures.

Just thought you might like to know... rather than find out the hard way.

[cafedreamz]

Just double check to make sure you have specified the autonumber on the primary key column.

[CoffeeMug]

Thank you for your quick responds guys, but I don't have the ID set up in the query (it's simply called id)
Any other thoughts?

Thanks again for all the replies!

[cafedreamz]

what is the primary key of your table?is it the ID column or is it ID+some other column?

[klintan]

Any other thoughts?

To get the inserted id, use SCOPE_IDENTITY() in SQL Server. (@@IDENTITY returns the latest inserted id on the current connection, it is usually the correct id but if your insert statement caused some trigger to insert some other row then this id will be returned by @@IDENTITY).

To use insert with data adapters, see this thread: http://www.codeguru.com/forum/showthread.php?t=344085

[cjard]

in oracle we do:


insert into table1
select max(id_number)+1, 'literal value for column 1', 'value for column 2', 'col 3' ...
from table1


the select statement returns 1 row that has:

the max id number +1, followed by all the literal text you put in, in columns

this row is then inserted. clever eh?

[CoffeeMug]

Hey people,

I found out what the problem was, the record I was using to test this with already existed in the database (DUH) which returned this error.
Thanks for all your help though really appreciate it!

[klintan]

in oracle we do:


insert into table1
select max(id_number)+1, 'literal value for column 1', 'value for column 2', 'col 3' ...
from table1


the select statement returns 1 row that has:

the max id number +1, followed by all the literal text you put in, in columns

this row is then inserted. clever eh?

I thought sequences were used in Oracle.