Emergency!!! please help with this update.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Emergency!!! please help with this update.

Author

Topic

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-12 : 13:42:39

Could someone point out what could be going wrong in this update? Its not adding the info into the database.


CREATE Procedure V020U18NUC_AGC.AMG_FinalCustomerAdd
    (
@CartID nvarchar(60),
   @bFname nvarchar(50),
@bLname nvarchar(50),
@bAddr1 nvarchar(255),
@bAddr2 nvarchar(255),
@bCity nvarchar(50),
@bState nvarchar(2),
@bZip int,
@bPhone nvarchar(50),
@bEmail nvarchar(100),
   @sFname nvarchar(50),
@sLname nvarchar(50),
@sAddr1 nvarchar(255),
@sAddr2 nvarchar(255),
@sCity nvarchar(50),
@sState nvarchar(2),
@sZip int,
@sPhone nvarchar(50),
@sEmail nvarchar(100),

        @CustomerID int OUTPUT
    )
    AS

Update V020U18NUC_AGC.Customers
    set
bFname =  @bFname,
bLname =@bLname,
bAddr1 = @bAddr1 ,
bAddr2 =@bAddr2,
bCity = @bCity,
bState =@bState ,
bZip =@bZip ,
bPhone =@bPhone ,
bEmail =@bEmail ,
sFname = @sFname,
sLname =@sLname,
sAddr1 =@sAddr1 ,
sAddr2 =@sAddr2,
sCity =@sCity,
sState =@sState ,
sZip =@sZip ,
sPhone =@sPhone ,
sEmail =@sEmail 

Where EmailAddress = @CartID

    SELECT
        @CustomerID = @@Identity

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-12 : 13:44:49

Is EmailAddress an nvarchar column, or is it varchar? That shouldn't cause a problem but you never know; the @CartID variable is nvarchar.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-12 : 13:50:55

quote:

Could someone point out what could be going wrong in this update? Its not adding the info into the database.

I just want to make sure you know you are trying to UPDATE a record that already exists, not ADD a new record.

If you are trying to create a new record, you need to use INSERT INTO.

(You probably know this but just making sure!)

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-12 : 13:53:15

If it is not changing the data then either EmailAddress = @CartID doesn't exist or it will generate an error.

SELECT
@CustomerID = @@Identity

will return null as @@identity is not set for an update.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-12 : 13:54:18

Thanks for pointing the emailadress out. I forgot I have changed to code on the serverside and the where clause needed to change to this,
Where CustomerID = @CartID not Where EmailAddress = @CartID.
Thanks, i just published this site and freeked out with the error!
john

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-12 : 14:30:13

You should still probably get rid of
SELECT @CustomerID = @@Identity
Maybe select @CustomerID = @CartID
or remove the parameter altogether.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-13 : 11:16:25

Thanks will do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources