Here is my Stored Procedure stored in SQl DB.
CREATE PROCEDURE qparmVerifyLogin @First_Name varchar(30), @Last_Name varchar(30), @SSN varchar(9)
AS
Select First_Name, Last_Name, SSN, ADDRESS1, CITY, STATE, POSTAL, DEPTID, EMPLID
FROM PERSONAL_DATA_TBL
WHERE First_Name = @First_Name AND Last_Name = @Last_Name
AND SSN = @SSN;
GOBelow is the code that I have used from a similar example on this site:
it errors out at the 2nd return parameter. Can anyone suggest wither a change in my .net code or a change in my stored proc that would make this work? I'm just trying to pass in a first name, last name, SSN, and validate that based upon these entries a user does exist and grab the record for that user and display in on a page called default.aspx. Thanks so much! :D
Function DBConnection(ByVal sFirstName As String, ByVal sLastName As String, ByVal sSSN As String) As Boolean
'<sumamry>
' ||||| Declare Required Variables
' ||||| Access appSettings of Web.Config for Connection String (Constant)
'</summary>
' ||||| This is the Connections Object for an SQL DB
Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
'<sumamry>
' ||||| Create a OleDb Command Object
' ||||| Pass in Stored procedure
' ||||| Set CommandType to Stored Procedure
'</summary>
' ||||| To Access a Stored Procedure in SQL Server - Requires a Command Object
Dim MyCmd As New SqlCommand("qparmVerifyLogin", MyConn)
MyCmd.CommandType = CommandType.StoredProcedure
' ||||| Create Parameter Objects for values passed in
Dim objParam1, objParam2, objParam3 As SqlParameter
' ||||| Create a parameter to store your Return Value from the Stored Procedure
Dim objReturnParam1, objReturnParam2, objReturnParam3, objReturnParam4, objReturnParam5, objReturnParam6, objReturnParam7, objReturnParam8, objReturnParam9 As SqlParameter
'<sumamry>
' ||||| Add the parameters to the parameters collection of the
' ||||| command object, and set their datatypes (OleDbType in this case)
'</summary>
objParam1 = MyCmd.Parameters.Add("@First_Name", SqlDbType.VarChar)
objParam2 = MyCmd.Parameters.Add("@Last_Name", SqlDbType.VarChar)
objParam3 = MyCmd.Parameters.Add("@SSN", SqlDbType.VarChar)
'returning
objReturnParam1 = MyCmd.Parameters.Add("@First_Name", SqlDbType.VarChar)
' ||||| Set the direction of the parameters...input, output, etc
objParam1.Direction = ParameterDirection.Input
objParam2.Direction = ParameterDirection.Input
objParam3.Direction = ParameterDirection.Input
objReturnParam1.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
objReturnParam2.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
objReturnParam3.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
objReturnParam4.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
objReturnParam5.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
objReturnParam6.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
objReturnParam7.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
objReturnParam8.Direction = ParameterDirection.ReturnValue ' Note RETURNVALUE
'' ||||| Set the value(s) of the parameters to the respective source controls
objParam1.Value = txtFirstName.Text
objParam2.Value = txtLastName.Text
objParam2.Value = txtSSN.Text
' ||||| Try, catch block!
Try
' ||||| Check if Connection to DB is already open, if not, then open a connection
If MyConn.State = ConnectionState.Closed Then
' ||||| DB not already Open...so open it
MyConn.Open()
MyCmd.ExecuteNonQuery()
End If
' ||||| Was the return value greater than 0 ???
If objReturnParam1.Value < 1 Then
lblMessage.Text = "Invalid Login!"
Else
Return True
End If
' ||||| Close the Connection Closes with it
MyConn.Close()
Catch ex As Exception
lblMessage2.Text = "Error Connecting to Database!"
End Try
