ASP.NET and SqlDataAdapter with string value

Question

I have the following:

    SqlConnection con = new SqlConnection(strConn);
    DataSet ds = new DataSet();
    SqlDataAdapter myda = new SqlDataAdapter("SELECT [Key], [Value] FROM [data_LookupValues] where Category = 'CanadaProvinces' ", con);
    myda.Fill(ds);
    ddlState.DataSource = ds;
    ddlState.DataTextField = "Value";
    ddlState.DataValueField = "Key";
    ddlState.DataBind();

I am stuck as I need to put a string value of CanadaProvinces within the SqlDataAdapter.

Is there a easy way of doing this? I get an error when I do it the way I have it.

Does that query run normally in SQL Management studio (or some other query tool). — CodingGorilla
– CodingGorilla, Commented Nov 14, 2012 at 21:33
After your last edit, the query appears as correct. Do you still have the syntax error? — Steve
– Steve, Commented Nov 14, 2012 at 21:38

Tim Schmelter · Accepted Answer · 2012-11-14 21:33:38Z

1

You haven't told us the error, however, this is no valid sql:

SELECT [Key], [Value] FROM [data_LookupValues] = 'CanadaProvinces'

What do you want to query, is data_LookupValues a table in the database?

Perhaps this is what you actually want:

SELECT [Key], [Value] FROM [data_LookupValues] WHERE Provinces = 'CanadaProvinces'

Of course i've just assumed the correct column name.

answered Nov 14, 2012 at 21:33

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2012-11-14 21:54:52Z

The proper way would be to do a number of things:

wrap your disposable objects into using blocks
use a separate SqlCommand object and use a parametrized query (instead of concatenating together your SQL)
since you're only returning a single set of data - use a DataTable instead of the much "heavier" DataSet
separate the fetching of the data into its own method - and just call the from your web page - makes it universally useable, too! (will work for other things - not just CanadaProvinces)

So your code should be something like this:

private DataTable FetchData(string categoryValue)
{
   string query = "SELECT [Key], [Value] FROM [data_LookupValues] where Category = @Category";

   using (SqlConnection con = new SqlConnection(strConn))
   using (SqlCommand cmd = new SqlCommand(query, con))
   {
       cmd.Parameters.Add("@Category", SqlDbType.VarChar, 50).Value = categoryValue;

       SqlDataAdapter myda = new SqlDataAdapter(cmd);
       DataTable result = new DataTable();

       myda.Fill(result);
       return result;
    }
}

and then in your ASPX web's code-behind, use something like this:

DataTable tblCanadaProvinces = FetchData('CanadaProvinces');  

ddlState.DataSource = tblCanadaProvinces;
ddlState.DataTextField = "Value";
ddlState.DataValueField = "Key";
ddlState.DataBind();

and be done with it. Now you can call your FetchData method to retrieve any possible category in your lookup table!

Collectives™ on Stack Overflow

ASP.NET and SqlDataAdapter with string value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related