Re: where clause parameter..
- From: "S. Justin Gengo" <sjgengo@[no_spam_please]aboutfortunate.com>
- Date: Tue, 13 Sep 2005 13:34:27 -0500
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" <britneychen_2001@xxxxxxxxx> wrote in message
news:uCAicsIuFHA.2504@xxxxxxxxxxxxxxxxxxxxxxx
> 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$UJbiHuFHA.3452@xxxxxxxxxxxxxxxxxxxxxxx
>> Britney,
>>
>> You can't use parameters that way.
>>
>> What you should do is add your question marks and parameters dynamically
>> to the entire statement. Something like this:
>>
>> System.Text.StringBuilder SQLStatement;
>>
>> SQLStatement.Append("SELECT TOP 100 user_id FROM dbo.users WHERE");
>>
>> if ([Some Contingency]) then
>> {
>> SQLStatement.Append(" has_picture=?") //note the space after the first
>> quote.
>> this.oleDbSelectCommand1.Parameters.Add(new
>> System.Data.OleDb.OleDbParameter("has_picture",
>> System.Data.OleDb.OleDbType.Boolean, 1, "has_picture"));
>> }
>>
>> this.oleDbSelectCommand1.CommandText = SQLStatement.ToString
>>
>> --
>> Sincerely,
>>
>> S. Justin Gengo, MCP
>> Web Developer / Programmer
>>
>> www.aboutfortunate.com
>>
>> "Out of chaos comes order."
>> Nietzsche
>>
>>
>> "Britney" <britneychen_2001@xxxxxxxxx> wrote in message
>> news:ehz1$SHuFHA.596@xxxxxxxxxxxxxxxxxxxxxxx
>> No.. you don't get my question. I don't want this format. I'm doing an
>> advance search, so my where conditions are dynamic.
>>
>>
>> for a simple example: WhereClause = "has_picture=1 AND sex='female' "
>>
>> this.oleDbSelectCommand1.CommandText = "SELECT TOP 100 user_id, password,
>> nick_name, sex, age, has_picture, city, state, " +
>> "country FROM dbo.users WHERE " + WhereClause + " ORDER BY age "
>>
>> however I got error when doing filling.
>>
>>
>>
>>
>> "S. Justin Gengo" <sjgengo@[no_spam_please]aboutfortunate.com> wrote in
>> message news:uO3d1M%23tFHA.2064@xxxxxxxxxxxxxxxxxxxxxxx
>> Britney,
>>
>> Try putting the single apostraphes around the sex parameter so it will
>> look like:
>>
>> this.oleDbSelectCommand1.CommandText = "SELECT TOP 100 user_id, password,
>> nick_name, sex, age, has_picture, city, state, " +
>> "country FROM dbo.users WHERE (has_picture = ?) AND (sex = '?') ORDER BY
>> age "
>>
>> --
>> Sincerely,
>>
>> S. Justin Gengo, MCP
>> Web Developer / Programmer
>>
>> www.aboutfortunate.com
>>
>> "Out of chaos comes order."
>> Nietzsche
>> "Britney" <britneychen_2001@xxxxxxxxx> wrote in message
>> news:u5YloH%23tFHA.464@xxxxxxxxxxxxxxxxxxxxxxx
>> Original code:
>> this.oleDbSelectCommand1.CommandText = "SELECT TOP 100 user_id, password,
>> nick_name, sex, age, has_picture, city, state, " +
>> "country FROM dbo.users WHERE (has_picture = ?) AND (sex = ?) ORDER BY
>> age "
>>
>> this.oleDbSelectCommand1.Parameters.Add(new
>> System.Data.OleDb.OleDbParameter("has_picture",
>> System.Data.OleDb.OleDbType.Boolean, 1, "has_picture"));
>> this.oleDbSelectCommand1.Parameters.Add(new
>> System.Data.OleDb.OleDbParameter("sex",
>> System.Data.OleDb.OleDbType.VarChar, 10, "sex"));
>>
>>
>>
>> but I want to modify it so (has_picture = ?) AND (sex = ?) become a
>> parameter.
>>
>> Now the code look like:
>> --for example:
>> string WhereClause = " has_picture=1 and sex='female' ";
>> this.oleDbSelectCommand1.CommandText = "SELECT TOP 100 user_id, password,
>> nick_name, sex, age, has_picture, city, state, " +
>> "country FROM Jiaoyou.dbo.users WHERE " + WhereClause + " Order by age";
>> //this.oleDbSelectCommand1.Parameters.Add(new
>> System.Data.OleDb.OleDbParameter("has_picture",
>> System.Data.OleDb.OleDbType.Boolean, 1, "has_picture"));
>> //this.oleDbSelectCommand1.Parameters.Add(new
>> System.Data.OleDb.OleDbParameter("sex",
>> System.Data.OleDb.OleDbType.VarChar, 10, "sex"));
>>
>> But somehow I got errors when go to URL. what is the problem?
>> Incorrect syntax near the keyword 'ORDER'.
>> Description: An unhandled exception occurred during the execution of the
>> current web request. Please review the stack trace for more information
>> about the error and where it originated in the code.
>>
>> Exception Details: System.Data.OleDb.OleDbException: Incorrect syntax
>> near the keyword 'ORDER'.
>>
>> Source Error:
>>
>> Line 85: Response.Write(this.oleDbSelectCommand1.CommandText);
>> Line 86:
>> Line 87: oleDbDataAdapter1.Fill(usersDs1);
>> Line 88: DataGrid1.DataBind();
>> Line 89:
>>
>
>
.
- References:
- Re: where clause parameter..
- From: S. Justin Gengo
- Re: where clause parameter..
- Prev by Date: Re: Visual Web Developer uses Firefox instead of IE?
- Next by Date: Re: where clause parameter..
- Previous by thread: Re: where clause parameter..
- Next by thread: Re: where clause parameter..
- Index(es):
Relevant Pages
|
