MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development
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.


Tutorials
| Forums

Download to Enter
| Contest Rules

DOWNLOAD INTEL® GPA FOR FREE

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old September 23rd, 2005, 10:54 PM
pbd22 pbd22 is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
 
Join Date: Jul 2005
Posts: 833 pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level) 
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

Reply With Quote
  #2  
Old September 24th, 2005, 03:46 PM
pbd22 pbd22 is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
 
Join Date: Jul 2005
Posts: 833 pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Week 23 h 32 m 32 sec
Reputation Power: 9
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

Reply With Quote
  #3  
Old September 25th, 2005, 12:04 PM
BillyDunny BillyDunny is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
 
Join Date: Sep 2003
Posts: 508 BillyDunny User rank is Sergeant (500 - 2000 Reputation Level)BillyDunny User rank is Sergeant (500 - 2000 Reputation Level)BillyDunny User rank is Sergeant (500 - 2000 Reputation Level)BillyDunny User rank is Sergeant (500 - 2000 Reputation Level)BillyDunny User rank is Sergeant (500 - 2000 Reputation Level) 
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...

Reply With Quote
  #4  
Old September 26th, 2005, 01:21 PM
pbd22 pbd22 is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
 
Join Date: Jul 2005
Posts: 833 pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Week 23 h 32 m 32 sec
Reputation Power: 9
Question 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.

Thanks again for your help.

Reply With Quote
  #5  
Old September 26th, 2005, 02:26 PM
pbd22 pbd22 is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
 
Join Date: Jul 2005
Posts: 833 pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Week 23 h 32 m 32 sec
Reputation Power: 9
Smile ok...

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)
         	

Reply With Quote
  #6  
Old September 26th, 2005, 02:34 PM
pbd22 pbd22 is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
 
Join Date: Jul 2005
Posts: 833 pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level) 
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...

Reply With Quote
  #7  
Old September 26th, 2005, 10:27 PM
BillyDunny BillyDunny is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
 
Join Date: Sep 2003
Posts: 508 BillyDunny User rank is Sergeant (500 - 2000 Reputation Level)BillyDunny User rank is Sergeant (500 - 2000 Reputation Level)BillyDunny User rank is Sergeant (500 - 2000 Reputation Level)BillyDunny User rank is Sergeant (500 - 2000 Reputation Level)BillyDunny User rank is Sergeant (500 - 2000 Reputation Level) 
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..."

or this:

VB.NET Code:
Original - VB.NET Code
  1. Private Function VerifyCredentials(ByVal emailAddress As String, ByVal password As String) As Boolean
  2.     Dim MyConn As SqlConnection = New SqlConnection("server=ARIA;database=dushkinmedia;Integrated Security=SSPI")
  3.  
  4.     Dim MyCmd As New SqlCommand("dbo.sp_ValidateUser", MyConn)
  5.     MyCmd.CommandType = CommandType.StoredProcedure
  6.     Dim objParam1 As SqlParameter = MyCmd.Parameters.Add("@emailAddress", SqlDbType.VarChar)
  7.     Dim objParam2 As SqlParameter = MyCmd.Parameters.Add("@password", SqlDbType.VarChar)
  8.  
  9.     objParam1.Value = _emailAddress.Text
  10.     objParam2.Value = _password.Text
  11.     Try
  12.         MyConn.Open()
  13.         Dim objReader As SqlDataReader
  14.         objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)
  15.  
  16.         Return objReader.Read()
  17.         objReader.Close()
  18.     Catch ex As Exception
  19.         lbTEMP.Text = ex.ToString 'tmp errorhandling
  20.         Return False
  21.     End Try
  22. End Function

Reply With Quote
  #8  
Old September 27th, 2005, 05:06 PM
pbd22 pbd22 is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
 
Join Date: Jul 2005
Posts: 833 pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level)pbd22 User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Week 23 h 32 m 32 sec
Reputation Power: 9
thanks again...

thanks BillyDunny,

that pointed me in the right direction. I have it working now.
Thanks again...

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > SQL/SPROC beginner: Loop logic problem


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.

© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 9 - Follow our Sitemap