Stored Procs

[Pete Newman]

Thanks to the help from yesterday i have managed to get thus far, just one stumbling block left if any one can help Code from login Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate Dim mydataSet As New DataSet Using connection As New SqlConnection("Data Source=TESTSQL;Initial Catalog=DevDataBase;Integrated Security=True") Dim adapter As New SqlDataAdapter() adapter.SelectCommand = New SqlCommand("Select Count(*) from DevDataBase.dbo.Operators where Name = '" & logon1.UserName & "' and Psw = '" & logon1.Password & "'", connection) adapter.Fill(mydataSet) MsgBox(mydataSet.Tables(0).Rows(0)(0)) End Using End Sub If the user name and password are correct then I get a count of 1. How would this work with a stored procedure Stored Procedure Name is 'OpersLogon' and the parameters supplied are @OperatorName varchar(20), @OperatorPassword varchar(20), @ReturnValue Int Output

I really do need help..... all this computer stuff is way over my head !!

[Are Jay]

Dim sqlConn As SqlClient.SqlConnection Dim command As SqlClient.SqlCommand Dim adapter As SqlClient.SqlDataAdapter Dim table As DataTable Try sqlConn = New SqlClient.SqlConnection("YOUR CONNECTION STRING") command = New SqlClient.SqlCommand("OpersLogon", sqlConn) With command .Parameters.AddWithValue("@OperatorName", Server.HtmlEncode(logon1.UserName)) .Parameters.AddWithValue("@OperatorPassword", Server.HtmlEncode(logon1.Password)) .CommandType = CommandType.StoredProcedure End With adapter = New SqlClient.SqlDataAdapter(command) table = New DataTable adapter.Fill(table) If CInt(table.Rows(0)(0)) <> 1 Then Throw New ApplicationException("Invalid User Credentials.") Catch ex As Exception ' Report or document the exception Finally If sqlConn.State <> ConnectionState.Closed Then sqlConn.Close() End Try Might not be the most efficaint code but I've been using it on a site for the past year and a-half with no troubles.

[Pete Newman]

That will work a treat, Its very similar to what i use in a standered VB6 application, :doh: just didnt think it would work on this for some reason. Thats the great thing about this site, theres always something more to learn :cool: Thanks again

I really do need help..... all this computer stuff is way over my head !!

[Pete Newman]

AreJay, seems like ive hit a small problem, what happens if the stored proc returns a integer , ie in the stored procedure its returning an int as an output parameter; in the stopred proc @ReturnValue INT OUTPUT

I really do need help..... all this computer stuff is way over my head !!

[Are Jay]

Pete Newman wrote:

@ReturnValue INT OUTPUT

table = New DataTable adapter.Fill(table) If CInt(table.Rows(0)(0)) <> 1 Then The output is being returned into the datatable then tested with table.Rows(0)(0). I understand that the stored proc may have a param of @ReturnValue INT OUTPUT, is this stopping the proc from running because your not assigning an output variable? I don't use an output param with my stored procs, I'll need to test the code I've posted and get back to you.

[Pete Newman]

cheers AreJay i know there has to be a way... but well its beyond me :doh:

I really do need help..... all this computer stuff is way over my head !!