Getting Value from URL into SQL Query. (ASP.NET & C#)

  • Thread starter Thread starter Harry
  • Start date Start date
H

Harry

Hi,

Could someone tell me where I am going wrong here.

I am filling a datagrid from a SQL database using SqlDataReader.
For the query, I want to use a variable coming from the URL.

Code snippet as follows:
SqlDataAdapter objDataAdapter = new SqlDataAdapter("SELECT CaseCode,
CaseName, @PriceCol, image_thumb FROM dbo.tblCases", myConnection);
objDataAdapter.SelectCommand.Parameters.Add(new
SqlParameter("@PriceCol",SqlDbType.VarChar,50)).Value =
Request.QueryString["Range"];

DataSet dsCases = new DataSet();
objDataAdapter.Fill(dsCases, "CaseName");

dgListCases.DataSource = dsCases.Tables["CaseName"].DefaultView;
dgListCases.DataBind();

myConnection.Close();
*************

When I use this, all I get in the column for "Price" is the actual URL
variable rather than the values from the database.
Can anyone see what I am doing wrong?

Thanks and Thanks and Thanks Again
H
 
If I understand you correctly, the "Price" column's name varies and passed
in in QueryString. If so, see comment below, otherwise, discard this post.

string colName=Request.QueryString["Range"]; //You get the column name
SqlDataAdapter objDataAdapter = new SqlDataAdapter("SELECT CaseCode,
CaseName," + colName + ", image_thumb FROM dbo.tblCases",
myConnection);

You do not need a command parameter here;
 
Thanks Norman,
That worked a treat. As always, it's all so simple when you know how!

I do have one more question!

I also have a datalist in the page.
I can grab values from my dataset using:
<%# DataBinder.Eval(Container.DataItem, "CaseCode") %>

But how do I grab the values for this new variable? (The Changing Price
column!)

For some reason, I know this is going to be very simple, but I just
can't seem to think of the answer at the moment!

Thanks
H
 
There is the risk of a SQL injection attack with this code.
string colName=Request.QueryString["Range"]; //You get the column name
SqlDataAdapter objDataAdapter = new SqlDataAdapter("SELECT CaseCode,
CaseName," + colName + ", image_thumb FROM dbo.tblCases",
myConnection);
Click to expand...

A safer design might be writing a stored proc like the following, and
passing which column you want as an int parameter.

CREATE PROCEDURE Test @Column int
AS

SELECT
ProductID,
SupplierID,
CASE @Column
WHEN 1 THEN Unit_Price
WHEN 2 THEN A_Different_Price
ELSE 0
END
AS DynamicColumn
FROM SomeTable

Of course there are tradeoffs to evaluate. Also, make sure to test the
performance and scalability, queries like these have some potential to
screw up a query plan.

HTH,

--
Scott
http://www.OdeToCode.com

If I understand you correctly, the "Price" column's name varies and passed
in in QueryString. If so, see comment below, otherwise, discard this post.

string colName=Request.QueryString["Range"]; //You get the column name
SqlDataAdapter objDataAdapter = new SqlDataAdapter("SELECT CaseCode,
CaseName," + colName + ", image_thumb FROM dbo.tblCases",
myConnection);

You do not need a command parameter here;

Harry said:
Hi,

Could someone tell me where I am going wrong here.

I am filling a datagrid from a SQL database using SqlDataReader.
For the query, I want to use a variable coming from the URL.

Code snippet as follows:
SqlDataAdapter objDataAdapter = new SqlDataAdapter("SELECT CaseCode,
CaseName, @PriceCol, image_thumb FROM dbo.tblCases", myConnection);
objDataAdapter.SelectCommand.Parameters.Add(new
SqlParameter("@PriceCol",SqlDbType.VarChar,50)).Value =
Request.QueryString["Range"];

DataSet dsCases = new DataSet();
objDataAdapter.Fill(dsCases, "CaseName");

dgListCases.DataSource = dsCases.Tables["CaseName"].DefaultView;
dgListCases.DataBind();

myConnection.Close();
*************

When I use this, all I get in the column for "Price" is the actual URL
variable rather than the values from the database.
Can anyone see what I am doing wrong?

Thanks and Thanks and Thanks Again
H
Click to expand...
Click to expand...
 
Thanks for the info. i did have the SQL security thing in the back of my
mind, but reminders always welcome.

No matter what method I use, how do I use the ColumnName (Price) in the
HTML. (This is the column name that could be different depending on the
url variable!)


Thanking all.
H
 
Couple options I can think of to evaluate:

One way to handle this is to alias the column into a specific name (AS
ThePrice) so the resultset is always the same no matter which column
you use.

Are you using DataBinding? If so you could programmatically set column
names in your code behind before calling DataBind.

Also, if you are pulling a small number of records (I know small is a
relative term), you could consider returning all price columns you
might need and displaying just the one column specified by the
parameter. With a DataGrid you can programmatically turn column
visibility on and off. This approach could be easier to maintain.

HTH,
 
Back
Top