Discussion Locked

Foreign Key Not Populated

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an .ASP page that you enter in data directly into a SQL Server 7 DB. Data can be entered but the Primary Key which is an Identity Seed does not populate the Foreign Key in another table, it leaves it Null. Does anyone know why? Or can anyone tell me if I need something which will add the data from the Primary Key to the Foreign Key?

Thanks,
Nate

4 replies

by steved

I'd need quite a bit more detail about what you're actually doing for a thorough reply, but here's a brief intro to the subject that may help. I presume you're inserting a new record into the PK table and then either updating an existing row in another table with the FK value or inserting a new row in the other table. In either case the FK column won't get populated automatically. You have to retrieve the value of the newly inserted row's identity column by getting @@IDENTITY, then use that value to populate the FK column in the other table.

This kind of operation is probably best accomplished by calling a stored procedure rather than executing SQL directly from your ASP page. But it will generally work the other way too. I have had pages that experienced occasional failures in retrieving @@IDENTITY when passing SQL directly to the db. A stored procedure is more reliable.

I hope this helps.

Steve Diamond

by steved

Here's more detail about using a stored procedure. Call your PK table Table_A and your FK table Table_B. Here are simplified structures:

Table_A
-------
Column_A1 int identity(1,1) primary key,
Column_A2 varchar(50)

Table_B
-------
Column_B1 int identity(1,1) primary key,
Column_B2 varchar(50),
Column_B3 int

Assume that Column_B3 is the foreign key referring to Column_A1 in Table_A. I also assume that you know the value of the key to the row in Table_B that you wish to update when inserting a new row into Table_A. (If this key value can be derived from nothing but the information in the new row in Table_A, then an insert trigger on Table_A could be used instead of this approach.) Your SP would look something like this:

Create Proc Insert_A @A2 varchar(50), @B1 int
As
Declare @B3 int

Insert into Table_A (Column_A2)
Values (@A2)

Select @B3 = @@IDENTITY

Update Table_B
Set Column_B3 = @B3
Where Column_B1 = @B1

There are two ways to call a stored procedure from your ASP page. You can create a command object, fill in the parameters, and call its Execute method (more code but more efficient). Or you can use the Execute method of the Connection object with a constructed SQL string. I'll give you the second method for brevity's sake. Assume that Conn1 is an existing open connection to your database, strA2 contains the data for Column_A2, and intB1 contains the key value for the row in Table_B to be updated:

SQL = "Exec Insert_A '" & strA2 & "', " & CStr(intB1)
Conn1.Execute SQL

This discussion is closed to new replies.