Connie McBride
asked on
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:
but it keeps blowing up, because I can't seem to get it quite right
it is called as:
List<object> parm = new List<object>();
parm.add(aName);
dbRoutines.getDataSet("select * from sales where company = ?", parm);
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;
}
so now I'm stuck, because parameterized queries are def. needed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER