By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,637 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,637 IT Pros & Developers. It's quick & easy.

where clause parameter..

P: n/a
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.CommandTex t);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:
Nov 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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" <br**************@yahoo.com> wrote in message news:u5***************@TK2MSFTNGP15.phx.gbl...
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.CommandTex t);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:
Nov 19 '05 #2

P: n/a
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:uO****************@TK2MSFTNGP09.phx.gbl...
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" <br**************@yahoo.com> wrote in message news:u5***************@TK2MSFTNGP15.phx.gbl...
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.CommandTex t);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:
Nov 19 '05 #3

P: n/a
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" <br**************@yahoo.com> wrote in message
news:eh*************@TK2MSFTNGP12.phx.gbl...
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:uO****************@TK2MSFTNGP09.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:u5***************@TK2MSFTNGP15.phx.gbl...
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.CommandTex t);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:
Nov 19 '05 #4

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

"S. Justin Gengo" <sjgengo@[no_spam_please]aboutfortunate.com> wrote in
message news:O$**************@TK2MSFTNGP14.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:eh*************@TK2MSFTNGP12.phx.gbl...
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:uO****************@TK2MSFTNGP09.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:u5***************@TK2MSFTNGP15.phx.gbl...
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.CommandTex t);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:

Nov 19 '05 #5

P: n/a
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" <sjgengo@[no_spam_please]aboutfortunate.com> wrote in message news:O$**************@TK2MSFTNGP14.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:eh*************@TK2MSFTNGP12.phx.gbl...
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:uO****************@TK2MSFTNGP09.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:u5***************@TK2MSFTNGP15.phx.gbl...
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.CommandTex t);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:

Nov 19 '05 #6

P: n/a
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" <br**************@yahoo.com> wrote in message
news:uC**************@tk2msftngp13.phx.gbl...
thanks..
do you know what are differences between sqldataadapter and
oledbsqldataadapter?
how do I know when to use which one?

"S. Justin Gengo" <sjgengo@[no_spam_please]aboutfortunate.com> wrote in
message news:O$**************@TK2MSFTNGP14.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:eh*************@TK2MSFTNGP12.phx.gbl...
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:uO****************@TK2MSFTNGP09.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:u5***************@TK2MSFTNGP15.phx.gbl...
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.CommandTex t);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:


Nov 19 '05 #7

P: n/a
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
"Britney" <br**************@yahoo.com> wrote in message news:Oz**************@TK2MSFTNGP15.phx.gbl...
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" <sjgengo@[no_spam_please]aboutfortunate.com> wrote in message news:O$**************@TK2MSFTNGP14.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:eh*************@TK2MSFTNGP12.phx.gbl...
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:uO****************@TK2MSFTNGP09.phx.gbl...
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" <br**************@yahoo.com> wrote in message
news:u5***************@TK2MSFTNGP15.phx.gbl...
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.CommandTex t);
Line 86:
Line 87: oleDbDataAdapter1.Fill(usersDs1);
Line 88: DataGrid1.DataBind();
Line 89:

Nov 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.