runtime error 214721900 (80040e14)

[aggeliki23]

Hi all,
I am getting a runtime error
Invalid Sql Statement, expected 'DELETE','INSERT','PROCEDURE','SELECT' OR 'UPDATE'

sql= "Select * from Orders " & _
"where OrderID like " & 'ORD02%' & _
" and ShipName like " & '%'

sqlOrdDet = "Select * from OrderDetails " & _
"where OrderID='" & rsOrd!OrderID & "'"

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText

cmd.CommandText = "SHAPE {" & sql & "} APPEND ({" & _
sqlOrdDet & "} AS rsDetails " & _
"RELATE OrderID TO OrderID AS rsOrders)"

cmd.execute 'here occurs me error.

I can't find what's wrong here. sql and sqlOrdDet is right for me. Can anyone help me please?
Any help will be appreciate.

 

[dglienna]

Take a look at this:

Private Sub Command1_Click()
    Dim cnLvConnection As ADODB.Connection
    Set cnLvConnection = New ADODB.Connection
    Dim rsLvRecordset As ADODB.Recordset
    Set rsLvRecordset = New ADODB.Recordset
    With cnLvConnection
        .Provider = "MSDataShape.1"
        .ConnectionString = "Data Source=" & App.Path & "\db1.mdb;" _
                          & "Data Provider=Microsoft.Jet.OLEDB.4.0;"
        .Open
        With rsLvRecordset
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
        End With
        Set rsLvRecordset = .Execute("SHAPE {SELECT c.CustomerName As Customer, c.CustomerID FROM Customers c ORDER BY c.CustomerName} As Customers" _
                                   & " APPEND ((SHAPE {SELECT oh.OrderNumber As [Order No], oh.CustomerID, oh.OrderHeaderID FROM OrderHeaders oh ORDER BY oh.OrderNumber} As OrderHeaders" _
                                   & "          APPEND ({SELECT od.OrderLine As [Line], od.OrderLineDescription As [Description], od.OrderLineQuantity As Quantity, od.OrderHeaderID FROM OrderDetails od ORDER BY od.OrderLine} As OrderDetails" _
                                   & "          RELATE OrderHeaderID TO OrderHeaderID))" _
                                   & " RELATE CustomerID TO CustomerID)")
    End With
    '   Setup Grid
    Set Me.MSHFlexGrid1.Recordset = rsLvRecordset
    Me.MSHFlexGrid1.ColWidth(1, 0) = 0 ' c.CustomerID
    Me.MSHFlexGrid1.ColWidth(1, 1) = 0 ' oh.CustomerID
    Me.MSHFlexGrid1.ColWidth(2, 1) = 0 ' oh.OrderHeaderID
    Me.MSHFlexGrid1.ColWidth(3, 2) = 0 ' od.OrderHeaderID
    '   Tidy up
    If Not rsLvRecordset Is Nothing Then
        If rsLvRecordset.State <> adStateClosed Then
            rsLvRecordset.Close
        End If
        Set rsLvRecordset = Nothing
    End If
    If Not cnLvConnection Is Nothing Then
        If cnLvConnection.State <> adStateClosed Then
            cnLvConnection.Close
        End If
        Set cnLvConnection = Nothing
    End If
End Sub

-David
2006 Microsoft Most Valueable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[aggeliki23]

I found why the statement i refered occured me this error. The reason was that i hadn't set the provider in MSDataShape.1 in the connection string. Now it works all right.
I have a question to do. Can i use ado shape command with data provider set to MSDASQL.1 ? Or i have to use it only with Microsoft Jet OleDb?

Thank you
very much.