Must declare the scalar variable "@datasetid"


I've tried everything I can think of. I'm getting the message:

Must declare the scalar variable "@datasetid".

In debug, I can see that the the item in the parameters collection with the value in it after the call to cmd.Parameters.Add().

Why does it think that it isn't declared?

Thanks!



public static bool DeleteDataset(int datasetId ,System.Web.UI.WebControls.Label lblErrorMsgs)
{
      const string DATASET_ID_PARM = "@datasetid";
      const string DELETE_DATASET_SQL = "DELETE SimDatasets WHERE DatasetId="+DATASET_ID_PARM;
      lblErrorMsgs.Text="";
      int rowsAffected = 0;
      OleDbConnection cnx = null;
      try
      {
            // define connection
            cnx = new OleDbConnection();
            cnx.ConnectionString = ConfigurationSettings.AppSettings.Get(CONNECTION_STRING);

            // define command
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = DELETE_DATASET_SQL;

            // establish parms
            OleDbParameter prm;
            prm = new OleDbParameter();
            prm.ParameterName = DATASET_ID_PARM;
            prm.OleDbType = OleDbType.Integer;
            prm.Direction = ParameterDirection.Input;
            prm.Value = datasetId;
            cmd.Parameters.Add((prm);

            cnx.Open();
            rowsAffected = cmd.ExecuteNonQuery();
      }
      catch (System.Data.OleDb.OleDbException ex1)
      {
            for (int i =0; i<ex1.Errors.Count; i++)
            {
                  lblErrorMsgs.Text += ex1.Errors[i].Message;
            }
            return false;
      }
      finally
      {
            // clean up
            cnx.Close();
      }
      return true;
}
LVL 3
adgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

athapaCEO / CTOCommented:
Why don't you just change this line

const string DELETE_DATASET_SQL = "DELETE SimDatasets WHERE DatasetId="+DATASET_ID_PARM;

to

const string DELETE_DATASET_SQL = "DELETE SimDatasets WHERE DatasetId="+datasetId.ToString();

and remove
        // establish parms
          OleDbParameter prm;
          prm = new OleDbParameter();
          prm.ParameterName = DATASET_ID_PARM;
          prm.OleDbType = OleDbType.Integer;
          prm.Direction = ParameterDirection.Input;
          prm.Value = datasetId;
          cmd.Parameters.Add((prm);

It will make your function a whole lot smaller too.

AT
adgAuthor Commented:
That's what I had before. The reason I need to use the parameters is to reduce the risk of a SQL injection attack.  
YurichCommented:
1. to reduce the risk of injection attack, you can use datasetId.ToString().Replace( "'", "''" ) - that's one ' is replaces with two ' and '.

2. to try to get your parameterized approach working (as this one is preferable), try to not use your const varaible, and change your sql string to this one:

const string DELETE_DATASET_SQL = "DELETE SimDatasets WHERE DatasetId = @datasetid";

and try to simplify your parameter declaration - you don't want to get a prize for the longest code in the world, do you?

          // establish parms
          OleDbParameter prm = new OleDbParameter( "@datasetid", OleDbType.Integer );
         // you don't have to specify direction Input as it is Input by default
          prm.Value = datasetId;
          cmd.Parameters.Add( prm );

if you were doing copy-pasting, please mark that I you had one extra ( in your Add statement.

Now see if it's working or not.

Good luck,
Yurich
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

adgAuthor Commented:
I really do want to use the parameters.  I've made the changes but am still getting the same error. Here is the updated code - this time copied and pasted directly. Thanks for the help!



            public static bool DeleteDataset(int datasetId ,System.Web.UI.WebControls.Label lblErrorMsgs)
            {
                  lblErrorMsgs.Text="";
                  int rowsAffected = 0;
                  OleDbConnection cnx = null;
                  try
                  {
                        // define connection
                        cnx = new OleDbConnection();
                        cnx.ConnectionString = ConfigurationSettings.AppSettings.Get(CONNECTION_STRING);
                        cnx.Open();

                        // define command
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.Connection = cnx;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "DELETE SimDatasets WHERE DatasetId=@datasetid";

                        // establish parms
                        OleDbParameter prm;
                        prm = new OleDbParameter("@datasetid",OleDbType.Integer);
                        prm.Value = datasetId;
                        cmd.Parameters.Add(prm);

                        rowsAffected = cmd.ExecuteNonQuery();
                  }
                  catch (System.Data.OleDb.OleDbException ex1)
                  {
                        for (int i =0; i<ex1.Errors.Count; i++)
                        {
                              lblErrorMsgs.Text += ex1.Errors[i].Message;
                        }
                        return false;
                  }
                  finally
                  {
                        // clean up
                        cnx.Close();
                  }
                  return true;
            }
YurichCommented:
Try to rebuild your solution, re-start VS, restart your machine - your code is absolutely fine, I just copy-pasted what you listed here into my program, swapped a connection string and table names and everything works just fine...

regs,
yurich
existenz2Commented:
I agree with Yurich. Your code looks fine. Have a look at your SQL Profiler to see what kind of query the SQL Server recieves. Might be a SQL Server issue and not a C#.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
athapaCEO / CTOCommented:
datasetid is an integer, not a string.I don't see any single sql injection threat in that function.
You need to be paraniod and think about security but over security will only degrade your application performance.
Just a thought.

AT
adgAuthor Commented:
>> datasetid is an integer, not a string

Yes, but of course this is just a small example from a much larger application.

>> You need to be paraniod and think about security

I'm just trying to do a good job. I'd like to ensure that I'm worth at least the pittance that I'm paid.  

>> security will only degrade your application

Not as much as having some clown shutdown our database. Now that would really degrade our application!

But I do appreciate your unique point of view.

I got it working. It seems that maybe SQL Server 2005 works differently than SQL Server 2000 in relation to OLE DB. In retrospect I should have mentioned I was using SQL Server 2005 but I didn't realize that that might be a factor. Anyhoo, here is the code that works for me. Thanks for the assistance!

            public static bool DeleteDataset(int datasetId ,System.Web.UI.WebControls.Label lblErrorMsgs)
            {
                  lblErrorMsgs.Text="";
                  int rowsAffected = 0;
                  OleDbConnection cnx = null;
                  try
                  {
                        // define connection
                        cnx = new OleDbConnection();
                        cnx.ConnectionString = ConfigurationSettings.AppSettings.Get(CONNECTION_STRING);

                        // define command
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.Connection = cnx;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "DELETE SimDatasets WHERE DatasetId=?";

                        // establish parms
                        OleDbParameter prm2 = new OleDbParameter("0",OleDbType.Numeric);
                        prm2.Value = 5;
                        cmd.Parameters.Add(prm2);

                        cnx.Open();
                        rowsAffected = cmd.ExecuteNonQuery();
                  }
                  catch (System.Data.OleDb.OleDbException ex1)
                  {
                        for (int i =0; i<ex1.Errors.Count; i++)
                        {
                              lblErrorMsgs.Text += ex1.Errors[i].Message;
                        }
                        return false;
                  }
                  finally
                  {
                        // clean up
                        cnx.Close();
                  }
                  return true;
            }


adgAuthor Commented:
100 to Yurich for actually running and verifing the code. Yurich, I assume your using SQL Server 2000, yes?

100 to existenz2 for suggesting the profiler which was very helpful and pointing out that it mihght be a SQL Server issue.

Sorry I can't give more - I'm out of points.
YurichCommented:
yes, it was SQL Server 2000. Don't worry about the points.

good luck,
yurich
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.