ASP.NET Stored Procedures

kwilliams · March 16th, 2006, 12:13 PM

I'm pretty much a newbie when it comes to stored procedures and ASP.NET, but I'm wanting to convert all of my recordsets to stored procedures throughout my site. I'm running into trouble with what should be a simple stored procedure.

This is what I classically would have done using a recordset:

Code:

<%
Response.Buffer = True
'Declare form variables
Dim strUsername As Object = trim(Request.Form("txtUsername"))
Dim strPassword As Object = trim(Request.Form("txtPassword"))

'Recordset
Dim objConn As Object, rsAccounts As Object
objConn = Server.CreateObject("ADODB.Connection")
objConn.Open (strConnAccounts)
rsAccounts = "SELECT * FROM tblAccounts WHERE Username = '" & strUsername & "' AND Password = '" & strPassword & "'"
rsAccounts = objConn.Execute(rsAccounts)
objConn.Close
rsAccounts = nothing
objConn = nothing

'Test output
Response.Write("FName: " & rsAccounts("FName") & "<br />")
Response.Write("LName: " & rsAccounts("LName") & "<br />")
%>

How can I accomplish this with using a Stored Procedure? I don't understand how I can use variables within an ASP.NET page within a Stored Procedure's WHERE clause. Thanks for any help.

KWilliams

jbenson001 · March 16th, 2006, 11:56 PM

Well, first you need to learn some fundamentals of sql. If you have sql server installed, look into the help which is called Books On Line. You can also get a beginners book on sql. The best way to learn is just to try.
You stored procedure can look something just like your select statement:

Create Procedure MyProcedure
@Username varchar(30),
@Password varchar(10)
As

Select *
From tblAccounts
Where UserName = @UserName

Then you can use the sqldataadapter(.NET 1.0 and 1.1) to call the sp. Just specify it when you go throgh the wizard. You can slso do it through code.
If you are using VS2005(.NET 2.0) then you can use the sqldatasource object.

Hope this gets you started...

Jim