Sending Data to a Stored Procedure Through Input Parameters

If you are working towards making your ASP.NET application more efficient and that application connects to an SQL Server database, you will want to create stored procedures that do basic things like adding records, deleting records, and other such tasks. With these types of stored procedures, you need to pass data into them. This technique shows you how to call a stored procedure that needs to have parameters passed into it.


USE IT The ASP.NET page created for this technique allows visitors to add a record to the Employees table. Instead of using an Insert statement in the ASP.NET code, a stored procedure is called to add the record.

That stored procedure has this definition:

CREATE PROCE @LastName va
DURE AddEmployee rchar(50),
@FirstName varchar(50), @BirthDate datetime, @Salary money,
@EmailAddress varchar(50) AS
Insert Into Employees (LastName, FirstName, BirthDate,
Salary, EmailAddress) values
@LastName, @FirstName, @BirthDate,
@Salary, @EmailAddress) GO
Notice that the stored procedure has five input parameters. Those parameters are used in the Insert statement in the stored procedure.

On the ASP.NET page, visitors enter the values for the new employee record into TextBox control. When they click the Button control, this procedure fires, adding the new record by calling the stored procedure:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs)
Within the procedure, you need these data objects:
Dim DBConn as SQLConnection Dim DBAdd As New SQLCommand
You need to start by connecting to the SQL Server database:
DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")
Next, you place the SQL syntax into the Command object that calls the stored procedure:

DBAdd.CommandText = "Exec AddEmployee " _
& "'" & Replace(txtLastName.Text, "'", """) _
&
& ''" & Replace(txtFirstName.Text, "'", """)
&
& "'" & Replace(txtBirthDate.Text, "'", """)
& HI,
& Replace(txtSalary.Text, "'", """) & ", "
& "'" & Replace(txtEmailAddress.Text, "'", """)
Notice that the call passes in five parameters, the values for the new record. A comma separate each parameter.

The Command object will connect to the database through the Connection object:

kdd.Connection = DBConn kdd.Connection.Open

and the stored procedure can be executed:

kdd.ExecuteNonQuery()

Adding SQL Server Data, ASP.net

When your ASP.NET application includes SQL Server database connectivity, you often will need to add records to the database through your ASP.NET pages. For example, you may track visitors as they visit your site. With each page they visit, you may want to add a hit record to your database. Or you may allow visitors to sign themselves up for a mailing list. In that case you would need to add their information to a database. This technique shows you how to add a record to an SQL Server database table.

The ASP.NET page presented with this technique allows visitors to add an employee record to the SQL Server database. Visitors enter information into the provided TextBox controls and click the Button control to add the new record to the database.

Defined on the page are TextBox controls like this one used to enter the new information through:

<asp:textbox
id="txtLastName" runat="Server"

This Button control allows visitors to submit their request:

<asp:button
id="butOK"
text=" OK "
onclick="SubmitEtn_Click" runat="server"
/>

When that button is clicked, this procedure fires:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs) To add a record, you will need a Connection object:

Dim DBConn as SQLConnection

and a Command object:

Dim DBAdd As New SQLCommand

The Connection object connects to the SQL Server database:

DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")

Next, you place into the CommandText property of the Command object the SQL Insert statement that adds the new record:

DBAdd.CommandText = "Insert Into Employees (" _
& "LastName, FirstName, BirthDate, Salary, " _
& "EmailAddress) values (" _
& "'" & Replace(txtLastName.Text, "'", """) _
& "', "
* "'" Replace(txtFirstName.Text, "'", """)
&
* "'" Replace(txtBirthDate.Text, "'", """)
&
Replace- (txtSalary.Text, "'", """) & ", " _
& "'" & Replace(txtEmailAddress.Text, "'", """) _
& “.).

The Command object will connect to the database through the Connection object: DBAdd.Connection = DBConn

That connection is opened:

DBAdd.Connection.Open

and the record is added to the database:

DBAdd.ExecuteNonQuery()

Iterating Through Records in an SQL Server Table

In many situations you no longer need to iterate through records in an SQL Server table in ASP.NET pages as you did with ASP. This is because you can now bind DataSet Tables directly to controls.

But on occasion you may still need to iterate through all the records in a DataSet Table so that they can be processed in some special way.

The DataSet Table object contains a Rows collection that allows you to iterate through each record it contains. This technique shows you how to create a loop so that you can process each record from an SQL Server table through a DataSet Table object.

The ASP.NET page presented with this technique iterates through each record in an Employees table using a For loop. The ID of each employee is placed into the Text property of this Label control as it is processed:

<asp:label id="1b1Message" runat="Server" />

The code that iterates through the records fires when the page loads:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)

Within that procedure, these data objects are needed:

Dim DBConn as SQLConnection

Dim DBCommand As SQLDataAdapter Dim DSPageData as New DataSet

You also need a variable that will be used in the For loop:

Dim I as Long

You start by connecting to the SQL Server database:

DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")

Then you retrieve all the employee records:

DBCommand = New SQLDataAdapter ("Select * from Employees", DBConn)

and place them into a DataSet table object:

DBCommand.Fill(DSPageData, "Emps")

Next, you initiate a For block that will go from 0 to the total number of records in the DataSet table minus 1. You retrieve the record count in the DataSet Table object through the Count property of the Rows collection:

For I = 0 To DSPageData.Tables("Emps").Rows.Count - 1

Then within the loop you can process each record. You would use the variable "I" to refer to the CU rrent row within the current iteration of the loop:

'Code to process record

lblMessage.Text = lblMessage.Text _
& "
Processed Record: "
& DSPageData.Tables("Emps"). _ Rows(I).Item("EmpID")

You would then move on to process the next record: Next