Inserting Data Into Two Tables in ASP.NET
One of the most common questions on the net these days is how to do the following scenario:
“How do I insert data into one table, get the ID from that table and insert that ID and other data, into the second table?”
This tutorial uses the SQL Server managed provider for demonstration purposes. The most widely accepted ‘best’ way to accomplish this, would be to create a Stored Procedure in SQL Server to do it all for you, so that’s the direction this tutorial will take….
Here’s one I just snipped out of one of mine and edited a little:
CREATE PROCEDURE procInsSamples @CategoryID int, @Title varchar(100), @Description text, @Link varchar(150), @Whatever AS Begin Set NoCount on DECLARE @WhateverID INT Insert Table1(Title,Description,Link,Whatever) Values (@title,@description,@link,@Whatever) Select @WhateverID=@@Identity Insert into Table2 (CategoryID,WhateverID) Values (@CategoryID,@WhateverID) End
This way, you take input from a form (wherever) and insert it into table one – then the next line gets the ID from the newly entered data:
Select @WhateverID=@@Identity
The next section of the Stored Procedure inserts into Table two what is needed, including the new ID…..
So to do all this – First, you must make a connection to your database (SQL Server in this case):
Dim strConn as string = “server=(local);uid=sa;pwd=pwd;database=Northwind”
Dim MyConn as New SQLConnection(strConn)
Then, once you’ve made your connection to the database:
Dim MySQL as string = “YourStoredProcedure Name”
Next, we define the command:
Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
cmd.CommandType=CommandType.StoredProcedure
And add some parameters to the command:
cmd.Parameters.Add(New SQLParameter(“@CategoryID”, strCategory))
cmd.Parameters.Add(New SQLParameter(“@Title”, strTitle))
cmd.Parameters.Add(New SQLParameter(“@Description”, strDescription))
cmd.Parameters.Add(New SQLParameter(“@link”, strLink))
cmd.Parameters.Add(New SQLParameter(“@Whatever”, strWhatever))
(the variables come from assigning textbox input, from a form (or whatever), to each of them, in this case)
Then, to wrap it up, open the connection, execute the the command and close the connection:
MyConn.Open()
MyConn.ExecuteNonQuery()
MyConn.Close()
Of course here, you’d probably want to put some sort of text telling the user that his task has been accomplished, but basically — Voila – there you have it in a nutshell.




10. May, 2007 








No comments yet... Be the first to leave a reply!