473,394 Members | 1,256 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

"Must declare the scalar variable @Nickname"...

Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.
Nov 18 '05 #1
5 15783
You might have better luck with this question in the sqlserver groups (microsoft.public.sqlserver.programming). In the meantime, why not let Transact-SQL do some of the work for you

IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname) BEGIN
-- the user is there
END
ELSE BEGIN
-- the user is not there
END

Scott
"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl...
Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.
Nov 18 '05 #2
dynamic sql runs in its own context (think of it as a sub call), so no
variables declared in the caller are valid in the dynamic statement. i have
no idea why you used dynamic sql or declared @checkforexistingrecord as a
varchar.

try:

declare @checkForExistingRecord int
if exists (select * from users where nickname = @usernick)
set @checkForExistingRecord = 1
else
set @checkForExistingRecord = 0
-- bruce (sqlwork.com)
"Jiggaz" <an*******@discussions.microsoft.com> wrote in message
news:2a*****************************@phx.gbl...
Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.

Nov 18 '05 #3
i have done that :
________________
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname)
BEGIN
-- the user is there
RETURN -1
END
ELSE BEGIN
insert into users values (@Nickname , @Password ,
@Email , @Date, @Name, @Lastname, @Sexe,
@Titre, @Adress, @Birthday, @Phonetel, @Mobilephone,
@Website, 50, '', '', '', '', @MailsAllowed)
RETURN 1
END
___________________

But, always return me -1 but, it's good: if user is
already in the table, sql doesn't add him and if not
exists, add! But always return me in msg box -1?
How to get if it hsn't been added to show a message : good
or error if already in base?

Thanks. Regards.

-----Original Message-----
You might have better luck with this question in the sqlserver groups (microsoft.public.sqlserver.programming).
In the meantime, why not let Transact-SQL do some of the
work for you
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname) BEGIN -- the user is there
END
ELSE BEGIN
-- the user is not there
END

Scott
"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl... Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.

Nov 18 '05 #4

I'm not exactly sure what you are asking; are you saying that your sproc is always returning -1? If you think the SELECT * FROM users... should be "failing" try to return a count or debug the sproc. You could also run put some SQL PRINT statements in you sproc and run it from QueryAnalyzer if you need to troubleshoot.

Scott

"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl...
i have done that :
________________
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname)
BEGIN
-- the user is there
RETURN -1
END
ELSE BEGIN
insert into users values (@Nickname , @Password ,
@Email , @Date, @Name, @Lastname, @Sexe,
@Titre, @Adress, @Birthday, @Phonetel, @Mobilephone,
@Website, 50, '', '', '', '', @MailsAllowed)
RETURN 1
END
___________________

But, always return me -1 but, it's good: if user is
already in the table, sql doesn't add him and if not
exists, add! But always return me in msg box -1?
How to get if it hsn't been added to show a message : good
or error if already in base?

Thanks. Regards.

-----Original Message-----
You might have better luck with this question in the sqlserver groups (microsoft.public.sqlserver.programming).
In the meantime, why not let Transact-SQL do some of the
work for you
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname) BEGIN -- the user is there
END
ELSE BEGIN
-- the user is not there
END

Scott
"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl... Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.

Nov 18 '05 #5
No, what's failing is the stored procedure : it doesn't
return me 1 when it adds the user. Always return me "-1"...

Why? If there is an user it has to return me -1 but when it
adds must not..

How can i return a value depending on the result of INSERT
which could be get, after, by asp.net and show a message to
the user if his account has or hasn't been created?

Thanks.

-----Original Message-----

I'm not exactly sure what you are asking; are you saying that your sproc is always returning -1? If you think the
SELECT * FROM users... should be "failing" try to return a
count or debug the sproc. You could also run put some SQL
PRINT statements in you sproc and run it from QueryAnalyzer
if you need to troubleshoot.
Scott

"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl... i have done that :
________________
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname)
BEGIN
-- the user is there
RETURN -1
END
ELSE BEGIN
insert into users values (@Nickname , @Password ,
@Email , @Date, @Name, @Lastname, @Sexe,
@Titre, @Adress, @Birthday, @Phonetel, @Mobilephone,
@Website, 50, '', '', '', '', @MailsAllowed)
RETURN 1
END
___________________

But, always return me -1 but, it's good: if user is
already in the table, sql doesn't add him and if not
exists, add! But always return me in msg box -1?
How to get if it hsn't been added to show a message : good
or error if already in base?

Thanks. Regards.

>-----Original Message-----
>You might have better luck with this question in the

sqlserver groups (microsoft.public.sqlserver.programming).
In the meantime, why not let Transact-SQL do some of the
work for you
>
>IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname)

BEGIN
> -- the user is there
>END
>ELSE BEGIN
> -- the user is not there
>END
>
>Scott
> "Jiggaz" <an*******@discussions.microsoft.com> wrote in

message news:2a*****************************@phx.gbl...
> Hi,
>
> Look my stored procedure :
> __________________
> ALTER PROCEDURE dbo.CreateAccount
>
> @Nickname varchar(30),
> @Password varchar(15),
> @Email varchar(50),
> @Date datetime,
> @Name varchar(50),
> @Lastname varchar(50),
> @Sexe varchar(2),
> @Titre varchar(15),
> @Adress varchar(255),
> @Birthday varchar(50),
> @Phonetel varchar(50),
> @Mobilephone varchar(50),
> @Website varchar(255),
> @MailsAllowed varchar(2)
>
> AS
>
> DECLARE @insertstring nchar(4000)
> DECLARE @checkforexistingrecord VARCHAR(60)
> DECLARE @add VARCHAR(2000)
> DECLARE @usernick VARCHAR(30)
>
> SET @usernick = @Nickname
>
> SET @insertstring = 'DECLARE @checkforexistingrecord
> VARCHAR(60)
> SELECT nickname FROM users WHERE nickname = '''
> + @usernick + ''''
> EXEC(@insertstring)
> SELECT @checkforexistingrecord = @@ROWCOUNT
> __________________
>
> Why i get this error?
> I just want to verify if the username already exists..
>
> Thanks. Regards.

Nov 18 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ken Adeniji | last post by:
Must declare the scalar variable '@FirstName' ContactGridViewWebForm.aspx <aspqlDataSource RunAt="server" ID="SqlDataSourceContact" ...
2
by: Oded Dror | last post by:
Hi there, Please take a look at the source code and tell me whats wrong? Imports System.Data Imports System.Data.SqlClient Partial Class Test Inherits System.Web.UI.Page Const conString As...
1
by: AndrewD | last post by:
when the following SELECT is executed via A cursor to SQL 2005 it works SELECT * FROM LCTRY WHERE LANGUAGE = 'EN' AND CTRY_CODE = :CTRY BUT SELECT * FROM LCTRY WHERE CTRY_CODE = :CTRY...
1
by: Rama Jayapal | last post by:
the following is my code can anybody rectify my problem that i get when running my application "Must declare scalar variable @ID" <asp:GridView ID="GridView1" DataKeyNames="ID" runat="server"...
0
by: roamnet | last post by:
hi i created database file with .mdf extention ,sql server as a source and use grid view to display data there're no problem in data retrieve and display,but i want to edit it or insert new...
2
by: saravana784 | last post by:
USE GO /****** Object: StoredProcedure . Script Date: 06/09/2007 20:15:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
0
by: =?Utf-8?B?V0pC?= | last post by:
I'm trying to understand the full interaction between controls on a formview that are bound to a SQLDataSource and the binding of the data using ASP.Net 2.0. When I set ConflictDetection to...
1
by: Mark | last post by:
I am really struggling with this, trying to understand why it doesn't work. I've simplified the code down to the essentials. The GridView displays in ReadOnly mode just fine, Clicking the "edit"...
13
by: Raman Pahwa | last post by:
Hi Suppose, i have a scalar variable named "@numberlist". How should i declare it in ASP coding
1
by: Tony M | last post by:
vs 2005 - vb .net - web forms - xp pro Can't figure out why I keep getting the error Must declare the scalar variable "@CheckUser". The error happens on SqlDA.Fill(SqlDS). Just trying to make...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.