Must declare the scalar variable "@datasetid"

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

That's what I had before. The reason I need to use the parameters is to reduce the risk of a SQL injection attack.  

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

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

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

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

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.

yes, it was SQL Server 2000. Don't worry about the points.

good luck,
yurich