?
Solved

C# correct syntax in sql string when filtering for a text field from a dropdownlist

Posted on 2012-08-28
3
Medium Priority
?
558 Views
Last Modified: 2012-08-28
I have a gridview and a drop down list. When a selection is made from the list, I want to filter the grid. I need to filter based on the selection from a drop down list. The value selected is a text field. I need the correct syntax for the sql statemtn in the c# code behind. What am I missing?
Here is my sql string. The Comp1 and Comp2 fields are text fields.
 
string strTransactions = "select * FROM vwTransactionsByComp where Comp1 = " + ddlComp.SelectedValue + " OR Comp2 = " + ddlComp.SelectedValue;
0
Comment
Question by:dodgerfan
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 total points
ID: 38344014
You are using string concatenation to build queries. This is bad. I would suggest using parameterized queries. For example:

using (SqlConnection con = new SqlConnection("your connection string"))
{
    using (SqlCommand cmd = new SqlCommand("select * FROM vwTransactionsByComp where Comp1 = @searchVal OR Comp2 = @searchVal", con))
    {
        try
        {
            con.Open();
            cmd.Parameters.Add("@searchVal", ddlComp.SelectedValue);
            SqlDataReader reader = cmd.ExecuteReader();
        }
        catch (SqlException ex)
        {
            // log exception
        }
    }
}

Open in new window


Hopefully you can see that query is now a bit easier to read on top of being a bit safer to actually send to the database.

Note:  I am using a reader in the above, but that is only for example. You would need to tweak that code to fit your environment.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38344027
P.S.

The reason your previous code doesn't work is because you are trying to test textual columns and you didn't quote the incoming values within your query (i.e. wrap the value that's coming from the combo box in single quotes). However, I still recommend you consider using what I described above.
0
 

Author Closing Comment

by:dodgerfan
ID: 38344035
Got it. Thanks for the help and tips.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

807 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