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()