Checking for Primary Key assignment

Question

I have a web application that writes to several databases for tracking employee change requests. I am running into a problem with entering in a new employee. They are first written to main Employee database before their access information is written to the other databases with EMP_ID being the primary key. When it goes to write to the other databases EMP_ID has been generated yet so it is getting entered in as 0.

To resolve this I was trying to loop and check the EMP_ID value until a value is generated but I continue to get stuck in a loop because the query returns back that no value was found.

while (int.Parse(empIDChecker) == 0)
{
   dbConnection.Open();
   validateIDSQLString = "SELECT EMP_ID FROM EMPLOYEE_TABLE WHERE FIRST_NAME = '" +        firstNameTextBox.Text.Trim() + "' AND LAST_NAME = '" + lastNameTextBox.Text.Trim() + "'";

   SqlCommand updateSQLCmd = new SqlCommand(validateIDSQLString, dbConnection);
   SqlDataReader getRecords = updateSQLCmd.ExecuteReader();

   try
   {
       empIDChecker = getRecords["EMP_ID"].ToString();
   }
   catch
   {
       empIDChecker = "0";
   }

   getRecords.Close();
   dbConnection.Close();
}

How is your EMP_ID generated - is it a auto incrementing key? If so, you need to return this from your insert statement - something like SELECT SCOPE_IDENTITY() after the insert should do it. — Paddy, Apr 10 '12 at 14:38
P.S. you should also look at this: http://stackoverflow.com/questions/601300/what-is-sql-injection — Paddy, Apr 10 '12 at 14:39
Are you using multiple databases or multiple tables. And if they are multiple databases then I think using auto increment id is not a good idea — Habib, Apr 10 '12 at 14:40
When you do your first insert into the EMPLOYEE_TABLE for a new employee, you return SCOPE_IDENTITY() from the stored procedure or sql statement you are calling. You then feed this ID into all of your other inserts. If you show the code for the insert then I'm sure that someone will give you some more help. — dash, Apr 10 '12 at 14:54

score 1 · Answer 1 · answered Apr 10 '12 at 15:00

1

you can use

SELECT IDENT_CURRENT(‘tablename’)

This will give you the last inserted auto increment ID of the table, you can use that to insert in other table

Check this link as well http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

answered Apr 10 '12 at 15:00

Habib

219,104
29
407
436

If you look at that article, you'll see that it recommends always using SCOPE_IDENTITY() - the line above will return the last identity value inserted into that table, regardless of scope/session. If you run your proc twice at the same time, you're going to run into race conditions at some point. – Paddy Apr 11 '12 at 07:00

score 1 · Accepted Answer · answered Apr 10 '12 at 15:26

1

OK, so if your insert sproc looks something like:

sp_InsertEmp
...

INSERT INTO Emp(Name, etc...)
VALUES ('Paul', etc...)

SELECT SCOPE_IDENTITY() AS EMP_ID

GO

And in your code:

   SqlCommand insertCmd = new SqlCommand("sp_InsertEmp", dbConnection);

   ... Add parameters here and set type to StoredProcedure

   SqlDataReader dr= insertCmd.ExecuteReader();
   int newId;

   if (dr.Read())
   {
     newId = dr.GetInteger(0);
   }

answered Apr 10 '12 at 15:26

Paddy

33,309
15
79
114

In implementing this code, I keep getting this error thrown: "Invalid attempt to read when no data is present." And I can't figure out why. The query string is correct, it returns the data I need in the management studio. – Spacemancraig Apr 10 '12 at 16:25
If you post some of your code in your question as an edit, we could have a look. – Paddy Apr 11 '12 at 06:58
This was simply a case of me over looking something simple. It's actually missing from the current code I posted. I forgot to actually .Read() the data... – Spacemancraig Apr 11 '12 at 14:36

Checking for Primary Key assignment

2 Answers2