Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

parameterized query, mssql and c#

Posted on 2013-01-25
2
534 Views
Last Modified: 2013-01-31
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.
0
Comment
Question by:ccMcBride
2 Comments
 
LVL 3

Accepted Solution

by:
contactnaeem earned 200 total points
ID: 38821698
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
 

Author Closing Comment

by:ccMcBride
ID: 38840757
thank you.  means that all parameters will have to be named in a consistent manner, but if that's what it takes..
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question