I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible?
My logical approach to this is:
1. Build a handler for the OnInsert event of the first table
2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource.
What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?
How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.
Sub SqlDataSource1_Inserted(sender as object, e as system.eventargs) handles sqldatasource1.Inserted
dim conn as new sqlconnection("{Your connect string or pull from web.config"})
dim cmd as new sqlcommand("INSERT INTO Table2(col1,col2,col3) VALUES (@.col1,@.col2,@.col3",conn)
cmd.parameters.add("@.col1",sqldbtype.varchar).value={something}
cmd.parameters.add("@.col2",sqldbtype.varchar).value={something else}
cmd.parameters.add("@.col3",sqldbtype.varchar).value={something 3}
conn.open
cmd.executenonquery
conn.close
end sub
The above is easy, but it's not wrapped in a transaction. So if the 2nd insert fails, you'll have an inconsistant data model (No coresponding record in table2).
You can also build a trigger on table1 to do the insert.
You can also build a stored procedure, and use it to do both inserts via the sqldatasource.
|||
Motley:
You can also build a stored procedure, and use it to do both inserts via the sqldatasource.
I actually already have the first table insert occurring with a stored procedure so that I can get the identity. I guess I could use that stored procedure to also enter the default data for the second table but then I would need to know the Identity from the first table to plug it into the second table. Not really that good with stored procedures. Would something like this work?
CREATE PROCEDURE [InsertTable]
@.StoreID Int,
@.Weight real,
@.Length real,
@.Name nvarchar(25),
@.myID int OUTPUT
AS
INSERT INTO [FirstTable] ([StoreID], [Weight], [Length]) VALUES (@.StoreID, @.Weight, @.Length )
SELECT @.myID = @.@.IDENTITY
INSERT INTO [SecondTable] ([Table1ID], [Name]) VALUES (@.myID, @.Name)
|||Yes, that should work fine. As a minor change, don't use @.@.IDENTITY, use SCOPE_IDENTITY() instead.
SET @.myID=SCOPE_IDENTITY()
There is some minor differences between the two, and what you really want is SCOPE_IDENTITY().
|||I tried this and got errors for the other values so I gave up and went with the other approach which seems to be working.
The stored procedure is more elegant but it was just complaining there was no value for the very first column, and I had the value specified/harcoded as '0'. I was very
