 |
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

February 15th, 2006, 05:45 PM
|
Friend of Wrox
|
|
Join Date: Oct 2005
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
|
|
Checking Data Before Inserting
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:
Code:
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
__________________
Rit
www.designandonline.co.uk
INSPIRE | CREATE | DELIVER
|

February 16th, 2006, 02:19 PM
|
Friend of Wrox
|
|
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
|
|
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
|

February 16th, 2006, 04:09 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
In addition to what Jim said, you can also combine the two solutions:
Code:
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.
|

February 16th, 2006, 04:18 PM
|
Friend of Wrox
|
|
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
|
|
Imar.. I like that solution... seems much neater...
|

February 16th, 2006, 04:27 PM
|
Friend of Wrox
|
|
Join Date: Oct 2005
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
|
|
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
|

February 17th, 2006, 02:46 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
Cool. Thank you....
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|

February 25th, 2006, 09:31 AM
|
Friend of Wrox
|
|
Join Date: Oct 2005
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
|
|
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...
Code:
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
|

February 25th, 2006, 01:49 PM
|
Friend of Wrox
|
|
Join Date: Oct 2005
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
|
|
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.
Code:
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
|

February 26th, 2006, 07:53 AM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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:
Code:
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.
|

February 26th, 2006, 09:56 AM
|
Friend of Wrox
|
|
Join Date: Oct 2005
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
|
|
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
Code:
...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
Code:
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':
Code:
<asp:Parameter Name="returnValue"
Type="Int32"
Direction="ReturnValue" />
Can anyone see why it would want to return the value twice?
Ta
Rit
|
|
 |