This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1D1BA.973B6960
Content-Type: text/plain;
	charset="iso-8859-1"

The reason it doesn't work is that the DATASHAPE is a part of ADO, not SQL.
There is no Shape command in T-SQL.

-----Original Message-----
From: herbe@a... [mailto:herbe@a...]
Sent: Friday, March 22, 2002 10:37 AM
To: professional vb
Subject: [pro_vb] MSDATASHAPE Error


     I got this to work with adCmdText, but when I change it to run a 
     adCmdStoredProc, I get a error "Syntax error or access violation.
     
     
     adCmdText VB Code: (This one Works)
     
     Private Sub DataReport_Initialize()
             Set cnn = New ADODB.Connection
             Set rst = New ADODB.Recordset
             Set cmd = New ADODB.Command
             rst.CursorLocation = adUseClient
         
             ' set strings
             cnn.Open "Provider=MSDataShape; " & _
                         " Data Provider=SQLOLEDB;Server=MEDSQL; " & _
                         " UID=MED;PWD=MED;Database=AAMS"
             
             With cmd
                .ActiveConnection = cnn
                .CommandType = adCmdText
                .CommandText = "SHAPE {SELECT * FROM OMedicate WHERE 
     MEDPATID = ?} AS PATIENT " & _
                                 "APPEND ({SELECT * FROM OMEDICATE WHERE 
     RRU = ?} AS MEDICATE " & _
                                 "RELATE MEDPATID TO MEDPATID)"
                .Parameters.Refresh
                .Parameters(0).Value = "012853"
                .Parameters(1).Value = "125"
                rst.Open cmd, , adOpenDynamic, adLockReadOnly
             End With
                    
             Set DataSource = rst
     
             Debug.Print "RecordSet has "; rst.RecordCount; " records."
     
                     
     End Sub
     
     
     adCmdStoredProc VB Code: (Not Working)
     Private Sub DataReport_Initialize()
             Set cnn = New ADODB.Connection
             Set rst = New ADODB.Recordset
             Set cmd = New ADODB.Command
             rst.CursorLocation = adUseClient
         
             ' set strings
             cnn.Open "Provider=MSDataShape; " & _
                         " Data Provider=SQLOLEDB;Server=MEDSQL; " & _
                         " UID=MED;PWD=MED;Database=AAMS"
             
             
             With cmd
                 .ActiveConnection = cnn
                 .CommandType = adCmdStoredProc
                 .CommandText = "SHAPE {spClientsByDDU2} AS PATIENT " & _
                                 "APPEND ({spClientsByDDU2} AS MEDICATE " & 
     _
                                 "RELATE MEDPATID TO MEDPATID)"
                 .Parameters.Append .CreateParameter("@DDU", adChar, 
     adParamInput, 6, "12853")
                 .Parameters.Append .CreateParameter("@RRU", adChar, 
     adParamInput, 3, "125")
                 rst.Open cmd, , adOpenDynamic, adLockReadOnly
             End With
             
             Set DataSource = rst
     
             Debug.Print "RecordSet has "; rst.RecordCount; " records."
     
                     
     End Sub