Automatic Insert of data into a related table

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

auto-increment nvarchar column

Hi experts!

The situation:I have a table containing the MemberID and related member's info. The MemberID is the pk and should be incremented for every new member. However, the ID is in "nvarchar" type, since it consists of an "IM" prefix. I used the following stored procedure to insert new member to the table.

CREATE PROCEDURE dbo.spInsertNewMember
@.parMemberTitle nvarchar(2),
@.parMemberFirstName nvarchar(40),
@.parMemberLastName nvarchar(40)
AS
DECLARE @.LastMemberID AS nvarchar(16)
DECLARE @.NextMemberID AS nvarchar(16)
SET @.LastMemberID = (SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName) VALUES (@.NextMemberID, @.parMemberTitle, @.parMemberFirstName, @.parMemberLastName)
RETURN

I want to ask if this sp can handle any concurrent insert to the MemberID table.

Thanks.

Hi,

why arent you doing the evaluation in the INSERT statement rather than separate ? This would minimize the concurrency effect.

(SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName)
SELECT 'IM'+ CAST(RIGHT(ISNULL(MAX(MemberID),'IM000000'),6) + 1 AS CHAR(6)),
@.parMemberTitle,
@.parMemberFirstName,
@.parMemberLastName
FROM MemberInfo

If you really want to be sure about this you have to specify a TABLOCK.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for your suggestion...

Since I have been using mysql for quite a while and thus not familiar with mssql.

what I am really looking for is something similar to "SELECT .... FOR UPDATE" in mysql.

I will try your suggest that later.

Thanks

auto incrementing->Updating values in two related tables :Help!

hi there,
i am new to sql server database.i am doing small projects rightnow using asp.net and sql to create webpages (very basic webpages)
My problem is:
Problem :

i have two tables ....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course .
table1 student id is the primary key refrencing table 2 student id.
Now i delete a record in table 1 which will in turn also get deleted intable 2 . so for eg if i have three records 1 ,2 and 3 ...then idelete 2 in table 1 ...i will have 1 and 3 in both table 1 and table2...now i want 3 to become 2 in both table 1 and table 2...so that idont have empty space between two student id's 1 and 3. so this is myproblem...if any one can help me out with suggestions pleasedo.
thank you all......
ahmed_ind
if you want my advice ... this is not a good solution or methodto follow .... you should not really care about the space ...once you create ID for a record and delete it, you should notreally care about it to have stabled data not to have confliced IDs.
if your concerns about the ID number that you will reach in 1 year ..you can avoid that when you create the ID columns and assign the datatype that has the range for what you know it will enough for you...... if i were you i will not bother myself with what are youlooking for.
By the way this is my idea from my experience and what database expertsthink..... but if some one has different opinion and beleive there isa better technique ... you can advise !!
|||hi fadil.....thanx for u r suggestion.
i wanted to have automation of operation at the background ; i.e. in the database (sql ) ...so i used identity function toauto-increment values of student id by one ....then i wanted to deleteby performing auto-decrement function.....so thats how i camewith the idea, i jus wanted to learn tough things...ok any way thanxagain for ur suggestion and if u have any way of solution for myquestion please do reply..thank u..
ahmed
|||look at this case and see what i mean
ID Name
1 Ahmed
2 John
3 Ali

if you deleted 'Ali' the decrement would be ok as it is the last one on the list but if you delete 'Ahmed' you will loose the primar key integritiy for John and Ali as they will be 1 and 2.....i beleive this is not a good methodlogy to do... you might injure your data !!|||hi fadil,
i get u...i understand what umean...ok then my logic is not a good one then...may be i have to trysomething else than auto decrementing.....
thanxanyway for the suggestions : i will take them and follow them now andin my future...

............ahmed