• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

Whats wrong with this query

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
jknj72
Asked:
jknj72
  • 13
  • 8
1 Solution
 
jknj72Author Commented:
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
 
AndyAinscowCommented:
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
 
AndyAinscowCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jknj72Author Commented:
So if I used ExecuteScalar would I get the values from the newID or 0 then right?
0
 
AndyAinscowCommented:
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
 
jknj72Author Commented:
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
 
AndyAinscowCommented:
Please post what you now have for  Public Function CreateUser
0
 
jknj72Author Commented:
        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
 
AndyAinscowCommented:
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
 
jknj72Author Commented:
Ahhhh...Ok thx
0
 
jknj72Author Commented:
let me check the value
0
 
jknj72Author Commented:
Wait, im not sure how to check for it? A little help would be appreciated!
0
 
AndyAinscowCommented:
basically what the link showed you

in parameter   @newid, newid
execute query
the new user ID is in the variable newid
0
 
jknj72Author Commented:
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
 
AndyAinscowCommented:
Have a read about using parameters:
https://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx

specifically about the direction.
0
 
jknj72Author Commented:
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
 
jknj72Author Commented:
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
 
jknj72Author Commented:
Thanks for your help
0
 
AndyAinscowCommented:
db.AddParameter("@newid", m_newid)  'HERE!!!!!!!!!!!!!!!!

            Try

                db.ExecuteNonQuery("spInsert_NewUser")
check what is in m_newid at this point in your code.
0
 
jknj72Author Commented:
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
 
jknj72Author Commented:
thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now