INSERT, SCOPE_IDENTITY, and parameters...

I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening.

 SqlConnection conn =new SqlConnection(@."Server=.\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True"); conn.Open();try { SqlCommand cmd =new SqlCommand(); cmd.Connection = conn;// delete all rows cmd.CommandText ="DELETE FROM Person"; cmd.ExecuteNonQuery(); Response.Write("start... <br><br>");// ad-hoc insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (5, 'John Smith', 20)"; cmd.ExecuteNonQuery(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery();// parameter insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@.PersonID, @.PersonName, @.PersonAge)"; p =new SqlParameter("@.PersonID", 11); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonName","Jon Doe2"); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonAge", 21); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery(); Response.Write("<br>end."); }finally { conn.Close(); }

I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing...

Thank you.

If I add "; SELECT SCOPE_IDENTITY()" to the INSERT statement and use ExecuteScalar instead of ExecuteNonQuery I get the identity back. The question is, why do I have to do this? If I use the SQL Server Profiler I don't see any difference between the two methods. Any thoughts?

Thank you.

|||Your SCOPE_IDENTITY() should be in the same batch as your INSERT. You are making 3 separate calls each with a different T-SQL statement and each one is treated independent of other. Better yet, use a stored proc and do it all in one place.|||

What exactly defines a batch? And if CommandText = "INSET INTO Table (fields..) VALUES (values); SELECT SCOPE_IDENTITY()" is a single batch when using parameters, why does SCOPE_IDENTITY work in two separate "batches" when not using parameters? I guess, what I'm trying to figure out is why are how are they different? Thanks.

|||

Hi,

A batch is guaranteed to be executed together. If you execute the command in 2 batches, there is possibility that a concurrent user may be also inserting into that table. Then your SELECT SCOPE_IDNETITY() query might return the incorrect number.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

sql

Insert with Parameters (SQL Server)

Hello, this is my code:

SqlCommand cmd =new SqlCommand("INSERT INTO Users (Username,Password) " +"VALUES ('@.username','@.password' ",new SqlConnection(my_ConnectionString)); cmd.Parameters.Add("@.username", SqlDbType.NVarChar, 50).Value = txtUsername.Text;cmd.Parameters.Add("@.password", SqlDbType.NVarChar, 50).Value = txtPassword.Text cmd.Connection.Open();cmd.ExecuteNonQuery();cmd.Connection.Close();

But in the database, the row inserted is exactly this:

"@.username" "@.password"

I mean, the parameters are not inserted :S

Please, tell me the error in the code...
Thank you so much,

Carlos.Placing single quotes around the parameters makes them be treated as literal strings. Remove the single quotes and you should have better luck.|||Thank you, now it works ;)

Carlos.

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...

Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "

Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you

Melanie

There is a conflict, you say that staffNo can be null, however, PropStaffFK says that it can not, since you have no staff member with an id of null.|||

Thanks for your reply Motley.

So does that mean if you have a foreign key referencing a primary key from another table that it can't be null?

That makes sence that it would be true, however, in one of my tables TblProperty i have the column staffNo and in this column i want to allow null values to indicate that the property hasn't been approved by a memeber of staff (if it has been approved then the member of staff that approved the property, thier staffNo will be inserted in there) . Do you know of any way to do this?

Thanks

Melanie

|||

Here's the problem. In order to set a FK contraint, you need to reference a primary key table (That has a primary key set). You can't set a primary key index on a column that is nullable. Since the Primary key table doesn't have a NULL-value as one of it's values, then the FK table can not have null as one of it's values either. (Actually, it wouldn't really make all that much sense anyhow, because even if there was a null vaue in the PK table, since null represents UNKNOWN, and UNKNOWN=UNKNOWN is always false, it still shouldn't work even if null was allowed in the PK table).

A workaround is to create an entry into the PK table for unassigned values, like "0" or "-1". Then make the column in the FK table, not-nullable, with a default constraint of "0" or "-1".

|||Another option is to remove the primary key on the primary key table, enter a null value in the old PK column, then apply a unique constraint on that column, then reestablish your FK relationship. Of course, if the old PK table column as of type identity, you'll have to remove that since identity columns can not contain null either.|||

Yeah thats the thing, i have the primary key column set as type Identity.

I like the first idea you mentioned, by putting a 0 or -1 in there replacing the null entry.

Could use the identity type below ( seed 0 and increment 1)

IDENTITY (0, 1)

then the null entry will be replaced by '0' by inserting a fake entry at the start.

Thanks for your help Motley i'll go and try this out.

Melanie

|||You can just remove the indentity from the column, insert your record, and add the identity back on the column too, that works too.|||

How would i carry that out?

I mean to add the identity back to the column after inserting a row of data, wouldn't this require me dropping the table which in turn removes the data entered previouly entered?

Could you tell me the way of doing this without dropping the table?

Thank you

Melanie

|||

I use management studio, so while technically you are correct, it hides it all in the background for me (Copying all the data), and if the table isn't too big, it's quick. But as far as I know, you are correct, you must create a new table to reinstate the identity.

|||

Thanks Motley

What do you suggest for me then?

Guess i'll have to go with '0' to replace the null entry.

Melanie

|||

You might try doing the insert by doing a SET IDENTITY_INSERT {Your table} ON then insert the record, then SET IDENTITY_INSERT {Your table} OFF