Query question

Hi,

I am trying to run a query in code behind, because the query is simple, I do not want to create a sp just for that. Here is my code. It runs well without a parameter.


        SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString());

        SqlCommand cmd = new SqlCommand("SELECT CategoryName from Category ORDER BY CategoryName="  + Session["sCMID"], con);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable table = new DataTable();
        da.Fill(table);

        ContactName.DataSource = table;
        ContactName.DataValueField = "CategoryName";
        ContactName.DataTextField = "CategoryName";
        ContactName.DataBind();


        con.Close();

Open in new window


The result will be loaded into a dropdown list.  thAnks
mcrmgAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
From a database perspective, that isn't a valid query.  I'm surprised the first one runs.  There is no = in an ORDER BY.

I would think you need something like this:

SqlCommand cmd = new SqlCommand("SELECT CategoryName from Category WHERE CategoryName = "  + Session["sCMID"] + "ORDER BY CategoryName", con);

Although, I would think that CATEGORYNAME isn't the column you are filtering on, you would need to put the correct name in the WHERE.
0
 
Dustin SaundersConnect With a Mentor Director of OperationsCommented:
You should make that a method so you can reuse it.

        public static DataTable GetSelectResults(string sqlQuery, string cs)
        {
            DataTable dT = new DataTable();
            
            using (SqlConnection _cs = new SqlConnection(cs))
            {
                _cs.Open();
                SqlCommand cmd = new SqlCommand(sqlQuery, _cs);

                SqlDataAdapter dA = new SqlDataAdapter(cmd);
                dA.Fill(dT);
            }
            return dT;
        }

Open in new window


You really should paramaterize your queries to avoid issues like an injection attack.  You can also put your combo box (or whatever control) code into a method to reuse as well.

public static FillCombo(DataTable table, ComboBox comboBox, string displayColumn, string valueColumn)
{
     comboBox.DataSource = table;
     comboBox.DisplayMember = displayColumn;
     comboBox.ValueMember = valueColumn;
}

Open in new window



If you're going to run static queries from code, you might also consider creating a separate class to put those in one place to make it easier to edit/modify/add.

public static class Queries
{
     public static string GetThisQuery()
     {
          return @"SELECT * FROM Table WHERE Column = Value";
     }
}

Open in new window


And get the queries as
Queries.GetThisQuery()

Open in new window

0
 
käµfm³d 👽Commented:
What is the question here?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
mcrmgAuthor Commented:
I need help with passing the parameter. thanks
0
 
mcrmgAuthor Commented:
The code works fine

SqlCommand cmd = new SqlCommand("SELECT CategoryName from Category ORDER BY CategoryName=", con);

But when I try to add parameter, it does not work

SqlCommand cmd = new SqlCommand("SELECT CategoryName from Category ORDER BY CategoryName="  + Session["sCMID"], con);

thanks
0
 
Dustin SaundersConnect With a Mentor Director of OperationsCommented:
Well, you have some issues.

First off, if you're using VARCHAR and not using parameter, you should include the single quotes.
"SELECT * FROM Table WHERE StringValue = '" + variable + "'";

Open in new window


Secondly,
SELECT CategoryName FROM Category ORDER BY CategoryName = @variable 

Open in new window

is not proper syntax.  Are you trying to do a WHERE clause?

Thirdly, that isn't a parameterized query.  You would need to do a query like:
"SELECT CategoryName FROM Category WHERE CategoryName = @categoryName"

Open in new window

And then parameterize it.  But you should also add in to your SQL method passing a list of parameters and then inserting them to the SqlCommand.
0
 
mcrmgAuthor Commented:
I do not know what I was thinking...  Where is the "WHERE clause"...............
0
 
mcrmgAuthor Commented:
thanks
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.