Problem with SP

Hello All!
CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
@.Wager Nvarchar(50))
AS
IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
begin
Update BankRoll
Set Wager = Wager + CAST(@.Wager as Nvarchar)
where userID = @.userID
return -1
end
ELSE
begin
Insert Bankroll (Wager) Values (@.Wager)
RETURN @.@.IDENTITY
end
GO
Having a little problem with the SP above. I'm trying to take a value from
the a "Wage" textbox, and update the value in the table. The userID number
is a value in the userID texbox, and already created in the SQL table. I
need to make sure the the wage value will go in the right row, where the use
r
ID matches. I get an error in SQL, "Server: Msg 515, Level 16, State 2,
Procedure PlbetII, Line 13
Cannot insert the value NULL into column 'UserID', table
'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The 'PlbetII' procedure attempted to return a status of NULL, which is not
allowed. A status of 0 will be returned instead."
I'm not sure why it's inserting the user ID. I see at "Set Wager = Wager +
CAST(@.Wager as Nvarchar)
where userID = @.userID" might be my problem, but I thought it's just
returning the value for user id and selecting it. I'm so .
Any help would be great!!!
TIA!!
RudyThe identity value (or autoincrement) for the column userId is not set,
hence the impossibility of inserting a new record at line 13 without
specifying the value for UserId. When creating a new record, you have two
choice: specify the value for UserId or ask SQL-Server to automatically
create a new value each time by setting the identity property.
From you piece of code, it's impossible to tell which one of these two
methods you should use.
Also, I'm not sure if the line Set Wager = Wager + CAST(@.Wager as
Nvarchar) will do what you are expecting it to do.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:B0D0D405-F131-4106-8602-E5F77D684B4D@.microsoft.com...
> Hello All!
> CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
> @.Wager Nvarchar(50))
> AS
> IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
> begin
> Update BankRoll
> Set Wager = Wager + CAST(@.Wager as Nvarchar)
> where userID = @.userID
> return -1
> end
> ELSE
> begin
> Insert Bankroll (Wager) Values (@.Wager)
> RETURN @.@.IDENTITY
> end
> GO
> Having a little problem with the SP above. I'm trying to take a value
> from
> the a "Wage" textbox, and update the value in the table. The userID
> number
> is a value in the userID texbox, and already created in the SQL table. I
> need to make sure the the wage value will go in the right row, where the
> user
> ID matches. I get an error in SQL, "Server: Msg 515, Level 16, State 2,
> Procedure PlbetII, Line 13
> Cannot insert the value NULL into column 'UserID', table
> 'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> The 'PlbetII' procedure attempted to return a status of NULL, which is not
> allowed. A status of 0 will be returned instead."
> I'm not sure why it's inserting the user ID. I see at "Set Wager = Wager
> +
> CAST(@.Wager as Nvarchar)
> where userID = @.userID" might be my problem, but I thought it's just
> returning the value for user id and selecting it. I'm so .
> Any help would be great!!!
> TIA!!
> Rudy
>
>|||Rudy (Rudy@.discussions.microsoft.com) writes:
> CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
> @.Wager Nvarchar(50))
> AS
> IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
> begin
> Update BankRoll
> Set Wager = Wager + CAST(@.Wager as Nvarchar)
> where userID = @.userID
> return -1
> end
> ELSE
> begin
> Insert Bankroll (Wager) Values (@.Wager)
> RETURN @.@.IDENTITY
> end
> GO
> Having a little problem with the SP above. I'm trying to take a value
> from the a "Wage" textbox, and update the value in the table. The
> userID number is a value in the userID texbox, and already created in
> the SQL table. I need to make sure the the wage value will go in the
> right row, where the user ID matches. I get an error in SQL,
> "Server: Msg 515, Level 16, State 2, Procedure PlbetII, Line 13
> Cannot insert the value NULL into column 'UserID', table
> 'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> The 'PlbetII' procedure attempted to return a status of NULL, which is not
> allowed. A status of 0 will be returned instead."
> I'm not sure why it's inserting the user ID.
Obviously the table does not have the IDENTITY property. If I am to
believe your procedure, the UserID column is nvarchar(50), why IDENTITY
is completely off-track.
Just say:
Insert Bankroll (UserID, Wager) Values (@.UserID, @.Wager)
By the way, the RETURN statement is mainly used to indicate
success/failure, with success being 0 and anything else means an
error. The regular way to return a value is to use an OUTPUT
parameter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you Erland and Sylvain!
I did change things up a little bit per you sugestions. Still having
problems, but I suspect something with the table itself. I deleted the table
,
and built it again, and now it works fine.
Thanks again!
Rudy
"Erland Sommarskog" wrote:

> Rudy (Rudy@.discussions.microsoft.com) writes:
> Obviously the table does not have the IDENTITY property. If I am to
> believe your procedure, the UserID column is nvarchar(50), why IDENTITY
> is completely off-track.
> Just say:
> Insert Bankroll (UserID, Wager) Values (@.UserID, @.Wager)
> By the way, the RETURN statement is mainly used to indicate
> success/failure, with success being 0 and anything else means an
> error. The regular way to return a value is to use an OUTPUT
> parameter.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Can run the following query and post the output.
select COLUMNPROPERTY( object_id('Bankroll') , 'pub_id' , 'AllowsNull' ) as
nullable,
COLUMNPROPERTY( object_id('Bankroll') , 'userID' , 'IsIdentity' ) as
[autogenID]
And a few observations:
Why is UserID nvarchar?
and why do you do this step?
Wager + CAST(@.Wager as Nvarchar)
when @.wager is already nvarchar.. and are you trying to append the string
wager or add the wager to the existing value? Is Wager a numeric value or ha
s
characters like the dollar and pounds.
It will be better to answer your question if you give table definition and
test data
so that we can reproduce the error here.