problem in generating the sql query to pass it to query string...

Question

0.00/5 (No votes)

See more:

i am trying to make a login in main page and pass it to a querystring to another page..

i am wrong in generating a query

the logic i am using is as below..

C#

while (dr.Read())
{
   if(proid == String.Empty)
   {
      proid =  dr["ProductID"].ToString(); 
   }
   else
   {
      proid += "," + dr["ProductID"].ToString();
   }
}

and when i cal the query with request.querystring on another page i.e Productcatalog.aspx

C#

string query = "Select * from Products where ProductID in ('" + Request.QueryString["ID"].ToString() +"') AND UnitCost < 5000";

but the query generated is as below

C#

query	"Select * from Products where ProductID= ',63,64,65' AND UnitCost < 5000"	string

i want to generate the query as

C#

Select * from Products where ProductID in (63,64,65) AND UnitCost < 5000

what changes i have to make in above while loop. to generate the similar query.

Posted 28-Dec-11 4:48am

codegeekalpha

Updated 28-Dec-11 7:31am

fjdiewornncalwe

v2

Add a Solution

2 solutions

Solution 2

One way to do the loop is

C#

while (dr.Read())
{
   proid = dr["ProductID"].ToString() + ", ";
}
proid.TrimEnd(' ', ',');

In more elegant (and faster) version you could use StringBuilder.

Posted 28-Dec-11 7:16am

Wendelius

Comments

Espen Harlinn 28-Dec-11 14:03pm

Nice reply :)

Wendelius 28-Dec-11 14:09pm

Thanks :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tim Groven · Accepted Answer · 2011-12-28T04:57:00

Solution 1

Try:

string query = "Select * from Products where ProductID in ('" + Request.QueryString["ID"].ToString().Substring(1) +"') AND UnitCost < 5000";

The problem lies in this statement:

proid += "," + dr["ProductID"].ToString();

You are putting a comma before every value that is returned. The substring will take everything after the first comma.

Posted 28-Dec-11 4:57am

Tim Groven

Updated 28-Dec-11 4:59am

v2

Comments

codegeekalpha 28-Dec-11 11:15am

its not removing the first comma... but its removing its generating query like that Select * from Products where ProductID= ',3,4,5' AND UnitCost < 5000" which is wrong..

Tim Groven 28-Dec-11 12:11pm

Is there any other code executed after the "string query =" line that gets executed before you check the value of query?