[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12503
  • Last Modified:

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;
}
0
adg
Asked:
adg
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
athapaCommented:
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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
 
athapaCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now