Stored Procedure - check if user (email exists) if not Insert

Report · Apr 21, 2008

What is the proper way to do this.

I want to use a Stored Procedure to register a new user and their
information. However, if the user exists, the user should get a message
back saying that that email is already taken and they should be given a
chance to try a different user name.

I've done this many times with the built in functionality of Dreamweaver
but never with SPs.

Links? Help?

Also, if you have a section on the same page like Billing address and
another section like Shipping address, is it better to use one form for
both and set the Stored Procedure to add them both?

Thanks

Report · Apr 21, 2008

I should ad that I am somewhat familiar with using Stored Procedures but
not all that versatile. In other words, I use SPs for your basic
queries and have a little experience adding and deleting records but
this is somewhat more.

Thanks in advance.

Report · Apr 22, 2008

I would look for something like

IF EXISTS ( SELECT * FROM users WHERE userid = @userid )
BEGIN

code here

ELSE

code here

END

I have not tried it but something like this should work

Report · Apr 23, 2008

>>Also, if you have a section on the same page like Billing address and
>>another section like Shipping address, is it better to use one form for
>>both and set the Stored Procedure to add them both?

Yes, and if the inserts are to two or more tables use TRANSACTIONS to ensure
that all or none are inserted.

"Art" <lee_*nospamification@artjunky.com> wrote in message
news:fuio3v$5ra$1@forums.macromedia.com...
> What is the proper way to do this.
>
> I want to use a Stored Procedure to register a new user and their
> information. However, if the user exists, the user should get a message
> back saying that that email is already taken and they should be given a
> chance to try a different user name.
>
> I've done this many times with the built in functionality of Dreamweaver
> but never with SPs.
>
> Links? Help?
>
> Also, if you have a section on the same page like Billing address and
> another section like Shipping address, is it better to use one form for
> both and set the Stored Procedure to add them both?
>
> Thanks

Report · Apr 23, 2008

Below is my meager attempt to figure this out. When I plugged this in to
msSQL 2000, it didn't show any errors when I checked sytax; however,
when I committed it by clicking "OK," I got this error:

Error -21037 (SQL DMO) The Name specified in the Text property's 'Create
... statement must match the Name property and must be followed by valid
TSQL statements

-------------------------------------
CREATE Procedure Insertuser
@varUser varchar (50),
@varPass varchar (20)
As

BEGIN TRANSACTION

IF NOT EXISTS (
SELECT EMail
FROM atblCust
WHERE EMail = @varUser
)
BEGIN
INSERT INTO atblCust (EMail, Password)
VALUES (@varUser, @varPass)
END
GO
IF @@ERROR <> 0
BEGIN
--ROLLBACK THE TRANSACTION
ROLLBACK
--RAISE AN ERROR AND RETURN
RAISERROR (13000,'Could not insert user', 16, 1)
RETURN
END
COMMIT

-----------------------------
After all this, I realize that all of this will only get me so far. What
I am worried about, from what I've read, if an error occurs while
submitting, if the last one that is executed does not error out, but
there were errors prior to it, no errors will be returned?

Any ideas?

Thanks
-----------------------------

Pat Shaw wrote:
> Yes, and if the inserts are to two or more tables use TRANSACTIONS
to ensure
> that all or none are inserted.

Report · Apr 30, 2008

Pat Shaw wrote:

> Yes, and if the inserts are to two or more tables use TRANSACTIONS to ensure
> that all or none are inserted.
Below is a simplified version of what I came up with. It works. In other
words, if it all transacts, it places the info in the database if not,
then it should raise an error.

However, I don't know how to pull the RAISEDERROR bit out into the page.
Do I somehow use RAISEERROR as a variable?

On the page that writes to the database, I use a recordset and select
"Stored Procedure" instead of "command" and stored procedure (because of
the bug in Dreamweaver)

With that in mind, how do I pull the error out so that I can send the
user to an alternate page that indicates that there was an error and to
try again?

Man, there really needs to be a book about this stuff. Know of one? Want
to write one?

Anyways, Below is my stored procedure; notice at the bottom where it
triggers the error.

CREATE Procedure xxx_Insert
@varFName varchar (50),
@varLName varchar (50),
@varPassWord varchar (20),
@varEmail varchar (50),
@varShipName varchar (100),
@varShipSchool varchar (100)

As

BEGIN TRANSACTION

IF NOT EXISTS (
SELECT EMail
FROM Customer
WHERE EMail = @varEmail
)
BEGIN
INSERT INTO Customer (FName, LName, Email, Password)
VALUES (@varFName, @varLName, @varEmail, @varPassword)
END
BEGIN
INSERT INTO ShipAdd (ShipName, ShipSchool)
VALUES (@varShipName, @varShipSchool)
END

IF @@ERROR <> 0
BEGIN
--ROLLBACK THE TRANSACTION
ROLLBACK TRANSACTION
--RAISE AN ERROR AND RETURN
RAISERROR ('Could not insert user', 16, 1)
RETURN
END
COMMIT TRANSACTION
GO

Report · Apr 30, 2008

Art wrote:
> Pat Shaw wrote:
>
>> Yes, and if the inserts are to two or more tables use TRANSACTIONS to
>> ensure that all or none are inserted.
> Below is a simplified version of what I came up with. It works. In other
> words, if it all transacts, it places the info in the database if not,
> then it should raise an error.
>
> However, I don't know how to pull the RAISEDERROR bit out into the page.
> Do I somehow use RAISEERROR as a variable?
>
> On the page that writes to the database, I use a recordset and select
> "Stored Procedure" instead of "command" and stored procedure (because of
> the bug in Dreamweaver)
>
> With that in mind, how do I pull the error out so that I can send the
> user to an alternate page that indicates that there was an error and to
> try again?
>
> Man, there really needs to be a book about this stuff. Know of one? Want
> to write one?
>
> Anyways, Below is my stored procedure; notice at the bottom where it
> triggers the error.
>
>
> CREATE Procedure xxx_Insert
> @varFName varchar (50),
> @varLName varchar (50),
> @varPassWord varchar (20),
> @varEmail varchar (50),
> @varShipName varchar (100),
> @varShipSchool varchar (100)
>
> As
>
> BEGIN TRANSACTION
>
> IF NOT EXISTS (
> SELECT EMail
> FROM Customer
> WHERE EMail = @varEmail
> )
> BEGIN
> INSERT INTO Customer (FName, LName, Email, Password)
> VALUES (@varFName, @varLName, @varEmail, @varPassword)
> END
> BEGIN
> INSERT INTO ShipAdd (ShipName, ShipSchool)
> VALUES (@varShipName, @varShipSchool)
> END
>
> IF @@ERROR <> 0
> BEGIN
> --ROLLBACK THE TRANSACTION
> ROLLBACK TRANSACTION
> --RAISE AN ERROR AND RETURN
> RAISERROR ('Could not insert user', 16, 1)
> RETURN
> END
> COMMIT TRANSACTION
> GO

You need to define an output parameter, and then pull that from your page.

Steve

Report · Apr 30, 2008

So how do I do that?
>>
>> IF @@ERROR <> 0
>> BEGIN
>> --ROLLBACK THE TRANSACTION
>> ROLLBACK TRANSACTION
>> --RAISE AN ERROR AND RETURN
>> RAISERROR ('Could not insert user', 16, 1)
>> RETURN
>> END
>> COMMIT TRANSACTION
>> GO
>
> You need to define an output parameter, and then pull that from your page.
>
> Steve

Report · Apr 30, 2008

>> Steve

Perhaps you can help? On my register form, I have one form that I want
written to two different tables in my database. How would you do this?
So far, I am trying to do it using SPs and can get it to store but I
can't get past that.

Report · Apr 30, 2008

Art wrote:
>
>>> Steve
>
> Perhaps you can help? On my register form, I have one form that I want
> written to two different tables in my database. How would you do this?
> So far, I am trying to do it using SPs and can get it to store but I
> can't get past that.

I'm also finding that If someone does a refresh on that page, they add
duplicate files to the database; of course I don't want that but I don't
know how to stop it.

To recap:

1. I can use my SP to store to both tables
2. I can't pull out an error if the SP fails
3. I can't keep the user from refreshing and adding multiple entries

Number 3 is a bit annoying because part of the SP is to check if the
user already exists.??? Yet, it allows the refresh to add two more of
the same record.

I'm officially frustrated. I just don't have enough time to both learn
this stuff AND get it working.

Report · Apr 30, 2008

Art wrote:

> 1. I can use my SP to store to both tables
> 2. I can't pull out an error if the SP fails
> 3. I can't keep the user from refreshing and adding multiple entries

Let me add a new one.

I changed the SP so that it checks each INSERT individually and when

INSTEAD OF THIS:

BEGIN TRANSACTION

IF NOT EXISTS (
SELECT EMail
FROM Customer
WHERE EMail = @varEmail
)
BEGIN
INSERT INTO Customer (FName, LName, Email, Password)
VALUES (@varFName, @varLName, @varEmail, @varPassword)
END
BEGIN
INSERT INTO ShipAdd (ShipName, ShipSchool)
VALUES (@varShipName, @varShipSchool)
END

IF @@ERROR <> 0
BEGIN
--ROLLBACK THE TRANSACTION
ROLLBACK TRANSACTION
--RAISE AN ERROR AND RETURN
RAISERROR ('Could not insert user', 16, 1)
RETURN
END
COMMIT TRANSACTION
GO

------------------------- ID DID The following. I don't know what the
error is so I can't fix it. I'm guessing that's it's in the first insert
but who knows?

BEGIN TRANSACTION

IF NOT EXISTS (
SELECT EMail
FROM Customer
WHERE EMail = @varEmail
)
BEGIN TRANSACTION
INSERT INTO Customer (FName, LName, Email, Password)
VALUES (@varFName, @varLName, @varEmail, @varPassword)
IF (@@ERROR <> 0) GOTO ERR_HANDLER

INSERT INTO ShipAdd (ShipName, ShipSchool)
VALUES (@varShipName, @varShipSchool)
IF (@@ERROR <> 0) GOTO ERR_HANDLER

COMMIT TRANSACTION
RETURN 0

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRANSACTION
RETURN 1

GO

Report · May 01, 2008

Art wrote:
> Art wrote:
>
>> 1. I can use my SP to store to both tables
>> 2. I can't pull out an error if the SP fails
>> 3. I can't keep the user from refreshing and adding multiple entries
>
> Let me add a new one.
>
> I changed the SP so that it checks each INSERT individually and when
>
> INSTEAD OF THIS:
>
> BEGIN TRANSACTION
>
> IF NOT EXISTS (
> SELECT EMail
> FROM Customer
> WHERE EMail = @varEmail
> )
> BEGIN
> INSERT INTO Customer (FName, LName, Email, Password)
> VALUES (@varFName, @varLName, @varEmail, @varPassword)
> END
> BEGIN
> INSERT INTO ShipAdd (ShipName, ShipSchool)
> VALUES (@varShipName, @varShipSchool)
> END
>
> IF @@ERROR <> 0
> BEGIN
> --ROLLBACK THE TRANSACTION
> ROLLBACK TRANSACTION
> --RAISE AN ERROR AND RETURN
> RAISERROR ('Could not insert user', 16, 1)
> RETURN
> END
> COMMIT TRANSACTION
> GO
>
>
> ------------------------- ID DID The following. I don't know what the
> error is so I can't fix it. I'm guessing that's it's in the first insert
> but who knows?
>
> BEGIN TRANSACTION
>
> IF NOT EXISTS (
> SELECT EMail
> FROM Customer
> WHERE EMail = @varEmail
> )
> BEGIN TRANSACTION
> INSERT INTO Customer (FName, LName, Email, Password)
> VALUES (@varFName, @varLName, @varEmail, @varPassword)
> IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
> INSERT INTO ShipAdd (ShipName, ShipSchool)
> VALUES (@varShipName, @varShipSchool)
> IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
> COMMIT TRANSACTION
> RETURN 0
>
> ERR_HANDLER:
> PRINT 'Unexpected error occurred!'
> ROLLBACK TRANSACTION
> RETURN 1
>
> GO

Hi Art,
Not ignoring you, but will have to have a think about this, as I haven't
used stored procedures in this manor before. I have some code that is
coming back to me today, as its been used on a laptop at a tradeshow for
user registration and clock in, so will grab the sql that I have been
using and post it here to show you, along with the asp to get the
returned data.

Steve

Report · May 01, 2008

> Hi Art,
> Not ignoring you, but will have to have a think about this, as I haven't
> used stored procedures in this manor before. I have some code that is
> coming back to me today, as its been used on a laptop at a tradeshow for
> user registration and clock in, so will grab the sql that I have been
> using and post it here to show you, along with the asp to get the
> returned data.
>
> Steve

Steve,

Thanks

Report · May 05, 2008

Steve (Dooza),

If you build a cart, how do you go about adding billing and shipping
information to a cart? Do you have a page for shipping information and
then have a separate page for billing?

Also, if I remember right, the new version 4 of Webassist cart includes
a shipping component that creates a template that has both shipping and
Billing address on the same page. Should I, perhaps, look into how this
is done with Webassist?

Thanks
> Hi Art,
> Not ignoring you, but will have to have a think about this, as I haven't
> used stored procedures in this manor before. I have some code that is
> coming back to me today, as its been used on a laptop at a tradeshow for
> user registration and clock in, so will grab the sql that I have been
> using and post it here to show you, along with the asp to get the
> returned data.
>
> Steve

Report · May 06, 2008

Art wrote:
> Steve (Dooza),
>
> If you build a cart, how do you go about adding billing and shipping
> information to a cart? Do you have a page for shipping information and
> then have a separate page for billing?
>
> Also, if I remember right, the new version 4 of Webassist cart includes
> a shipping component that creates a template that has both shipping and
> Billing address on the same page. Should I, perhaps, look into how this
> is done with Webassist?

Hi Art,
I have eCart v4, but didn't use any of the shipping stuff, I just had it
all on the same page. Go to www.aclighting.com/shop and make a purchase,
just don't pay at the end, you will see how I have done it using just
the checkout and store details in database behaviours. Your welcome to
email me if you want to talk it through.

I about the previous post, I haven't been given the laptop back with the
database, hopefully it will happen today, then I can show you my Stored
Procedure that may help.

Steve

Report · May 06, 2008

Dooza wrote:

> Hi Art,
> I have eCart v4, but didn't use any of the shipping stuff, I just had it
> all on the same page. Go to www.aclighting.com/shop and make a purchase,

FYI,

I got this error on your checkout page. I think it's because I didn't
fill in the second address box? Not sure. I may have also let the
session time out but I can't tell. Either way, you may want to put an if
statement around that or some other sort of error checking so that it
doesn't throw errors.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value
NULL into column 'uad_usr_id', table 'shop.dbo.tblUserAddresses'; column
does not allow nulls. INSERT fails.

/shop/checkout/changeSAddress.asp, line 88

The way you did the cart interests me though. I like the way the
checkout flows and I'm thinking of making mine more like that. I have a
couple questions. The UseInvoice.asp page. What is on that page? Is it
simply a page with a recordset that pulls in the current user and then
stores it to the database as the shipping address and then sends the
user back to the checkout?

If so, how do you keep the checkout page from throwing up errors if it
doesn't have a shipping address?

Report · May 06, 2008

Art wrote:
> Dooza wrote:
>
>
>> Hi Art,
>> I have eCart v4, but didn't use any of the shipping stuff, I just had
>> it all on the same page. Go to www.aclighting.com/shop and make a
>> purchase,
>
> FYI,
>
> I got this error on your checkout page. I think it's because I didn't
> fill in the second address box? Not sure. I may have also let the
> session time out but I can't tell. Either way, you may want to put an if
> statement around that or some other sort of error checking so that it
> doesn't throw errors.
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value
> NULL into column 'uad_usr_id', table 'shop.dbo.tblUserAddresses'; column
> does not allow nulls. INSERT fails.

Its ok, I deleted your second shipping address after you had first
visited as I saw the duplicate address being inserted, we don't get any
USA customers as we don't generally ship there, and don't have rates for
the USA, so wasn't too worried.

> /shop/checkout/changeSAddress.asp, line 88
>
> The way you did the cart interests me though. I like the way the
> checkout flows and I'm thinking of making mine more like that. I have a
> couple questions. The UseInvoice.asp page. What is on that page? Is it
> simply a page with a recordset that pulls in the current user and then
> stores it to the database as the shipping address and then sends the
> user back to the checkout?
>
> If so, how do you keep the checkout page from throwing up errors if it
> doesn't have a shipping address?

Email me steve.hoare at aclighting.com and I will explain all, its a bit
much for the newsgroup at this point.

Steve

Report · May 08, 2008

Art, I have emailed you the SP and ASP and the page from my shop.

Steve

Report · May 08, 2008

Dooza wrote:
> Art, I have emailed you the SP and ASP and the page from my shop.
>
> Steve

Thanks Steve, I will look at that tonight at home. My works blocks me
from using my own email account...even if it is for work .

Report · May 09, 2008

Stored Procedures and Dreamweaver are officially kicking my @$$.

What gets me is that there are about 50 tasks/techniques that MOST Web
Developers/Designers will need to accomplish with Stored Procedures.

There are plenty of books on SQL and plenty of books on Dreamweaver yet
nothing that really ties them together. It doesn't help that Adobe
Dreamweaver still has the bug that breaks the recordset if you use a
Stored Procedure.

If you know of a good link that demonstrates a work-a-round for the
SP/recordset issue, let me know.

I've searched the web for a tutorial that would walk me through saving
to multiple tables and retrieving error codes and IDs from entered item
but I can't find much.

Also, if you got all this figured out, perhaps you should think about
writing a book.

Report · May 10, 2008

> Also, if you got all this figured out, perhaps you should think about
> writing a book.

Generally using the following syntax in a regular record set works.

exec mystoredproc

Sometimes it gives me fits and I just open gold old 8 and create the
stored procedure there.

--

Report · May 12, 2008

Art wrote:
> Stored Procedures and Dreamweaver are officially kicking my @$$.
>
> What gets me is that there are about 50 tasks/techniques that MOST Web
> Developers/Designers will need to accomplish with Stored Procedures.
>
> There are plenty of books on SQL and plenty of books on Dreamweaver yet
> nothing that really ties them together. It doesn't help that Adobe
> Dreamweaver still has the bug that breaks the recordset if you use a
> Stored Procedure.
>
> If you know of a good link that demonstrates a work-a-round for the
> SP/recordset issue, let me know.

I just don't use the Command approach, thats the only way around it.
Create a recordset, expand the Stored Procedure panel, select it, enter
your first paremeter, save and close, open it again and enter the rest.
The output side is still new to me, and I had to hand code it, which
breaks it in Dreamweaver.

> I've searched the web for a tutorial that would walk me through saving
> to multiple tables and retrieving error codes and IDs from entered item
> but I can't find much.

Yes its a pain, there is very little, but its the way forward, so maybe
your right...

> Also, if you got all this figured out, perhaps you should think about
> writing a book.

Or at least a tutorial or blog post maybe?

I was really hoping it would get addressed in an update, but that
doesn't look likes its going to happen anytime soon. I guess we will
have to pay to have bugs fixed, which is criminal to be honest.

If I get time I will look at writing something for the community. Never
done it before, but its about time I gave something back.