-
June 21st, 2005, 09:06 AM
#1
Adding a record in SQL with an autonumber
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#)
Code:
"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
-
June 21st, 2005, 09:26 AM
#2
Re: Adding a record in SQL with an autonumber
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..
-
June 21st, 2005, 09:26 AM
#3
Re: Adding a record in SQL with an autonumber
because the ID is automatically populated, you don't need to populate it yourself.
so, your statement would be:
Code:
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.
Mike
-
June 21st, 2005, 09:28 AM
#4
Re: Adding a record in SQL with an autonumber
Just double check to make sure you have specified the autonumber on the primary key column.
-
June 21st, 2005, 09:30 AM
#5
Re: Adding a record in SQL with an autonumber
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!
-
June 21st, 2005, 09:34 AM
#6
Re: Adding a record in SQL with an autonumber
what is the primary key of your table?is it the ID column or is it ID+some other column?
-
June 21st, 2005, 02:58 PM
#7
Re: Adding a record in SQL with an autonumber
Originally Posted by CoffeeMug
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
-
June 21st, 2005, 05:05 PM
#8
Re: Adding a record in SQL with an autonumber
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?
-
June 22nd, 2005, 01:35 AM
#9
Re: Adding a record in SQL with an autonumber
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!
-
June 22nd, 2005, 02:17 AM
#10
Re: Adding a record in SQL with an autonumber
Originally Posted by 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?
I thought sequences were used in Oracle.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|