Re: where clause parameter..

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Britney,

You can't use parameters that way.

What you should do is add your question marks and parameters dynamically to
the entire statement. Something like this:

System.Text.StringBuilder SQLStatement;

SQLStatement.Append("SELECT TOP 100 user_id FROM dbo.users WHERE");

if ([Some Contingency]) then
{
SQLStatement.Append(" has_picture=?") //note the space after the first
quote.
this.oleDbSelectCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("has_picture",
System.Data.OleDb.OleDbType.Boolean, 1, "has_picture"));
}

this.oleDbSelectCommand1.CommandText = SQLStatement.ToString

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche


"Britney" <britneychen_2001@xxxxxxxxx> wrote in message
news:ehz1$SHuFHA.596@xxxxxxxxxxxxxxxxxxxxxxx
No.. you don't get my question. I don't want this format. I'm doing an
advance search, so my where conditions are dynamic.


for a simple example: WhereClause = "has_picture=1 AND sex='female' "

this.oleDbSelectCommand1.CommandText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM dbo.users WHERE " + WhereClause + " ORDER BY age "

however I got error when doing filling.




"S. Justin Gengo" <sjgengo@[no_spam_please]aboutfortunate.com> wrote in
message news:uO3d1M%23tFHA.2064@xxxxxxxxxxxxxxxxxxxxxxx
Britney,

Try putting the single apostraphes around the sex parameter so it will look
like:

this.oleDbSelectCommand1.CommandText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM dbo.users WHERE (has_picture = ?) AND (sex = '?') ORDER BY
age "

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"Britney" <britneychen_2001@xxxxxxxxx> wrote in message
news:u5YloH%23tFHA.464@xxxxxxxxxxxxxxxxxxxxxxx
Original code:
this.oleDbSelectCommand1.CommandText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM dbo.users WHERE (has_picture = ?) AND (sex = ?) ORDER BY age
"

this.oleDbSelectCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("has_picture",
System.Data.OleDb.OleDbType.Boolean, 1, "has_picture"));
this.oleDbSelectCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("sex", System.Data.OleDb.OleDbType.VarChar,
10, "sex"));



but I want to modify it so (has_picture = ?) AND (sex = ?) become a
parameter.

Now the code look like:
--for example:
string WhereClause = " has_picture=1 and sex='female' ";
this.oleDbSelectCommand1.CommandText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM Jiaoyou.dbo.users WHERE " + WhereClause + " Order by age";
//this.oleDbSelectCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("has_picture",
System.Data.OleDb.OleDbType.Boolean, 1, "has_picture"));
//this.oleDbSelectCommand1.Parameters.Add(new
System.Data.OleDb.OleDbParameter("sex", System.Data.OleDb.OleDbType.VarChar,
10, "sex"));

But somehow I got errors when go to URL. what is the problem?
Incorrect syntax near the keyword 'ORDER'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Incorrect syntax near
the keyword 'ORDER'.

Source Error:

Line 85: Response.Write(this.oleDbSelectCommand1.CommandText);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:


.