parameterized query, mssql and c#

I am trying to work out my stored procedure to build and open a dynamic parameterized query.

it is called as:
List<object> parm = new List<object>();
parm.add(aName);
dbRoutines.getDataSet("select * from sales where company = ?", parm);

Open in new window


but it keeps blowing up, because I can't seem to get it quite right
   public static DataSet GetDataSet(string mySQL, List<Object> parms)
   {
	  SqlDataAdapter sqlDa = new SqlDataAdapter();
	  DataSet sqlDs = new DataSet();
	  using (SqlConnection sqlConn =

	  new SqlConnection("Context Connection=True"))
	  {
	     SqlCommand sqlCmd = new SqlCommand("SET NOCOUNT ON; "  + mySQL, sqlConn);
		  sqlCmd.CommandType = CommandType.Text;         
        sqlDa.SelectCommand = sqlCmd;
        for (int x = 0; x <= parms.Count - 1; x++)
        {
           sqlCmd.Parameters.AddWithValue(x.ToString(), parms[x]); --- this one results in 'incorrect syntax near ?'
           //sqlCmd.Parameters[x].Value = parms[x];//this one results in 'Invalid index 0 for this SqlParameterCollection with Count=0'.
         //sqlCmd.Parameters.Add(new SqlParameter("@" + x.ToString(), parms[x]))  --- this one also results in 'incorrect syntax near ?'
       
        }

        try
        {
		     sqlDa.Fill(sqlDs, "TABLE");
        }

        catch(Exception Ex)
        {
           SqlContext.Pipe.Send("Error retrieving record " + Ex.GetType().ToString() + " " + Ex.Message);
           SqlContext.Pipe.Send(mySQL);
         }
         finally
         {
			   sqlConn.Close();
         }
	  }
     if ((sqlDs.Tables == null) ||
         (sqlDs.Tables["Table"] == null) ||
         (sqlDs.Tables.Count == 0))
        return null;
     else
	     return sqlDs;
   }

Open in new window

so now I'm stuck, because parameterized queries are def. needed.
Connie McBrideJust a simple programmerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
contactnaeemConnect With a Mentor Commented:
change ? with @param1
List<object> parm = new List<object>();
parm.add(aName);
dbRoutines.getDataSet("select * from sales where company = @param1", parm);


public static DataSet GetDataSet(string mySQL, List<Object> parms)
   {
        SqlDataAdapter sqlDa = new SqlDataAdapter();
        DataSet sqlDs = new DataSet();
        using (SqlConnection sqlConn =

        new SqlConnection("Context Connection=True"))
        {
           SqlCommand sqlCmd = new SqlCommand("SET NOCOUNT ON; "  + mySQL, sqlConn);
              sqlCmd.CommandType = CommandType.Text;        
        sqlDa.SelectCommand = sqlCmd;
        for (int x = 0; x <= parms.Count - 1; x++)
        {
command.Parameters.AddWithValue("@demographics", demoXml);
           sqlCmd.Parameters.AddWithValue("@param"+x.ToString(), parms[x]);      
        }

        try
        {
                 sqlDa.Fill(sqlDs, "TABLE");
        }

        catch(Exception Ex)
        {
           SqlContext.Pipe.Send("Error retrieving record " + Ex.GetType().ToString() + " " + Ex.Message);
           SqlContext.Pipe.Send(mySQL);
         }
         finally
         {
                     sqlConn.Close();
         }
        }
     if ((sqlDs.Tables == null) ||
         (sqlDs.Tables["Table"] == null) ||
         (sqlDs.Tables.Count == 0))
        return null;
     else
           return sqlDs;
   }
0
 
Connie McBrideJust a simple programmerAuthor Commented:
thank you.  means that all parameters will have to be named in a consistent manner, but if that's what it takes..
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.