Discuss SQL/SPROC beginner: Loop logic problem in the MS SQL Development forum on Dev Shed. SQL/SPROC beginner: Loop logic problem MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.
The ASP Free website provides in-depth information on the latest developer tools available from Microsoft. Our cadre of writers, highly experienced industry experts, reveals the best ways to use established technologies as well as new and emerging technologies. Our coverage of Microsoft's development and administration technologies is among the most respected in the IT industry today.
ASP Free and Iron Speed Designer are giving away $5,500+ in FREE licenses. Iron Speed's RAD CASE toolset can save up to 80% of your coding time. One free license per week, one perpetual license per month! Download and Activate to enter!
Intel® Graphics Performance Analyzers is a powerful tool suite for analyzing and optimizing your games, media, and graphics-intensive applications. Used by some of the best developers on the planet, Intel GPA lets you maximize your app’s performance.
Posts: 833
Time spent in forums: 1 Week 23 h 32 m 32 sec
Reputation Power: 9
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
Posts: 508
Time spent in forums: 4 Days 8 h 28 m 55 sec
Reputation Power: 15
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...
Posts: 833
Time spent in forums: 1 Week 23 h 32 m 32 sec
Reputation Power: 9
thanks...
Quote:
Originally Posted by BillyDunny
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".
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:
ALTER PROCEDURE dbo.sp_ValidateUser
(
@emailAddress VARCHAR(50) = NULL,
@password VARCHAR(50) = NULL,
@UserID INT = 0 OUTPUT
)
AS
SET @UserID = (SELECT COUNT(*) AS UserID
FROM dbo.Users
WHERE emailAddress = @emailAddress AND password = @password)
RETURN @UserID
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.
Posts: 833
Time spent in forums: 1 Week 23 h 32 m 32 sec
Reputation Power: 9
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...
Posts: 508
Time spent in forums: 4 Days 8 h 28 m 55 sec
Reputation Power: 15
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..."