|
 |
access_asp thread: multiple strings in a parameter object
Message #1 by "Serge Wagemakers" <swagemakers@d...> on Fri, 26 Oct 2001 10:04:43
|
|
Hi there,
I've got a little problem on my hands... I probably haven't looked
hard enough.
The situation is this. I have a query in access 2000 which has a parameter
named region. What I want to do is to select all kinds of information
based on 1 or more regions, so the select statement in access will be
dynamically built based on this region. It would be something like this:
"SELECT * FROM A_TABLE WHERE Region IN ([A_Region])"
The code below:
dim A_Connection ' as ADODB.COnnection
dim cmdQuery ' as ADODB.Command
dim rsQuery ' as ADODB.Recordset
dim MultiStringVar
set A_Connection = Server.CreateObject("ADODB.Connection")
A_Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "data source=c:\mydb.mdb"
A_connection.Open
set cmdQuery = Server.CreateObject("ADODB.Command")
cmdQuery.CommandText = "Query_On_Regions" ' Which is the query in access
set cmdQuery.ActiveConnection = A_Connection
MultiStringVar = "Region1" & "," & "Region2"
set parID = cmdQuery.createParameter("A_Region",adChar,1,50,MultiStringVar)
cmdQuery.Parameters.Append parID
set rsQuery = cmdQuery.Execute
Do while not rsQuery.EOF
response.write rsQuery("Field_A")
response.write rsQuery("Field_B")
Loop
rsQuery.Close
A_Connection.Close
I want to have multiple strings into the "A_Region" parameter in the access
query.
When I have more than 1 region in the MultiStringVar variable, I don't see
anything, but when I have exactly 1 region, I see results. Is there
something I'm missing? Why is it that when I'm entering more than 1 string
in the parameter object, I don't get any results?
Basically the where-clause in the selectstatement has to be expanded with
the IN clause. There's nothing (in my mind) that I'm doing wrong on the
access part.
Hoping anyone can help me out a bit.
Greetz,
Serge
Message #2 by <sathish297@y...> on Tue, 30 Oct 2001 18:59:00 +0530
|
|
Probably you have to try this
strRegion= "( Region= '" & Region1 & " ' OR ' Region= '" & "Region2
&"' )
In this append Region='"&Region1&"' with the OR clause so that you can
retrieve all the records at once.
By the previous method using in it will check for Region=india,us,.. .
I think this is the wrong way.
The correct way is
SELECT * FROM A_TABLE WHERE (Region='india' or Region='US')
----- Original Message -----
From: "Serge Wagemakers" <swagemakers@d...>
To: "Access ASP" <access_asp@p...>
Sent: Friday, October 26, 2001 10:04 AM
Subject: [access_asp] multiple strings in a parameter object
> Hi there,
>
> I've got a little problem on my hands... I probably haven't looked
> hard enough.
>
> The situation is this. I have a query in access 2000 which has a parameter
> named region. What I want to do is to select all kinds of information
> based on 1 or more regions, so the select statement in access will be
> dynamically built based on this region. It would be something like this:
>
> "SELECT * FROM A_TABLE WHERE Region IN ([A_Region])"
>
> The code below:
>
> dim A_Connection ' as ADODB.COnnection
> dim cmdQuery ' as ADODB.Command
> dim rsQuery ' as ADODB.Recordset
> dim MultiStringVar
>
> set A_Connection = Server.CreateObject("ADODB.Connection")
> A_Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
> & "data source=c:\mydb.mdb"
>
> A_connection.Open
>
> set cmdQuery = Server.CreateObject("ADODB.Command")
> cmdQuery.CommandText = "Query_On_Regions" ' Which is the query in access
> set cmdQuery.ActiveConnection = A_Connection
>
> MultiStringVar = "Region1" & "," & "Region2"
>
> set parID
cmdQuery.createParameter("A_Region",adChar,1,50,MultiStringVar)
> cmdQuery.Parameters.Append parID
> set rsQuery = cmdQuery.Execute
>
> Do while not rsQuery.EOF
> response.write rsQuery("Field_A")
> response.write rsQuery("Field_B")
> Loop
> rsQuery.Close
> A_Connection.Close
>
> I want to have multiple strings into the "A_Region" parameter in the
access
> query.
> When I have more than 1 region in the MultiStringVar variable, I don't see
> anything, but when I have exactly 1 region, I see results. Is there
> something I'm missing? Why is it that when I'm entering more than 1 string
> in the parameter object, I don't get any results?
>
> Basically the where-clause in the selectstatement has to be expanded with
> the IN clause. There's nothing (in my mind) that I'm doing wrong on the
> access part.
>
> Hoping anyone can help me out a bit.
>
> Greetz,
>
> Serge
>
>
Message #3 by "Serge Wagemakers" <swagemakers@d...> on Wed, 31 Oct 2001 10:11:58 +0100
|
|
Thanks Sathish!
I probably have to create a string dynamically to fill in the regions.
A user will select one or more regions from a selectlist. These selected
regions
have to be concatenated with an 'OR' in between.
Forgive me, for my novice level of programming ;-)
Thanks again,
Regards,
Serge
----- Original Message -----
From: "sathish r" <sathish297@y...>
To: "Access ASP" <access_asp@p...>
Sent: Tuesday, October 30, 2001 2:29 PM
Subject: [access_asp] Re: multiple strings in a parameter object
> Probably you have to try this
> strRegion= "( Region= '" & Region1 & " ' OR ' Region= '" & "Region2
> &"' )
>
> In this append Region='"&Region1&"' with the OR clause so that you can
> retrieve all the records at once.
> By the previous method using in it will check for Region=india,us,.. .
> I think this is the wrong way.
> The correct way is
> SELECT * FROM A_TABLE WHERE (Region='india' or Region='US')
>
> ----- Original Message -----
> From: "Serge Wagemakers" <swagemakers@d...>
> To: "Access ASP" <access_asp@p...>
> Sent: Friday, October 26, 2001 10:04 AM
> Subject: [access_asp] multiple strings in a parameter object
>
>
> > Hi there,
> >
> > I've got a little problem on my hands... I probably haven't looked
> > hard enough.
> >
> > The situation is this. I have a query in access 2000 which has a
parameter
> > named region. What I want to do is to select all kinds of information
> > based on 1 or more regions, so the select statement in access will be
> > dynamically built based on this region. It would be something like this:
> >
> > "SELECT * FROM A_TABLE WHERE Region IN ([A_Region])"
> >
> > The code below:
> >
> > dim A_Connection ' as ADODB.COnnection
> > dim cmdQuery ' as ADODB.Command
> > dim rsQuery ' as ADODB.Recordset
> > dim MultiStringVar
> >
> > set A_Connection = Server.CreateObject("ADODB.Connection")
> > A_Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
> > & "data source=c:\mydb.mdb"
> >
> > A_connection.Open
> >
> > set cmdQuery = Server.CreateObject("ADODB.Command")
> > cmdQuery.CommandText = "Query_On_Regions" ' Which is the query in access
> > set cmdQuery.ActiveConnection = A_Connection
> >
> > MultiStringVar = "Region1" & "," & "Region2"
> >
> > set parID
> cmdQuery.createParameter("A_Region",adChar,1,50,MultiStringVar)
> > cmdQuery.Parameters.Append parID
> > set rsQuery = cmdQuery.Execute
> >
> > Do while not rsQuery.EOF
> > response.write rsQuery("Field_A")
> > response.write rsQuery("Field_B")
> > Loop
> > rsQuery.Close
> > A_Connection.Close
> >
> > I want to have multiple strings into the "A_Region" parameter in the
> access
> > query.
> > When I have more than 1 region in the MultiStringVar variable, I don't
see
> > anything, but when I have exactly 1 region, I see results. Is there
> > something I'm missing? Why is it that when I'm entering more than 1
string
> > in the parameter object, I don't get any results?
> >
> > Basically the where-clause in the selectstatement has to be expanded
with
> > the IN clause. There's nothing (in my mind) that I'm doing wrong on the
> > access part.
> >
> > Hoping anyone can help me out a bit.
> >
> > Greetz,
> >
> > Serge
|
|
|
View Cart
