afrausto
Posts: 66
|
| Posted: 09/15/2005, 8:55 AM |
|
Hello,
Has anyone used GetRows() with CCS / MS Access? What I'm trying to do is display a 2d array report in CCS. This is what I have so far:
Dim cmd, rs, connect
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adVarChar = 200
Set cmd = Server.CreateObject ("ADODB.Command")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database.mdb;Persist Security Info=False"
cmd.ActiveConnection = connect
cmd.CommandText = "qryReport1"
cmd.CommandType = adCmdStoredProc
Set rs = Server.CreateObject ("ADODB.Recordset")
Set rs = cmd.Execute
arrReport1 = rs.GetRows()
rs.Close
Set rs = nothing
set cmd = nothing
My thoughts were to create an asp page with a label in it. Within the label I would place the above code in a Before Show event. The next obstacle is displaying the 2D array out in a table.
Anyone have any suggestions?
Thanks in advance for any help.
|
 |
 |
DonB
|
| Posted: 09/15/2005, 3:03 PM |
|
Maybe I missed something, but it appears you're reinventing the Grid
control.
What is your objective? Note that a Grid is a "2D array" containing the
same data that a GetRows() would give you.
--
DonB
http://www.gotodon.com/ccbth
"afrausto" <afrausto@forum.codecharge> wrote in message
news:64329995a85cac@news.codecharge.com...
> Hello,
>
> Has anyone used GetRows() with CCS / MS Access? What I'm trying to do is
> display a 2d array report in CCS. This is what I have so far:
>
> Dim cmd, rs, connect
> Const adCmdStoredProc = &H0004
> Const adParamInput = &H0001
> Const adVarChar = 200
>
> Set cmd = Server.CreateObject ("ADODB.Command")
> connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\database.mdb;Persist
> Security Info=False"
> cmd.ActiveConnection = connect
> cmd.CommandText = "qryReport1"
> cmd.CommandType = adCmdStoredProc
> Set rs = Server.CreateObject ("ADODB.Recordset")
> Set rs = cmd.Execute
>
> arrReport1 = rs.GetRows()
>
> rs.Close
> Set rs = nothing
> set cmd = nothing
>
> My thoughts were to create an asp page with a label in it. Within the
label I
> would place the above code in a Before Show event. The next obstacle is
> displaying the 2D array out in a table.
>
> Anyone have any suggestions?
>
> Thanks in advance for any help.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
afrausto
Posts: 66
|
| Posted: 09/15/2005, 3:54 PM |
|
Donb,
What I'm trying to accomplish is to display data from qryTurtle2 into a grid. The query is counting the number of species in a particular zone. Here is the data from qryReport1:
I want to display the results like so:
2005
Are you saying that the Grid control is able to accomplish? I wasn't able to accomplish that, that is why I entered the Query results into GetRows() so I could create a function that would get the value of each combination and write it out into a static table.
What would you recommend? Keep in mind I need to display the data by years.
Thanks for your time and efforts.
|
|
|
|
DonB
|
| Posted: 09/15/2005, 4:08 PM |
|
OK, this is the always-a-pain-in-the-butt crosstab query. No, a plain grid
can't do that - not directly, at least. There are numerous techniques for
doing this, but I don't have a URL handy to reference this. A good
reference is the book "SQL for Smarties" by Joe Celko. He's one of, if not
THE, top experts on SQL. His book goes over several ways to accomplish what
you want.
You might find that the output of an MS Access crosstab query would suffice,
but I'm not sure how that gets mangled over an ODBC data connection (if at
all). I seem to recall that only Access can correctly display the results
of one of its crosstabs.
But it would be easy to write the Access query (a wizard will do it for you)
and attempt to use it as the Data Source of a simple Grid. Maybe you'll get
lucky and it will 'just work'.
--
DonB
http://www.gotodon.com/ccbth
"afrausto" <afrausto@forum.codecharge> wrote in message
news:64329fbb19e2ce@news.codecharge.com...
> Donb,
>
> What I'm trying to accomplish is to display data from qryTurtle2 into a
grid.
> The query is counting the number of species in a particular zone. Here is
the
> data from qryReport1:
>
>
>
> I want to display the results like so:
>
> 2005
>
>
> Are you saying that the Grid control is able to accomplish? I wasn't able
to
> accomplish that, that is why I entered the Query results into GetRows() so
I
> could create a function that would get the value of each combination and
write
> it out into a static table.
>
> What would you recommend? Keep in mind I need to display the data by
years.
>
> Thanks for your time and efforts.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
afrausto
Posts: 66
|
| Posted: 09/15/2005, 5:35 PM |
|
Donb,
You are correct only MS Access can display a crosstab query. CCS doesn't even see a crosstab query as an option. In this case, I wish it did, because it would save me from getting more grey hairs. 
Anyways, I've created the query to pull the data I need, I was hoping there was a way to display it within CCS as shown above. If there's a will there's a way.
Thank you for the book reference. I will definitely research that option.
afrausto
|
|
|
|
|
