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.

Related Posts:

• No Related Posts