Solved

Whats wrong with this query

Posted on 2016-10-19
21
38 Views
Last Modified: 2016-10-20
I have an app where I am creating users and one part of the app and if the user exists then I want to return a 0 and if they dont exists then return a non 0.... A 1 would be fine!!!!

Anyway heres my code in the application(ASP.NET)
Button click to Add User
    Protected Sub btnAddUser_Click(sender As Object, e As EventArgs) Handles btnAddUser.Click
        Try
            Dim u As UserProfile = New UserProfile
            With u
                .UserName = txtUserName.Text
                .Password = PasswordGenerator.Generate(8, 25)
                .Email = txtEmail.Text
                .SecurityQuestion = ""
                .SecurityAnswer = ""
                .PasswordReset = 0
                .DisableUser = 0
                Dim iAddUser As Integer = u.CreateUser(True)

                If iAddUser > 1 Then
                    If SendEmail() Then
                        MsgBox("User Created and an Email has been sent to New User " & txtUserName.Text & ".", vbOKOnly, "Email New User")
                    End If
                Else
                    MsgBox("User " & txtUserName.Text & " already exists.", vbOKOnly, "User Exists Already")
                End If
            End With
        Catch ex As Exception
            FailureText.Text = "btnAddUser_Click - Error Adding New User"
        End Try

    End Sub

Open in new window


Heres the code where I call the proc in one of my business objects
        Public Function CreateUser(Optional bAdmin As Boolean = False) As Integer

            Dim db As New DBAccess()

            db.AddParameter("@UserName", m_UserName)
            db.AddParameter("@Password", m_Password)
            db.AddParameter("@Email", m_Email)
            db.AddParameter("@Question", m_SecurityQuestion)
            db.AddParameter("@Answer", m_SecurityAnswer)
            db.AddParameter("@PasswordReset", m_PasswordReset)
            db.AddParameter("@DisableUser", m_DisableUser)

            Try
                Return db.ExecuteNonQuery("spInsert_NewUser", False)
            Catch ex As Exception
                Throw New ArgumentException("CreateUser - Error saving New User")
            End Try

        End Function

Open in new window


And heres my proc

ALTER PROCEDURE [dbo].[spInsert_NewUser]

@UserName VARCHAR,  
@Password VARCHAR,  
@Email VARCHAR,  
@Question VARCHAR,  
@Answer VARCHAR,  
@PasswordReset bit,
@DisableUser bit
	
AS

DECLARE @UserID INT, @newid INT
	 IF NOT EXISTS(SELECT UserID FROM Users WHERE UserName = @UserName)	
		BEGIN
		
			INSERT INTO dbo.Users 
				(UserName, Password, Email, Question, Answer, Password_Reset, Disable_User)
			VALUES
				(@UserName, @Password, @Email, @Question, @Answer, @PasswordReset, @DisableUser)
			
			SET @newid = @@IDENTITY;
			RETURN @newid;
		END
	ELSE
		BEGIN	
			UPDATE Users Set LastLogin = GETDATE() Where UserName = @UserName
			RETURN 0;
		END

Open in new window


You can see where I am trying to return the Number 0 or 1 and I am not getting the values Im looking for....Any help would be appreciated...
I want to evaluate the return number 0 or 1??
Thats all
If it returns a 0 then user already exists and if it returns a 1 then everything worked fine and the user was inserted...Im getting a 1 everytime though??
0
Comment
Question by:jknj72
  • 13
  • 8
21 Comments
 

Author Comment

by:jknj72
Comment Utility
Well if it already exists Im returning the next id in the table which is the primary key. I apologize because its not a 1...
Thanks
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
From the help files:
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

SqlCommand.ExecuteNonQuery Method ()
.NET Framework (current version)
Other Versions
 

Executes a Transact-SQL statement against the connection and returns the number of rows affected.
One new user is one row affected which means return 1.
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
You might want to lok at this:
https://msdn.microsoft.com/en-us/library/ms378108(v=sql.110).aspx

about returning values from stored procedures
0
 

Author Comment

by:jknj72
Comment Utility
So if I used ExecuteScalar would I get the values from the newID or 0 then right?
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
You should be OK with what you have at present, just return the ID via an OUT parameter.  The return from the ExecuteNonQuery you can still use to check if the actual operation went without problem.
0
 

Author Comment

by:jknj72
Comment Utility
Its returning a 1 everytime regardless of what happens? Let me make sure my code is correct

ALTER PROCEDURE [dbo].[spInsert_NewUser]

@UserName VARCHAR(50),  
@Password VARCHAR(50),  
@Email VARCHAR(50),  
@Question VARCHAR(100),  
@Answer VARCHAR(100),  
@PasswordReset bit,
@DisableUser bit,
@newid INT OUTPUT
	
AS

DECLARE @UserID INT
	 IF NOT EXISTS(SELECT UserID FROM Users WHERE UserName = @UserName)	
		BEGIN
		
			INSERT INTO dbo.Users 
				(UserName, Password, Email, Question, Answer, Password_Reset, Disable_User)
			VALUES
				(@UserName, @Password, @Email, @Question, @Answer, @PasswordReset, @DisableUser)			
			
			SET @newid = @@IDENTITY;
			RETURN @newid;
		END		
	ELSE
		BEGIN	
			UPDATE Users Set LastLogin = GETDATE() Where UserName = @UserName
			
			SET @newid =0;	
			RETURN @newid;		
		END

Open in new window


Its returning a 1 whether or not the record gets inserted or not..>Any clue on what I should do?
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
Please post what you now have for  Public Function CreateUser
0
 

Author Comment

by:jknj72
Comment Utility
        Public Function CreateUser(Optional bAdmin As Boolean = False) As Integer

            Dim db As New DBAccess()

            db.AddParameter("@UserName", m_UserName)
            db.AddParameter("@Password", m_Password)
            db.AddParameter("@Email", m_Email)
            db.AddParameter("@Question", m_SecurityQuestion)
            db.AddParameter("@Answer", m_SecurityAnswer)
            db.AddParameter("@PasswordReset", m_PasswordReset)
            db.AddParameter("@DisableUser", m_DisableUser)
            db.AddParameter("@newid", 0)

            Try
                Return db.ExecuteNonQuery("spInsert_NewUser")

            Catch ex As Exception
                Throw New ArgumentException("CreateUser - Error saving New User")
            End Try

        End Function

Open in new window

0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
You aren't checking the value in newid after executing the query.  The ID of the new user is returned in that variable, the number of rows affected is the return of the db.ExecuteNonQuery call.
0
 

Author Comment

by:jknj72
Comment Utility
Ahhhh...Ok thx
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jknj72
Comment Utility
let me check the value
0
 

Author Comment

by:jknj72
Comment Utility
Wait, im not sure how to check for it? A little help would be appreciated!
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
basically what the link showed you

in parameter   @newid, newid
execute query
the new user ID is in the variable newid
0
 

Author Comment

by:jknj72
Comment Utility
I added the output parameter to the CreateUser function but I do not know how to ssign it as an output param...

If you can you help me with this part Id really appreciate it because its really holding me up...
This is the CreateUser with @newid added
        Public Function CreateUser(Optional bAdmin As Boolean = False) As Integer

            Dim db As New DBAccess()

            db.AddParameter("@UserName", m_UserName)
            db.AddParameter("@Password", m_Password)
            db.AddParameter("@Email", m_Email)
            db.AddParameter("@Question", m_SecurityQuestion)
            db.AddParameter("@Answer", m_SecurityAnswer)
            db.AddParameter("@PasswordReset", m_PasswordReset)
            db.AddParameter("@DisableUser", m_DisableUser)
            db.AddParameter("@newid", m_newid)   '''Output param

            Try

                db.ExecuteNonQuery("spInsert_NewUser")

            Catch ex As Exception
                Throw New ArgumentException("CreateUser - Error saving New User")
            End Try

        End Function

Open in new window



In CreateUser it has db.Addparameter and here is my AddParameter Sub...Im assuming I need to add the Direction here but just for the @newid?.
        Public Sub AddParameter(paramname As String, paramvalue As Object)
            Dim param As New SqlParameter(paramname, paramvalue)
            cmd.Parameters.Add(param)
        End Sub

Open in new window


I simply dont know how to add it as an output param and I also dont know how to retrieve the output param after the ExecuteNonQuery?.

As far as the link, I think it may be a little different then what I am using because its using the  Microsoft JDBC Driver for SQL Server . It doesnt seem like its too different but maybe a little.
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
Have a read about using parameters:
https://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx

specifically about the direction.
0
 

Author Comment

by:jknj72
Comment Utility
I understand how it works but the way the class Im using "DBAccess" it  is throwing a curve ball because it is building the parameters on the fly and Im not sure where to do what? I am thinking, because I need to use the cmd object, that I need to go into the AddParameter Sub and check for the @newid param and when its being added then I set the direction of the parameter?
Here is where I added the param newid in the CreateUser sub
  Public Function CreateUser(Optional bAdmin As Boolean = False) As Integer

            Dim db As New DBAccess()

            db.AddParameter("@UserName", m_UserName)
            db.AddParameter("@Password", m_Password)
            db.AddParameter("@Email", m_Email)
            db.AddParameter("@Question", m_SecurityQuestion)
            db.AddParameter("@Answer", m_SecurityAnswer)
            db.AddParameter("@PasswordReset", m_PasswordReset)
            db.AddParameter("@DisableUser", m_DisableUser)
When I call this I am setting the direction in the AddParameter Sub
            db.AddParameter("@newid", m_newid)  'HERE!!!!!!!!!!!!!!!!

            Try

                db.ExecuteNonQuery("spInsert_NewUser")

            Catch ex As Exception
                Throw New ArgumentException("CreateUser - Error saving New User")
            End Try

        End Function

Open in new window

Heres the AddParameter Sub...
   Public Sub AddParameter(paramname As String, paramvalue As Object)
            Dim param As New SqlParameter(paramname, paramvalue)
            If paramname = "@newid" Then
                param.Direction = ParameterDirection.Output  '''HERE
            End If
            cmd.Parameters.Add(param)
        End Sub

Open in new window


 I tried setting the parameter but then after the ExecuteNonQuery how do I get the return value of newid and where should I be getting this? Please remember that the parameters are being built and its not a DataReader I am running an Insert and trying to get back the new value from the identity column, thats all... Can you tell me how to do this?
0
 

Author Comment

by:jknj72
Comment Utility
This is what I needed....

  Public Function ExecuteNonQuery(commandtext As String) As Integer
            Dim i As Integer '= 0
            Dim retval As Integer = 0
            Try
              cmd.CommandText = commandtext
               Me.Open()
                i = cmd.ExecuteNonQuery()
                retval = CInt(cmd.Parameters("@newid").Value)

            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return i
        End Function
0
 

Author Comment

by:jknj72
Comment Utility
Thanks for your help
0
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 500 total points
Comment Utility
db.AddParameter("@newid", m_newid)  'HERE!!!!!!!!!!!!!!!!

            Try

                db.ExecuteNonQuery("spInsert_NewUser")
check what is in m_newid at this point in your code.
0
 

Author Comment

by:jknj72
Comment Utility
Where you are telling me to check wont work because the ExecuteNonQuery is a Function that calls the real ExecuteNonQuery function so when the real function below is called I am accessing the return value...I believe thats where the disconnect lies. The m_newid is pretty much a placeholder for the output param and has no value going in...
This is being called from the CreateUser function above, where you wanted me to check for the value...Thats where I was getting confused...
'This is the real call to the ExecuteNonQuery
        Public Function ExecuteNonQuery(commandtext As String) As Integer
            Dim i As Integer '= 0
            Dim retval As Integer = 0
            Try
              cmd.CommandText = commandtext
               Me.Open()
                i = cmd.ExecuteNonQuery()
                retval = CInt(cmd.Parameters("@newid").Value)

            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return i
        End Function

Open in new window


You pointed me in the right direction so thank you for your help with this...
0
 

Author Closing Comment

by:jknj72
Comment Utility
thank you
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now