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 not .
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).
"["code"]"
... (your lines of code here)
... (" " ")
...
"["/code"]"
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:
