473,287 Members | 1,663 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

where clause parameter..

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
7 3019
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Eric | last post by:
Hi : From a crystal report i get a list of employee firstnames as a string into my store procedure. Why is it comming this way ? hmmmmmm it's a question for me too. ex: "e1,e2,e3" here are my...
3
by: Ryan | last post by:
I am trying to create a stored procedure whose where clause is dependent on a parameter. If the parameter @myparam is null or '' then I want the where clause to be one thing, else I want it to...
3
by: MikeY | last post by:
Hi everyone, I'm having problems with my WHERE Clause syntax with in my SQL CommandText. The error that it is display is "You Have No Data". My problem lies with in the WHERE clause not finding...
7
by: Swinky | last post by:
Mr. Browne's copy code on his web site has saved me. I have been struggling to copy a record with several related sub-form tables. I found code on his web site that copies a sub-form table,...
1
by: not_a_commie | last post by:
I was hoping for increased functionality with the where clause in C# 3.0. Using the new keyword 'var' would really allow us to take nice advantage of these. Specifically: 1. I want to limit it...
3
by: teddymeu | last post by:
Hi Guys, new to development using visual studio and vb.net 2.0. and SQL express. Have a small problem. I have an update command which im using to change the values of a blob image table to NULL...
8
by: SambhajiB | last post by:
Hi, I am using Access 2003 and .Net 2005. I have one problem in access query. I am trying to send the condtion of where clause through parameter to Access query as follows... PARAMETERS ...
4
benchpolo
by: benchpolo | last post by:
I wrote a SQL script with a rxfilldate (mm/dd/yyyy format) parameter in WHERE clause. Is there way to automate the where clause to pick up the previous months data. For example: DATEPAID BETWEEN...
2
by: =?Utf-8?B?Um95?= | last post by:
I want to dynamically construct a TSQL with a datetime column in the WHERE clause. How do I include the millisecond in the query? "WHERE datetimecolumn = " + dt.ToString() will not include the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.