Must declare a scalar variable

Question

I am getting the exception "Must declare the scalar variable"@strAccountID"

string @straccountid = string.Empty;

sSQL =
"SELECT GUB.BTN,
       GUP.CUST_USERNAME,
       GUP.EMAIL
FROM   GBS_USER_BTN         GUB,
       GBS_USER_PROFILE     GUP
WHERE  GUB.CUST_UID = GUP.CUST_UID
       AND GUB.BTN = '@straccountID'
ORDER BY
       CREATE_DATE DESC"

@straccountid = strAccountID.Substring(0, 10);

Code For running the query against the DB

    try
                {
                    oCn = new SqlConnection(ConfigurationSettings.AppSettings["GBRegistrationConnStr"].ToString());
                    oCn.Open();
                    oCmd = new SqlCommand();      
oCmd.Parameters.AddWithValue("@strAccountID", strAccountID);  

                    oCmd.CommandText = sSQL;
                    oCmd.Connection = oCn;
                    oCmd.CommandType = CommandType.Text;

                    oDR = oCmd.ExecuteReader(CommandBehavior.CloseConnection);

I already declared the variable. Is there any flaw in my query?

Wherever you declared it, SQL doesn't see it - your query should have a `DECLARE @strAccountID varchar(50)` or something similar, somewhere... is it a stored procedure? — Mathieu Guindon, May 09 '13 at 22:17
Might be only a preference, but I find all-caps SQL is hard to read. Isn't `SELECT gub.Btn, gup.cust_UserName, gup.Email FROM gbs_USER_BTN gub, gbs_USER_PROFILE gup WHERE ...` easier on the eye? — Mathieu Guindon, May 09 '13 at 22:23
Could you include the code where you are actually running the SQL statement against the database? — Nicole DesRosiers, May 09 '13 at 22:33
You need to pass that variable in to the SQL Command object as a parameter. — Mark Kram, May 09 '13 at 22:41
@All So when the above code is executed when I see the value of SQL at ocmd.commandtext= sql,should I see the value or not.that is most confusing for me — user1567194, May 09 '13 at 22:52

Mark Kram · Answer 1 · 2013-05-09T23:12:46.403

2

First off the bat get rid of these two lines:

string @straccountid = string.Empty;
@straccountid = strAccountID.Substring(0, 10);

and then try this code:

string strAccountID = "A1234"; //Create the variable and assign a value to it
string AcctID = strAccountID.Substring(0, 10);

oCn = new SqlConnection(ConfigurationSettings.AppSettings["GBRegistrationConnStr"].ToString());
oCn.Open();
oCmd = new SqlCommand();        

oCmd.CommandText = sSQL;
oCmd.Connection = oCn;
oCmd.CommandType = CommandType.Text;
ocmd.Parameters.Add("straccountid", AcctID); //<-- You forgot to add in the parameter
oDR = oCmd.ExecuteReader(CommandBehavior.CloseConnection);

Here is a link on how to create Parametized Query: http://www.dotnetperls.com/sqlparameter

edited May 09 '13 at 23:12

answered May 09 '13 at 22:43

Mark Kram

5,672
7
51
70

I like your answer more than my own, though we point in the same direction. Trying to write these answers from an iPhone is somewhat less than easy :) – Adrian May 09 '13 at 22:50
Thanks kram .I tried and ran the code and saw the output and still the value for that is not set: SELECT GUB.BTN,GUP.CUST_USERNAME,GUP.EMAIL from GBS_USER_BTN GUB,GBS_USER_PROFILE GUP WHERE GUB.CUST_UID = GUP.CUST_UID AND GUB.BTN = @straccountid ORDER BY CREATE_DATE DESC – user1567194 May 09 '13 at 22:50
Adrian you pointed him in the right direction and typing on an iPhone +1 for you! – Mark Kram May 09 '13 at 22:51
I am sure that once we add the value to the command object the sql should pick up the value which is not happening. – user1567194 May 09 '13 at 22:51
@user1567194 - Are you assigning a value to the strAccountID variable? – Mark Kram May 09 '13 at 22:52
yes here is the place where I am doing it : ocmd.Parameters.Add("straccountid", strAccountID.Substring(0, 10)) – user1567194 May 09 '13 at 22:55
No where are you assigning a value to the strAccountID variable before the Parameters.Add? – Mark Kram May 09 '13 at 22:57
is that not the place where I am assiging the value? – user1567194 May 09 '13 at 23:00
@user1567194 That is the place where you are assigning the value in strAccountID to the Command Object's parameter object. Look at my code. – Mark Kram May 09 '13 at 23:02

score 1 · Answer 2 · edited May 23 '17 at 10:32

1

You've declared @straccountid but not as part of the SQL. The SQL server only sees what you send to it. You'd be better off using SQLCommand and parameters to build your select statement safely. This post has examples.

edited May 23 '17 at 10:32

Community

1
1

answered May 09 '13 at 22:39

Adrian

2,825
1
22
32

Thanks for the reply.I used the same parametrized query and add the parameters with the tag "AddWithValue" to the cmd but if I see the statement oCmd.CommandText = sSQL;,When I am trying to check the value of @accuntId still the value is not getting assigned – user1567194 May 09 '13 at 22:43
Can you give an example of what your code looks like now? When you execute the query are you still getting the same error? – Adrian May 09 '13 at 22:46

Must declare a scalar variable

2 Answers2