|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
SQL/SPROC beginner: Loop logic problem
hi.
i am getting some weird behaviour in my sql server 2000 code pasted below. When using the step-through, it seems that i get to the line: While objReader.Read() and then the compiler jumps to "End Try" without going inside the objReader.Read() statement. I am new to this and would appreciate some insight. why isn't entering the conditions within the while loop? I am new to sql and stored procedures so, i'd appreciate any advice at the moment. thanks in advance. Code:
Private Function VerifyCredentials(ByVal emailAddress As String, _
ByVal password As String) As Boolean
'<sumamry>
' ||||| Declare Required Variables
' ||||| Access appSettings of Web.Config for Connection String (Constant)
'</summary>
' ||||| First is the Connection Object for an Access DB
Dim MyConn As SqlConnection = New SqlConnection("server=ARIA;database=dushkinmedia;Integrated Security=SSPI")
'<sumamry>
' ||||| Create a OleDb Command Object
' ||||| Pass in Stored procedure
' ||||| Set CommandType to Stored Procedure
'</summary>
' ||||| To Access a Stored Procedure in Access - Requires a Command Object
Dim MyCmd As New SqlCommand("sp_ValidateUser", MyConn)
' ||||| To Access a Stored Procedure in SQL Server - Requires a Command Object
MyCmd.CommandType = CommandType.StoredProcedure
' ||||| Create Parameter Objects for values passed in
Dim objParam1, objParam2 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("@emailAddress", SqlDbType.VarChar)
objParam2 = MyCmd.Parameters.Add("@password", SqlDbType.VarChar)
'' ||||| Set the direction of the parameters...input, output, etc
objParam1.Direction = ParameterDirection.Input
objParam2.Direction = ParameterDirection.Input
'' ||||| Set the value(s) of the parameters to the passed in values
objParam1.Value = _emailAddress.Text
objParam2.Value = _password.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()
End If
' ||||| Create OleDb Data Reader
Dim objReader As SqlDataReader
objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)
' ||||| Close the Reader and the Connection Closes with it
'PROBLEM HERE: NEVER ENTERS CONDITIONS OF WHILE LOOP
While (objReader.Read())
If CStr(objReader.GetValue(0)) <> "1" Then
Return False
'lblMessage.Text = "Invalid Login!"
Else
objReader.Close() ' ||||| Close the Connections & Reader
Return True
End If
End While
Catch ex As Exception
lbTEMP.Text = ex.ToString 'tmp errorhandling
Return False
'lblMessage.Text = "Error Connecting to Database!"
End Try
|
|
#2
|
|||
|
|||
|
stored procedure
hi, i am adding the stored procedure that gets called from my function.
Code:
CREATE PROCEDURE dbo.sp_ValidateUser
(
@emailAddress VARCHAR(50) = NULL,
@password VARCHAR(50) = NULL,
@UserID INT = 0
)
AS
SET @UserID = (SELECT COUNT(*) AS UserID
FROM dbo.Users
WHERE emailAddress = @emailAddress AND password = @password)
RETURN @UserID
|
|
#3
|
|||
|
|||
|
My guess is that it is failing because you are returning just a variable and nothing for the recordset.
Try either using @UserID as an output parameter (also consider renaming the variable), or just doing a straight select (of anything) and then call with an "ExecuteScalar". Let me know if you need more help with either of those approaches... |
|
#4
|
||||
|
||||
|
Quote:
Hey, thanks for the reply. I am new to this so, did some reading up on what you suggested. I found information about the output part, and coded the following: Quote:
the above change to my stored procedure seems to most closely reflect what i read in the SQL book i have. After compile and run, i am still unsuccessful at login. as for a "stright select" I am not quite sure what you mean by that. Is this a rewrite of my stored procedure? Would you mind giving me an example? sorry, i am just getting going with stored procedures. Thanks again for your help. |
|
#5
|
|||
|
|||
|
I figured it out. I figured out what you ment by a "staight select". Thanks again for your advice. The below code did the trick:
Code:
ALTER PROCEDURE dbo.sp_ValidateUser (
@emailAddress VARCHAR(50) = NULL,
@password VARCHAR(50) = NULL
)
AS
SELECT dbo.Users.UserID
FROM dbo.Users
WHERE (dbo.Users.emailAddress = @emailAddress) AND (dbo.Users.password = @Password)
|
|
#6
|
|||
|
|||
|
woops...
i spoke too soon... arrrrgh. now it works sometime and doesn't work other time. very odd. but, it still skips out of the
While(objReader.Read()) loop. Seems the new code for the stored procedure still isn't doing the trick... |
|
#7
|
|||||
|
|||||
|
Well, "objReader.Read()" should return true or false...and if you're just checking that something was returned, you don't need to use a while, instead try "if objReader.Read() then..."
or this: VB.NET Code:
|
|
#8
|
|||
|
|||
|
thanks again...
thanks BillyDunny,
that pointed me in the right direction. I have it working now. Thanks again... |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > SQL/SPROC beginner: Loop logic problem |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|