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;  
  
GO

Below 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

Recommended Answers

All 4 Replies

OK a few things.

1. You are good at copying and pasting. You took my code samples verbatim, but you forgot to correct the spelling mistakes. it is <summary> not <sumamry>.

2. If you are going to copy and paste code, be sure you understand what it is doing, and I thought my tutorials we as clear as I could be without causing too much confusion. (I will have to re-evalute them). :mrgreen:

3. Ok, just looking at your code answer me this: Why do you have some many return Parameters, when your Select statement will only return 1 value? That being the number of rows affected. In the approach you have taken it would require you to dim a parameter object for each column and each row. :) But good effort.

Lesson Time! You only create as many objects as you need, and you only create as many Parameter objects you require to both pass in values, and to return a value.

Inorder to return a value other than the number of rows affected, you must have a return value declared in your stored procedure. Which in your case you don't, and neither do you require it.

I would be interested in the error message you got for the second return parameter, as it would have be a very good indicator of what is wrong and how to fix it.

Saying that, I see you don't quite grasp the concept of a SELECT statements (i.e query) and a return statement in a stored procedure. A SELECT statement will retrieve a result set (a capture of the data you query for) based on the qualifiers (specifics) you set out. That result set is not a "value" per say, it can be if you want the number of records retrieved, but a query really is only a means of snapshoting a specific portion of a set of data. Make sense?

Well, when you run a SELECT statement in SQL outside of a stored procedure do you have a RETURN in it? NOPE! The return statement in a stored procedure does two things; returns control back from the server, and returns either a predefined value or the number of rows affected.


So you don't need this line of code:

objReturnParam1 = MyCmd.Parameters.Add("@First_Name", SqlDbType.VarChar)	End Try

And if you think about it logically, why would you return a value you just passed in as part of your WHERE clause. You would hope that if the query returned a FIRST_NAME is would be the same as the value passed in, since your WHERE clause requested FIRST_NAME = @First_Name !!!!!!!!!

So your code would look more like this:

NEW CODE:

Function DBConnection(ByVal sFirstName As String, ByVal sLastName As String, ByVal sSSN As String) As Boolean
	'<summary>
	' ||||| 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"))

	'<summary>
	' ||||| 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
	'<summary>
	' ||||| 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)
	objReturnParam1(MyCmd.Parameters.Add("@Num", SqlDBType.Int))
	'@Num is the count of Rows (Should be 1 or more if login parameters are found) matching the query

	' ||||| 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
	

	'' ||||| 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!"

4. If you have questions about examples/tutorials posted here or anywhere, ask. Just copying and pasting only goes so far, you have to understand how the code implements inorder ot make it work. Point in case right here. Feel free to ask, there is not a silly or dumb question, we all have to learn. :)

5. If you are going to copy and paste, try to correct the errors first. Hehehehe. :mrgreen:

6. When posting CODE please use the code placeholders (minus the quotation marks).

"[[/b]"[b]code[/b]"[b]]"

... (your lines of code here)
... (" " ")
...
"[[/b]"[b]/code[/b]"[b]]"


7. Now, I left one piece of the puzzle out. What you need to change in the stored procedure to make that new code I provided work. If you don't know what it is...take a closer look at the tutorial your code is from (see my signature below for the link), and good luck.

Happy Coding!!
:cool:

Using a stored Procedure in Authentication is quite complex.I meant if your a beginner and if you dont know the concepts of stored procedures.

If you want to learn the concepts of the program.DO ONE STEP AT A TIME.

Always break the entire code.

If you want to do the Authentication,do ONE STEP at a time.

Step 1:Create the database and create the stored procedure and test out how that works.

Step 2:Write a .NET application of using the stored procedure that you have created.Check out this article..Its really useful.

http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

Step 3:Now work on the authentication,

Refer the tutorial,
http://www.daniweb.com/tutorials/tutorial19303.html

If you need any help or tutorials just post the questions as a forum.

I know this post does not tell you how to solve your problem.
I'm just trying to help you.
REMEMBER ..NOBODY IS PERFECT IN THIS WORLD. :D

Agreed Letscode.

No one is perfect! Certainly not me!!!

Note though.... Please if you are going to copy and paste. Be sure you understand the code and how it works.

yeah.I agree.But thats how people think they learn to program by copying and pasting.But thats not the case.

One has to realise 99.999% ,the code does not work if you just copy and paste.
Some people in my work still think programmers copy and paste the code :)
LOL..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.