Table:

CustID int, primary key, Identity Specification = yes

CustName nvarchar(20)

When a customer is added the CustName field is passed to a stored procedure, therby adding the customer. The CustID is created automatically by the DB.

I need to grab the value of the CustID just created by the DB. There is no windows databinding class being used.

That value is needed for a foreign key into additional tables (billing adresses, phone numbers, etc.)

Any thoughts? I must be overlooking a simple mechanism here. I can't simply grab the record by CustName since there may be more than one company with the same name. Also, I don't want the user to do a secondary lookup of customers once the customer is added merely to obtain the correct CustID.

I want to add the record, snag the CustID, and move onto the next task.

Thanks in advance,

Lance

Thanks for the reply. It did not work for me. However, I took your suggestion of Scope_Identity, searched the forum, and discovered others with the same problem.

this lead to a solution which did work for me.

SqlCommand command = new SqlCommand("SELECT IDENT_CURRENT('tablename')", myConnection);
int newId = Convert.ToInt32(command.ExecuteScalar());

Here's one way (in VB 2005)

Private Sub InsertRow()

Dim conn as New SQLConnection([your connection string])

Dim cmdInsert as New SQLCommand([your insert command], conn)

Dim cmdSelect as New SQLCommand("SELECT Scope_Identity()", conn)

Dim newKey as integer

conn.Open()

cmdInsert.ExecuteNonQuery()

newKey = cmdSelect.ExecuteScalar

conn.Close()

End Sub

Thanks for the reply. It did not work for me. However, I took your suggestion of Scope_Identity, searched the forum, and discovered others with the same problem.

this lead to a solution which did work for me.

SqlCommand command = new SqlCommand("SELECT IDENT_CURRENT('tablename')", myConnection);
int newId = Convert.ToInt32(command.ExecuteScalar());