473,549 Members | 2,560 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

where clause parameter..

Original code:
this.oleDbSelec tCommand1.Comma ndText = "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.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("has_picture ", System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar, 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.oleDbSelec tCommand1.Comma ndText = "SELECT TOP 100 user_id, password, nick_name, sex, age, has_picture, city, state, " +

"country FROM Jiaoyou.dbo.use rs WHERE " + WhereClause + " Order by age";

//this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("has_picture ", System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
//this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar, 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.Ole Db.OleDbExcepti on: Incorrect syntax near the keyword 'ORDER'.

Source Error:

Line 85: Response.Write( this.oleDbSelec tCommand1.Comma ndText);
Line 86:
Line 87: oleDbDataAdapte r1.Fill(usersDs 1);
Line 88: DataGrid1.DataB ind();
Line 89:
Nov 19 '05 #1
7 3034
Britney,

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

this.oleDbSelec tCommand1.Comma ndText = "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******** *******@TK2MSFT NGP15.phx.gbl.. .
Original code:
this.oleDbSelec tCommand1.Comma ndText = "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.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("has_picture ", System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar, 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.oleDbSelec tCommand1.Comma ndText = "SELECT TOP 100 user_id, password, nick_name, sex, age, has_picture, city, state, " +

"country FROM Jiaoyou.dbo.use rs WHERE " + WhereClause + " Order by age";

//this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("has_picture ", System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
//this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar, 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.Ole Db.OleDbExcepti on: Incorrect syntax near the keyword 'ORDER'.

Source Error:

Line 85: Response.Write( this.oleDbSelec tCommand1.Comma ndText);
Line 86:
Line 87: oleDbDataAdapte r1.Fill(usersDs 1);
Line 88: DataGrid1.DataB ind();
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_pictur e=1 AND sex='female' "

this.oleDbSelec tCommand1.Comma ndText = "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******** ********@TK2MSF TNGP09.phx.gbl. ..
Britney,

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

this.oleDbSelec tCommand1.Comma ndText = "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******** *******@TK2MSFT NGP15.phx.gbl.. .
Original code:
this.oleDbSelec tCommand1.Comma ndText = "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.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("has_picture ", System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar, 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.oleDbSelec tCommand1.Comma ndText = "SELECT TOP 100 user_id, password, nick_name, sex, age, has_picture, city, state, " +

"country FROM Jiaoyou.dbo.use rs WHERE " + WhereClause + " Order by age";

//this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("has_picture ", System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
//this.oleDbSelec tCommand1.Param eters.Add(new System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar, 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.Ole Db.OleDbExcepti on: Incorrect syntax near the keyword 'ORDER'.

Source Error:

Line 85: Response.Write( this.oleDbSelec tCommand1.Comma ndText);
Line 86:
Line 87: oleDbDataAdapte r1.Fill(usersDs 1);
Line 88: DataGrid1.DataB ind();
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.Str ingBuilder SQLStatement;

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

if ([Some Contingency]) then
{
SQLStatement.Ap pend(" has_picture=?") //note the space after the first
quote.
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
}

this.oleDbSelec tCommand1.Comma ndText = SQLStatement.To String

--
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******** *****@TK2MSFTNG P12.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_pictur e=1 AND sex='female' "

this.oleDbSelec tCommand1.Comma ndText = "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******** ********@TK2MSF TNGP09.phx.gbl. ..
Britney,

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

this.oleDbSelec tCommand1.Comma ndText = "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******** *******@TK2MSFT NGP15.phx.gbl.. .
Original code:
this.oleDbSelec tCommand1.Comma ndText = "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.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar,
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.oleDbSelec tCommand1.Comma ndText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM Jiaoyou.dbo.use rs WHERE " + WhereClause + " Order by age";
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar,
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.Ole Db.OleDbExcepti on: Incorrect syntax near
the keyword 'ORDER'.

Source Error:

Line 85: Response.Write( this.oleDbSelec tCommand1.Comma ndText);
Line 86:
Line 87: oleDbDataAdapte r1.Fill(usersDs 1);
Line 88: DataGrid1.DataB ind();
Line 89:
Nov 19 '05 #4
thanks..
do you know what are differences between sqldataadapter and
oledbsqldataada pter?
how do I know when to use which one?

"S. Justin Gengo" <sjgengo@[no_spam_please]aboutfortunate. com> wrote in
message news:O$******** ******@TK2MSFTN GP14.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.Str ingBuilder SQLStatement;

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

if ([Some Contingency]) then
{
SQLStatement.Ap pend(" has_picture=?") //note the space after the first
quote.
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
}

this.oleDbSelec tCommand1.Comma ndText = SQLStatement.To String

--
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******** *****@TK2MSFTNG P12.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_pictur e=1 AND sex='female' "

this.oleDbSelec tCommand1.Comma ndText = "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******** ********@TK2MSF TNGP09.phx.gbl. ..
Britney,

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

this.oleDbSelec tCommand1.Comma ndText = "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******** *******@TK2MSFT NGP15.phx.gbl.. .
Original code:
this.oleDbSelec tCommand1.Comma ndText = "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.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex",
System.Data.Ole Db.OleDbType.Va rChar, 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.oleDbSelec tCommand1.Comma ndText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM Jiaoyou.dbo.use rs WHERE " + WhereClause + " Order by age";
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex",
System.Data.Ole Db.OleDbType.Va rChar, 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.Ole Db.OleDbExcepti on: Incorrect syntax near
the keyword 'ORDER'.

Source Error:

Line 85: Response.Write( this.oleDbSelec tCommand1.Comma ndText);
Line 86:
Line 87: oleDbDataAdapte r1.Fill(usersDs 1);
Line 88: DataGrid1.DataB ind();
Line 89:

Nov 19 '05 #5
Justin,
just another question, In order to set oleDbSelectComm and1.CommandTex t 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.oleDbSelec tCommand1.Comma ndText = "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$******** ******@TK2MSFTN GP14.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.Str ingBuilder SQLStatement;

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

if ([Some Contingency]) then
{
SQLStatement.Ap pend(" has_picture=?") //note the space after the first
quote.
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
}

this.oleDbSelec tCommand1.Comma ndText = SQLStatement.To String

--
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******** *****@TK2MSFTNG P12.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_pictur e=1 AND sex='female' "

this.oleDbSelec tCommand1.Comma ndText = "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******** ********@TK2MSF TNGP09.phx.gbl. ..
Britney,

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

this.oleDbSelec tCommand1.Comma ndText = "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******** *******@TK2MSFT NGP15.phx.gbl.. .
Original code:
this.oleDbSelec tCommand1.Comma ndText = "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.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar,
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.oleDbSelec tCommand1.Comma ndText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM Jiaoyou.dbo.use rs WHERE " + WhereClause + " Order by age";
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar,
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.Ole Db.OleDbExcepti on: Incorrect syntax near
the keyword 'ORDER'.

Source Error:

Line 85: Response.Write( this.oleDbSelec tCommand1.Comma ndText);
Line 86:
Line 87: oleDbDataAdapte r1.Fill(usersDs 1);
Line 88: DataGrid1.DataB ind();
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 OleDbDataAdapte r when
connecting to our older AS400 system. I also use the OleDbDataAdapte r 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******** ******@tk2msftn gp13.phx.gbl...
thanks..
do you know what are differences between sqldataadapter and
oledbsqldataada pter?
how do I know when to use which one?

"S. Justin Gengo" <sjgengo@[no_spam_please]aboutfortunate. com> wrote in
message news:O$******** ******@TK2MSFTN GP14.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.Str ingBuilder SQLStatement;

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

if ([Some Contingency]) then
{
SQLStatement.Ap pend(" has_picture=?") //note the space after the first
quote.
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
}

this.oleDbSelec tCommand1.Comma ndText = SQLStatement.To String

--
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******** *****@TK2MSFTNG P12.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_pictur e=1 AND sex='female' "

this.oleDbSelec tCommand1.Comma ndText = "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******** ********@TK2MSF TNGP09.phx.gbl. ..
Britney,

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

this.oleDbSelec tCommand1.Comma ndText = "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******** *******@TK2MSFT NGP15.phx.gbl.. .
Original code:
this.oleDbSelec tCommand1.Comma ndText = "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.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex",
System.Data.Ole Db.OleDbType.Va rChar, 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.oleDbSelec tCommand1.Comma ndText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM Jiaoyou.dbo.use rs WHERE " + WhereClause + " Order by age";
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex",
System.Data.Ole Db.OleDbType.Va rChar, 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.Ole Db.OleDbExcepti on: Incorrect syntax
near the keyword 'ORDER'.

Source Error:

Line 85: Response.Write( this.oleDbSelec tCommand1.Comma ndText);
Line 86:
Line 87: oleDbDataAdapte r1.Fill(usersDs 1);
Line 88: DataGrid1.DataB ind();
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******** ******@TK2MSFTN GP15.phx.gbl...
Justin,
just another question, In order to set oleDbSelectComm and1.CommandTex t 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.oleDbSelec tCommand1.Comma ndText = "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$******** ******@TK2MSFTN GP14.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.Str ingBuilder SQLStatement;

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

if ([Some Contingency]) then
{
SQLStatement.Ap pend(" has_picture=?") //note the space after the first
quote.
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
}

this.oleDbSelec tCommand1.Comma ndText = SQLStatement.To String

--
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******** *****@TK2MSFTNG P12.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_pictur e=1 AND sex='female' "

this.oleDbSelec tCommand1.Comma ndText = "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******** ********@TK2MSF TNGP09.phx.gbl. ..
Britney,

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

this.oleDbSelec tCommand1.Comma ndText = "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******** *******@TK2MSFT NGP15.phx.gbl.. .
Original code:
this.oleDbSelec tCommand1.Comma ndText = "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.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar,
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.oleDbSelec tCommand1.Comma ndText = "SELECT TOP 100 user_id, password,
nick_name, sex, age, has_picture, city, state, " +
"country FROM Jiaoyou.dbo.use rs WHERE " + WhereClause + " Order by age";
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("has_picture ",
System.Data.Ole Db.OleDbType.Bo olean, 1, "has_picture")) ;
//this.oleDbSelec tCommand1.Param eters.Add(new
System.Data.Ole Db.OleDbParamet er("sex", System.Data.Ole Db.OleDbType.Va rChar,
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.Ole Db.OleDbExcepti on: Incorrect syntax near
the keyword 'ORDER'.

Source Error:

Line 85: Response.Write( this.oleDbSelec tCommand1.Comma ndText);
Line 86:
Line 87: oleDbDataAdapte r1.Fill(usersDs 1);
Line 88: DataGrid1.DataB ind();
Line 89:

Nov 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3940
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 tables tblProjects ProjectId
3
3586
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 be a completely different thing. I can do it easily using iif but obviously that is not an option here... Here is what I have: Select @ FROM my...
3
2736
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 up my passed variable from my get/set function. I have done watch's on the variable and the data name I'm looking for is present. I thought my...
7
3369
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, implemented it and was successful to make it work. Thank you Mr. Browne! However, the copy code on his web site only updates one sub-form table. My...
1
2759
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 to primitives: "where T : System.Type.Primitive" or something like that. 2. I want to limit it to an unspecified generic: "where T : List<>". 3. I...
3
3393
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 so that i can then insert a new image into the table. This works fine. My problem is that my insert statement has an error and i cant figure this...
8
4485
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 Text ( 255 ); SELECT *
4
10370
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 '09/01/2007' AND '09/30/2007' where rxfilldate,112) BETWEEN convert(varchar(2),datepart(mm,dateadd(month,-1,getdate()))) + '-01-' +...
2
3025
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 millisecond. Thanks.
0
7526
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7457
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7723
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7965
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7817
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5375
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5092
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3487
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
771
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.