Wrox Programmer Forums
|
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
 
Old February 15th, 2006, 05:45 PM
Friend of Wrox
 
Join Date: Oct 2005
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default 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
 
Old February 16th, 2006, 02:19 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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




 
Old February 16th, 2006, 04:09 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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

Imar.. I like that solution... seems much neater...

 
Old February 16th, 2006, 04:27 PM
Friend of Wrox
 
Join Date: Oct 2005
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

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
 
Old February 17th, 2006, 02:46 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Cool. Thank you....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old February 25th, 2006, 09:31 AM
Friend of Wrox
 
Join Date: Oct 2005
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

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

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

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

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
pre checking of submitted data bond007 PHP How-To 1 October 15th, 2008 11:24 PM
Data reader checking.... janees ASP.NET 1.0 and 1.1 Professional 1 March 11th, 2007 06:09 AM
Inserting data when data already exsist desireemm SQL Language 0 January 16th, 2006 11:01 PM
Checking a Database before Inserting a Record vbmazza VB Databases Basics 2 April 28th, 2005 07:23 AM
Compile Time Data Type Checking c3nty@yahoo.com C# 0 September 18th, 2003 01:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.