VB6 Question re: Data Shaping relations and Shape query 
Author Message
 VB6 Question re: Data Shaping relations and Shape query

Hi, all.

I'm reading through the MSDN library teaching myself ADO and VB 6.0 new
features.  I am trying to build a hierarchical recordset using the example
on the "Accessing Rows in a Hierarchical Recordset" page.  I am able to
establish a connection to the biblio.mdb, but am getting an error on my
first rst.open statement.

Here is the code . . .

Sub datashape()

   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim rsChapter As Variant

   cnn.Provider = "MSDataShape"
   cnn.Open DataEnvironment1.Biblio

   ' Step 1
   rst.StayInSync = False
   rst.Open "SHAPE {SELECT * FROM Authors} APPEND ({SELECT * FROM 'Title
Author'} AS Chapter RELATE au_id TO au_id)", cnn

!!!!!!!!!!
The above line produces the following error:  "Run-time error '-2147217900
(80040e14)': {yeah, whatever}  Invalid SQL statement; expected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

It obviously is not accepting a "Shape" query.  I am running Pro edition,
but I don't think that's an issue.  I have ADO2.0 added to my references, so
I don't think that's an issue.  I've triple-checked my syntax.  Can someone
please help me.  I think this is a great new feature and I know it will be
well used in our existing apps.  Thank you ahead of time.
!!!!!!!!!!!!

   ' Step 2
   While Not rst.EOF
      Debug.Print rst("au_fname"), rst("au_lname"), rst("state"),
rst("au_id")

   ' Step 3
      Set rsChapter = rst("chapter")

   ' Step 4
      While Not rsChapter.EOF
         Debug.Print rsChapter(0), rsChapter(1), rsChapter(2), rsChapter(3)
         rsChapter.MoveNext
      Wend
      rst.MoveNext
   Wend
End Sub



Sun, 04 Mar 2001 03:00:00 GMT  
 VB6 Question re: Data Shaping relations and Shape query
Andy:  it may be that the MSShape uses CHAPTER as a keyword...

Quote:

>Hi, all.

>I'm reading through the MSDN library teaching myself ADO and VB 6.0 new
>features.  I am trying to build a hierarchical recordset using the example
>on the "Accessing Rows in a Hierarchical Recordset" page.  I am able to
>establish a connection to the biblio.mdb, but am getting an error on my
>first rst.open statement.

>Here is the code . . .

>Sub datashape()

>   Dim cnn As New ADODB.Connection
>   Dim rst As New ADODB.Recordset
>   Dim rsChapter As Variant

>   cnn.Provider = "MSDataShape"
>   cnn.Open DataEnvironment1.Biblio

>   ' Step 1
>   rst.StayInSync = False
>   rst.Open "SHAPE {SELECT * FROM Authors} APPEND ({SELECT * FROM 'Title
>Author'} AS Chapter RELATE au_id TO au_id)", cnn

>!!!!!!!!!!
>The above line produces the following error:  "Run-time error '-2147217900
>(80040e14)': {yeah, whatever}  Invalid SQL statement; expected 'DELETE',
>'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

>It obviously is not accepting a "Shape" query.  I am running Pro edition,
>but I don't think that's an issue.  I have ADO2.0 added to my references,
so
>I don't think that's an issue.  I've triple-checked my syntax.  Can someone
>please help me.  I think this is a great new feature and I know it will be
>well used in our existing apps.  Thank you ahead of time.
>!!!!!!!!!!!!

>   ' Step 2
>   While Not rst.EOF
>      Debug.Print rst("au_fname"), rst("au_lname"), rst("state"),
>rst("au_id")

>   ' Step 3
>      Set rsChapter = rst("chapter")

>   ' Step 4
>      While Not rsChapter.EOF
>         Debug.Print rsChapter(0), rsChapter(1), rsChapter(2), rsChapter(3)
>         rsChapter.MoveNext
>      Wend
>      rst.MoveNext
>   Wend
>End Sub

begin 666 Pol D Stafford.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..E-T869F;W)D.U!O;#M$#0I&




M4T$-"DQ!0D5,.U=/4DL[14Y#3T1)3D<]455/5$5$+5!224Y404),13HQ,3$T

M4T$-"D5-04E,.U!2148[24Y415).150Z<&%U;$!C87-E=')A:VME<BYC;VT-

`
end


Sun, 04 Mar 2001 03:00:00 GMT  
 VB6 Question re: Data Shaping relations and Shape query


Fri, 19 Jun 1992 00:00:00 GMT  
 VB6 Question re: Data Shaping relations and Shape query
Thanks, but that did not work either, though it was a good guess.

The error, after I looked at it more closely is defined as

"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'. (Error 3129)
A word that is part of a language, such as the Microsoft Jet database engine
SQL language. Reserved words include the names of statements, predefined
functions and data types, methods, operators, and objects. Examples include
SELECT, UPDATE, BETWEEN, SET, and INSERT. Dont use reserved words for
variable or object names."

Although this is not the Error # I get during run-time, the title is the
same.

Here is another snippet from help:
"A part of the Shape command is a query issued to the underlying data
provider which returns a Recordset object. The query's syntax depends on the
requirements of the underlying data provider. This will usually be
Structured Query Language (SQL), although ADO doesn't require the use of any
particular query language."

I am using an Access 7.0 backend using MicrosoftJet OLEDB 3.51 as my data
provider.  Perhaps Jet does not recognize the Shape syntax?  Is this query
even possible using Jet?  This is confusing to me because I think I can
create the same structure using the Data Environment tools.

Thanks again, y'all.

Quote:

>Andy:  it may be that the MSShape uses CHAPTER as a keyword...


>>Hi, all.

>>I'm reading through the MSDN library teaching myself ADO and VB 6.0 new
>>features.  I am trying to build a hierarchical recordset using the example
>>on the "Accessing Rows in a Hierarchical Recordset" page.  I am able to
>>establish a connection to the biblio.mdb, but am getting an error on my
>>first rst.open statement.

>>Here is the code . . .

>>Sub datashape()

>>   Dim cnn As New ADODB.Connection
>>   Dim rst As New ADODB.Recordset
>>   Dim rsChapter As Variant

>>   cnn.Provider = "MSDataShape"
>>   cnn.Open DataEnvironment1.Biblio

>>   ' Step 1
>>   rst.StayInSync = False
>>   rst.Open "SHAPE {SELECT * FROM Authors} APPEND ({SELECT * FROM 'Title
>>Author'} AS Chapter RELATE au_id TO au_id)", cnn

>>!!!!!!!!!!
>>The above line produces the following error:  "Run-time error '-2147217900
>>(80040e14)': {yeah, whatever}  Invalid SQL statement; expected 'DELETE',
>>'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

>>It obviously is not accepting a "Shape" query.  I am running Pro edition,
>>but I don't think that's an issue.  I have ADO2.0 added to my references,
>so
>>I don't think that's an issue.  I've triple-checked my syntax.  Can
someone
>>please help me.  I think this is a great new feature and I know it will be
>>well used in our existing apps.  Thank you ahead of time.
>>!!!!!!!!!!!!

>>   ' Step 2
>>   While Not rst.EOF
>>      Debug.Print rst("au_fname"), rst("au_lname"), rst("state"),
>>rst("au_id")

>>   ' Step 3
>>      Set rsChapter = rst("chapter")

>>   ' Step 4
>>      While Not rsChapter.EOF
>>         Debug.Print rsChapter(0), rsChapter(1), rsChapter(2),
rsChapter(3)
>>         rsChapter.MoveNext
>>      Wend
>>      rst.MoveNext
>>   Wend
>>End Sub



Sun, 04 Mar 2001 03:00:00 GMT  
 VB6 Question re: Data Shaping relations and Shape query


Fri, 19 Jun 1992 00:00:00 GMT  
 VB6 Question re: Data Shaping relations and Shape query
Do you have SQL Server service pack 4 installed on your server?

Quote:

>Hi, all.

>I'm reading through the MSDN library teaching myself ADO and VB 6.0 new
>features.  I am trying to build a hierarchical recordset using the example
>on the "Accessing Rows in a Hierarchical Recordset" page.  I am able to
>establish a connection to the biblio.mdb, but am getting an error on my
>first rst.open statement.

>Here is the code . . .

>Sub datashape()

>   Dim cnn As New ADODB.Connection
>   Dim rst As New ADODB.Recordset
>   Dim rsChapter As Variant

>   cnn.Provider = "MSDataShape"
>   cnn.Open DataEnvironment1.Biblio

>   ' Step 1
>   rst.StayInSync = False
>   rst.Open "SHAPE {SELECT * FROM Authors} APPEND ({SELECT * FROM 'Title
>Author'} AS Chapter RELATE au_id TO au_id)", cnn

>!!!!!!!!!!
>The above line produces the following error:  "Run-time error '-2147217900
>(80040e14)': {yeah, whatever}  Invalid SQL statement; expected 'DELETE',
>'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

>It obviously is not accepting a "Shape" query.  I am running Pro edition,
>but I don't think that's an issue.  I have ADO2.0 added to my references,
so
>I don't think that's an issue.  I've triple-checked my syntax.  Can someone
>please help me.  I think this is a great new feature and I know it will be
>well used in our existing apps.  Thank you ahead of time.
>!!!!!!!!!!!!

>   ' Step 2
>   While Not rst.EOF
>      Debug.Print rst("au_fname"), rst("au_lname"), rst("state"),
>rst("au_id")

>   ' Step 3
>      Set rsChapter = rst("chapter")

>   ' Step 4
>      While Not rsChapter.EOF
>         Debug.Print rsChapter(0), rsChapter(1), rsChapter(2), rsChapter(3)
>         rsChapter.MoveNext
>      Wend
>      rst.MoveNext
>   Wend
>End Sub



Sat, 10 Mar 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. VB6 Question re: Data Shaping relations and Shape query

2. VB6 Question re: Data Shaping relations and Shape query

3. Querying child relation table using SHAPE

4. Shapes shapes shapes

5. master/detail1,detail2,detail3 relation SHAPE command example

6. vb6 shape question

7. new shape in another shape

8. Adding a shape from Forms Shapes Stencil

9. How to differentiate a shape created from Stencil drag/drop and shape Copy/Paste

10. How to update document master shapes from template master shapes

11. Dynamically convert shapes to Process Engineering shapes

12. Dropping shapes through VBA with shape Custom Properties setting ASK =True

 

 
Powered by phpBB® Forum Software