using ADO SHAPE command (Access 2000 Win 2000 SR-1)

Topic

New Reply

I am trying to replicate programmically the grouping and ordering function of an Access report using ado and asp and the SHAPE command. I have gotten things set up to open the MSDataShape provider and feed it a SHAPE string like this:

objCommand.CommandText = “SHAPE ” & _
“{SELECT Categories, CRCGType, MemberType FROM tblOutlookContacts} ” & _
“APPEND ({SELECT Categories, FullName, Email1Address FROM tblOutlookContacts} ” & _
“RELATE Categories TO Categories)”

That seems to parse fine but on outputting the SHAPE command I get an error

Microsoft VBScript runtime error ‘800a01a8’

Object required: ‘[string: “Brooks, Duval, Jim_W”]’

when using the following asp to print the results out:

Do while Not objRS.EOF
Response.Write “Counties Covered: ” & objRS(“Categories”) & _
“CRCG Type: ” & objRS(“CRCGType”) & _
“Member Type: ” & objRS(“MemberType”) & _

SET rsCategories = objRS(“Categories”).Value

While Not rsCategories.EOF
Response.Write “Name: ” & objRS(“FullName”) & _
“Email Address: ” & objRS(“Email1Address”) & _
Wend
objRS.MoveNext
Response.Write “

---

”
LOOP

where the offending line is the SET section in the output code. I’m completely new to the SHAPES structure and have been looking at MSDN articles for help. I think there may be a problem trying to assign the parent correctly (which is Categories, a series of County names comma-separated). Any ideas on what might be happening here?

I’ve taken this discussion up in the web design and scripting forum but it was advised that i ask the Access mavens as this may have to do with the proper use of SHAPE.

TIA

Reply | Quote

Replies

I’m sorry but I can’t figure out what you’re doing. I’ve worked with SHAPE, but you’re code is too fragmentary to figure out. Where does objRS come from and why are you trying to set what looks like a recordset variable to a value? You use the SET keyword to instantiate an object, not to assign a value.

Reply | Quote

sorry if i didn’t post enough of the code. I have this as the objRS source:

Set objConn = Server.CreateObject(“ADODB.Connection”)
Set objRS = Server.CreateObject(“ADODB.Recordset”)
objConn.Open = “Provider=MSDataShape; Data Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & Server.MapPath(“”) & “MDBCRCG_Contacts.mdb;”

As for using SET I was trying to adapt some code on a tutorial site:

‘ Loop through the publishers
Response.Write “

”
While Not rsPublishers.EOF
Response.Write “
• ” & rsPublishers(“Pub_Name”)

  To access the child recordset, we set a variable to point to the Value of the field containing the child. In this case it’s rsTitles:

  ‘ Now the titles
  Response.Write “

  ”
  Set rsTitles = rsPublishers(“rsTitles”).Value

  The variable rsTitles is now a recordset, and behaves just like an ordinary one. So, we can loop through the values for this recordset, which only contains titles for the matching parent Publisher:

  ‘ Loop through the titles
  While Not rsTitles.EOF
  Response.Write “

  • ” & rsTitles(“title”)
    rsTitles.MoveNext
    Wend
    Response.Write “[/list]”

    ‘ Move to the next publisher
    rsPublishers.MoveNext
    Wend
    Response.Write “[/list]”

    rsPublishers.Close
    Set rsPublishers = Nothing
    Set rsTitles = Nothing
    %>

As you can see, the example code uses SET rsTitles = rsPublishers(“rsTitles”).value and I read that as a recordset value (that is, the parent data to the child details)

From what I’ve got so far, I can successfully open and execute the SHAPE command but it does indeed appear that the SET part is faulty. Are you supposed to use SET to indicate an alias for the parent SELECT string and how do you load the value for each ‘header’ part in the SHAPE output?

Another thing I’ve noticed is doing SHAPE commands badly can cause asp to become a huge memore hog. I’ve had to reboot twice in testing. But, as the germans say, ‘C’est le vie’.

Reply | Quote