CODEWITHSUNDEEP
c#sqlparameters
user1567194
user1567194

Reputation: 179

Must declare a scalar variable

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?

Upvotes: 0

Views: 380

Answers (2)

Mark Kram
Mark Kram

Reputation: 5832

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

Upvotes: 2

Adrian
Adrian

Reputation: 2875

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.

Upvotes: 1

Related Questions