where clause parameter..

B

Britney

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:
 
S

S. Justin Gengo

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
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:
 
B

Britney

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.




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
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:
 
S

S. Justin Gengo

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


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.




message 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
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:
 
B

Britney

thanks..
do you know what are differences between sqldataadapter and
oledbsqldataadapter?
how do I know when to use which one?



S. Justin Gengo said:
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


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.




message 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
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:
Click to expand...
 
B

Britney

Justin,
just another question, In order to set oleDbSelectCommand1.CommandText to some string,
the string must have at least one parameter (?) in it to work? in the code I had showed you before, WhereClause is actually a string, not a question mark (?)

I already initialized c# variable WhereClause = "1=1 "

so actually my querystring doesn't really contain any OleDbParameter

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 "

but my question is why it failed. and why can't I do it this way?





S. Justin Gengo said:
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


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.




message 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
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:
Click to expand...
 
S

S. Justin Gengo

Various data adapters work with different types of databases. It really
depends on which kind of database you are connecting to. Many of the
dataadapters will work with multiple databases. I use the SqlDataAdapter
whenever connecting to SQL Server 2000 and the OleDbDataAdapter when
connecting to our older AS400 system. I also use the OleDbDataAdapter when I
have to connect to an Access database.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
Britney said:
thanks..
do you know what are differences between sqldataadapter and
oledbsqldataadapter?
how do I know when to use which one?



S. Justin Gengo said:
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


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.




message 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
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:
Click to expand...
Click to expand...
 
S

S. Justin Gengo

Britney,

You can do it that way, you probably just had a typo in your where clause (missing a space or something like that). Try doing a response.Write of your entire CommandText after the where clause has been added to see if the SQL looks proper to you.

I would suggest that you always use parameters whenever you are putting user input into a SQL command. If you don't you are leaving your site open to SQL Injection attacks. Best case scenario a user could get/change your data. Worst case scenario a user could destroy your entire database. The parameters are there for a reason.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
Justin,
just another question, In order to set oleDbSelectCommand1.CommandText to some string,
the string must have at least one parameter (?) in it to work? in the code I had showed you before, WhereClause is actually a string, not a question mark (?)

I already initialized c# variable WhereClause = "1=1 "

so actually my querystring doesn't really contain any OleDbParameter

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 "

but my question is why it failed. and why can't I do it this way?





S. Justin Gengo said:
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


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.




message 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
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:
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

userDs1.EnforceConstraints problem 0
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in sy 0
Another DataGrid Sorting problem 3
hash array loop in sequence? 9
Escaping optional parameter in WHERE clause 3
parameter query in MDB 1
Output Parameter? 1
dynamically created gridview sorting/paging 1

Members online

Total: 68 (members: 2, guests: 66)
Robots: 485

Forum statistics

Threads
473,995
Messages
2,570,228
Members
46,816
Latest member
nipsseyhussle

Latest Threads

Top