INSERT into two tables?

[rgouette]

Howdy!

vb.net (forms) app using INSERT statement into table, tha contains an ID column that's
an Identity column.
I have another table related by said column(it's the foreign key, and NON Identity)

In SSMS,, the relationship states that Delete & Update Rule(s) = Cascade

When I run the INSERT statement, I get nothing in table#2
no errors, just no new row

I'm not a guru, so maybe I'm expecting something that's not meant to be by issuing one INSERT statement and expecting
two tables to receive the row

Thanks lads,
Rich

 

[rgouette]

ok, actually I now have two INSERT statements , and I am getting a new row in each table:
however the primary key column values are not matching up
Which is not of course, good.

I assume that in the primary table, the ID column will grab the next int(identity=Yes)
but how do I have the INSERT for the foreign table match it?

hmmm

 

[jmcilhinney]

Those delete and update rules are useless to you in that scenario because you aren't deleting or updating. If you were to generate a typed DataSet from your database then those rules would propagate to it and then the update rule would come into play. When you inserted the parent record, the table adapter would update the parent DataTable with the new ID from the database and the update rule would then push that value to the child record(s), after which you save them to the database.

If you don't want to create a typed DataSet then you can do basically the same thing with an untyped DataSet and data adapters, but you have to do a bit more work yourself. You have to put the parent and child DataTables in a DataSet and create a DataRelation between them. You can configure that DataRelation with similar delete and update rules. You then need to make sure that you refresh the DataTables when inserting new records. To do that, the CommandText of your InsertCommand has to look something like this:

INSERT INTO SomeTable (Column1, Column2) VALUES (@Column1, @Column2);
SELECT ID = SCOPE_IDENTITY()

where ID is your PK column.

 

[rgouette]

ok, lemme give that a whirl,
thanks much
Rich

 

[rgouette]

hmm, I'm guessing I'm screwing up this thing:

Cannot insert the value NULL into column 'userID', table 'moderntraining.dbo.certStatus'; column does not allow nulls. INSERT fails.

Here's what I tried:

cmd.CommandText = "INSERT INTO TABLE#2(userID,fullName) VALUES((SELECT userID = SCOPE_IDENTITY()),@newUserVal)"

the syntax doesn't make sense to me, but I'm not a guru.

any thoughts?

Rich

 

[jmcilhinney]

You have not done what I said. Read my post again and this time follow the instructions provided. Note that the change I specified to the InsertCommand is for the parent adapter, not the child.