Solved

Whats wrong with this query

Posted on 2016-10-19
21
50 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
ID: 41850515
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
ID: 41850664
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
ID: 41850668
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

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

Expert Comment

by:AndyAinscow
ID: 41850701
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
ID: 41850733
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
ID: 41850741
Please post what you now have for  Public Function CreateUser
0
 

Author Comment

by:jknj72
ID: 41850754
        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
ID: 41850767
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
ID: 41850983
Ahhhh...Ok thx
0
 

Author Comment

by:jknj72
ID: 41850985
let me check the value
0
 

Author Comment

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

Expert Comment

by:AndyAinscow
ID: 41851574
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
ID: 41851917
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
ID: 41852018
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
ID: 41852103
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
ID: 41852126
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
ID: 41852128
Thanks for your help
0
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 500 total points
ID: 41852176
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
ID: 41852246
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
ID: 41852291
thank you
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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