|
Subject:
|
Checking Data Before Inserting
|
|
Posted By:
|
rit01
|
Post Date:
|
2/15/2006 4:45:17 PM
|
Hi All
I am developing an asp.net2 application and as part of it it will enable members to update their username and password that is stored within a user table within SQL Server.
What I need to make sure is that the username is unique to each record so I was looking for a solution that would check the username field BEFORE performing the insert.
I have been trawling the web to find the best practice to achieve this process and what I have come up with is to check the incoming username value within a stored procedure similar to the one below:
CREATE PROCEDURE dbo.CheckUname
@uname VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM Users WHERE uname = @uname)
RAISERROR('This value already exists.', 11, 1)
ELSE
INSERT users (uname) VALUES (@uname)
END
GO
My question are if you don't mind..
1. Does this sound like good practice?
2. Could someone guide me in the right direction as to how I would raise this error to the user within my ASP.Net2 page?
Many thanks
Rit
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
2/16/2006 1:19:54 PM
|
What I do is to call a stored procedure that looks for the user name, something like this:
declare @cnt int
Select @cnt = (Select count(*) from Users
Where UserName = @UserName)
I then return the count in an Outupt Paramater. Then I use that variable on the Page and do the error handling there.
Somthing Like:
If @returnval > 0 Then
Label1.Text = "UserName already exists"
Else
''Do Insert
End If
Jim
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/16/2006 3:09:29 PM
|
In addition to what Jim said, you can also combine the two solutions:DECLARE @returnValue int
IF EXISTS (SELECT 1 FROM Users WHERE uname = @uname)
SET @returnValue = -1
ELSE
INSERT users (uname) VALUES (@uname)
SET @returnValue = SCOPE_IDENTITY()
END
RETURN @returnValue This way, the sproc returns -1 when the user already exists, or its new Id when the insert succeeded.
Personally, I wouldn't raise an error for this, as technically, it's not an error. It's a situation you can check before you do the insert, and you should be able to handle it gracefully, instead of throwing exceptions.
Cheers,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
2/16/2006 3:18:54 PM
|
Imar.. I like that solution... seems much neater...
|
|
Reply By:
|
rit01
|
Reply Date:
|
2/16/2006 3:27:18 PM
|
Thanks both for your valuable input... things are starting to sink in now.
Imar, your latest book recommendation has been pre-ordered buddy.
Ta
Rit
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/17/2006 1:46:49 PM
|
Cool. Thank you....
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
Reply By:
|
rit01
|
Reply Date:
|
2/25/2006 8:31:51 AM
|
Hi All
What am I doing wrong? Based on the code from Imar I have adapted it to use to check user login info (authentication) too with the return value either being -1 if login details are wrong or their userID (record ID) if login details are correct.
When I use the sproc detailed below in my ASP.Net page the return value I get is 0 (the default RETURN value).. I have checked to make sure that the UserID column within my table is Integer too...
CREATE PROCEDURE GCGetIn
@username nvarchar(30),
@password nvarchar(30),
AS
DECLARE @ReturnValue int
IF EXISTS (SELECT [UserID] FROM UserTable INNER
WHERE Uname = @username AND [Password] = @password)
SET @returnValue = (SELECT [UserID] FROM UserTable
WHERE Uname = @username AND [Password] = @password)
ELSE
SET @ReturnValue = -1
RETURN @ReturnValue
GO
|
|
Reply By:
|
rit01
|
Reply Date:
|
2/25/2006 12:49:07 PM
|
The same with this code too... It just returns a 0 even if the first conditions are met and @returnValue is set to 1... it seems to ignore it.
DECLARE @returnValue int
IF EXISTS (SELECT [user_ID] FROM Users WHERE uname = @uname AND
[user_ID] <> @user_ID)
SET @returnValue = 1
ELSE
UPDATE [Users]
SET
[Uname] = @Uname,
[Password] = @Password
WHERE
[User_ID] = @User_ID
SET @returnValue = 0
RETURN @returnValue
... can anyone see what I am doing wrong?
Ta
Rit
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/26/2006 6:53:15 AM
|
I think this is my bad. I gave you an IF example with a multiple statement code block, without the proper use of BEGIN and END.
In T-SQL, you can use If ELSE END IF without BEGIN and END if you have a single statement, like a SELECT statement or something like SET @someVar = someValue.
However, if you need multiple statements, you need to wrap them in BEGIN and END blocks, similar to VB or the {} signs in languages like JavaScript ot C#.
So, in your code below, SET @returnValue = 0 is always executed as the UPDATE statement is the only statement executed for the Else clause
This should fix it:DECLARE @returnValue int
IF EXISTS (SELECT [user_ID] FROM Users WHERE uname = @uname AND
[user_ID] <> @user_ID)
BEGIN
SET @returnValue = 1
END
ELSE
BEGIN
UPDATE [Users]
SET
[Uname] = @Uname,
[Password] = @Password
WHERE
[User_ID] = @User_ID
SET @returnValue = 0
END
RETURN @returnValue HtH,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
Reply By:
|
rit01
|
Reply Date:
|
2/26/2006 8:56:16 AM
|
Thanks Imar that makes the return data behave, But my voyage of Return value discovery now takes a bazaar twist... sorry for the long one!..
I seem to get the return value twice within my ASP.NET page... I have checked over my code both in the page and Sproc but can't see why its doing it... have you ever come accross such a thing? The code is below...
SPROC
...sproc stuff
AS
DECLARE @returnValue int
IF EXISTS (SELECT [user_ID] FROM Users
WHERE uname = @uname AND [user_ID] <> @user_ID)
BEGIN
SET @returnValue = 1
END
ELSE
BEGIN
UPDATE [Users]
SET
[Uname] = @Uname,
[Password] = @Password
WHERE
[User_ID] = @User_ID
SET @returnValue = 2
END
RETURN @returnValue
ASP.NET CODE WITHIN UPDATED EVENT
Protected Sub SqlStatus_Updated(ByVal sender As Object,
ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
Handles SqlStatus.Updated
Dim ReturnStatus As Integer = _
Convert.ToInt32(e.Command.Parameters("@returnValue").Value)
SELECT CASE ReturnStatus
CASE 1
Response.Write("Username Already in Use")
CASE 2
Response.Write("Saved!")
END SELECT
End Sub
And then I have the following parameter within my UpdateParameters within my SqlDataSource 'SqlStatus':
<asp:Parameter Name="returnValue"
Type="Int32"
Direction="ReturnValue" />
Can anyone see why it would want to return the value twice?
Ta
Rit
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/27/2006 3:32:16 AM
|
What do you mean with "get the return value twice "??
Is the event handler SqlStatus_Updated firing twice?
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
Reply By:
|
rit01
|
Reply Date:
|
2/27/2006 4:00:04 AM
|
HI Imar
I have found the culprit!.. just now.
'Handles SqlGCIStatus.Updated' When I remove this from the event handler it returns the value once but when added I get it twice.
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/27/2006 4:04:23 AM
|
That's probably because you have an OnUpdated handler in the markup for the control as well.
In that case, the handler is hooked up twice, and it'll fire twice...
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
Reply By:
|
rit01
|
Reply Date:
|
2/27/2006 4:31:23 AM
|
Excellent, thanks for your help. One last thing if you don't mind..
Within this SELECT CASE statement I want to bind the result to a Label but for some reason it doesn't like it.. page still loads but the label is not showing any text..
SELECT CASE ReturnStatus
CASE 1
lblalert.Text = "Username Already in Use"
CASE 2
lblalert.Text = "Saved!"
END SELECT
And I declare my Label like so...
Dim lblalerttext As Label = _
CType(FormView1.FindControl("lblAlertsaved"), Label)
I have checked the value of the 'ReturnStatus' which either show 1 0r 2 (Integer)
Ta
Rit
|
|
Reply By:
|
rit01
|
Reply Date:
|
2/27/2006 4:33:01 AM
|
sorry lblalerttext = lblalert... just in my example above is wrong.
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/27/2006 12:47:21 PM
|
Where did you declare the label? Inside a template for the FormView? In that case,is it possible that the selected template is no longer visible after you performed the update? (That is, the Label is defined in the Update section??)
Can't you just add the Label to the page, outside the FormView and directly change its Text?
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
Reply By:
|
rit01
|
Reply Date:
|
2/28/2006 6:57:43 AM
|
Blimey it must be like helping a monkey... sorry about that... I have set it in the page and it works fine.
No disrespect to monkeys by the way!
Rit
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/28/2006 8:17:02 AM
|
No offense taken.... ;-)
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
