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?
 
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#.
0
 
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
0
 
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.  
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
0
 
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;
            }
0
 
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
0
 
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
0
 
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;
            }


0
 
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.
0
 
YurichCommented:
yes, it was SQL Server 2000. Don't worry about the points.

good luck,
yurich
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.