How to change into stored Procedure

[benyam_dessu]

Dear friends:
I've copy the asp.net code from Unleashed book. I want to use this part of the code to take stored procedure rather than select statment. The stored procedure I write for is this:

*********************************************************
CREATE PROCEDURE authenticatedUser
(
@userName Varchar ( 20 ) ,
@userStatus Varchar (20),
@userPassword Varchar(20),
@jobTitle Varchar(20) Output,
@projectId Varchar(20) Output
)

AS

select projectId, jobTitle
From amcduser
where userName = @userName and userPassword = @userPassword and userStatus = "Active"
GO
*********************************************************

When the Login button is cliked the next event handler (from Asp.Net Unleashed book) will be executed. But I want to use my stored procedure instead of select statment.

How can I do that dear friends.

************************************************************
Sub button_Click(ByVal s As Object, ByVal e As EventArgs)
Dim dstEmployees As DataSet
Dim conNorthwind As SqlConnection ' Object
Dim dadEmployees As SqlDataAdapter ' no object
Dim arrvalues(1) As Object
Dim dvwEmployees As DataView
Dim intEmployeesIndex As Integer

'Get chached Dataview
dvwEmployees = Cache("amdcuser")
If dvwEmployees Is Nothing Then
dstEmployees = New DataSet()
conNorthwind = New SqlConnection("server=(local);database= childdatabase ;Trusted_Connection=yes")
dadEmployees = New SqlDataAdapter("select * From amcduser", conNorthwind)

dadEmployees.Fill(dstEmployees, "amdcuser")
dvwEmployees = dstEmployees.Tables("amdcuser").DefaultView()
dvwEmployees.Sort = "userName , userPassword"
Cache("amdcuser") = dvwEmployees
End If
'Find The Employee
arrvalues(0) = txtusername.Text
arrvalues(1) = txtpassword.Text
intEmployeesIndex = dvwEmployees.Find(arrvalues)
If intEmployeesIndex <> -1 Then
lbltest.Text = txtusername.Text & " , " & txtpassword.Text
Session("jobtitle") = dvwEmployees(intEmployeesIndex).Row("jobTitle")
Session("projectid") = dvwEmployees(intEmployeesIndex).Row("projectId")

lbltest.Text = Session("jobtitle") & "<>"
lbltest.Text &= Session("projectid")
Else
test.Text = "Employee Not Found"
End If
End Sub
*********************************************************

Regards,

Ben

[alc6379]

You have to make a SqlCommand object, and change the CommandType to "CommandType.StoredProcedure". Then when you initialize the SqlDataAdapter, you pass that SqlCommand to the SqlDataAdapter.

I just found this pretty good code snippet, from this site:

sqlConnection = new SqlConnection( "Integrated Security=yes;Initial Catalog=Northwind;Data Source=(local)" );

            //pass the stored proc name and SqlConnection
            sqlCommand = new SqlCommand( "Employee Sales by Country", sqlConnection );
            //important to set this as StoredProcedure is *not* the default
            sqlCommand.CommandType = CommandType.StoredProcedure;
    
            //instantiate SqlAdapter and DataSet
            sqlDataAdapter = new SqlDataAdapter( sqlCommand );

The example is in C#, but it illustrates how you'd use the SqlCommand instead of just feeding a string to the SqlDataAdapter.