Shape SQL Command With VB6 ADO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Shape SQL Command With VB6 ADO

Author

Topic

xardoz
Starting Member

3 Posts

Posted - 2006-08-08 : 12:59:05

This is a SQL I was using with MS DataEnvironment, I am moving away from that to pure ADO. I can not get this SQL to Execute, Just get a error:

Invalid SQL Statement; expected 'Delete','Insert','Porceedure','Select',or 'Update'

Any Ideas?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-08 : 14:23:53

xardoz -- this might sound like a crazy idea, but I bet that if you actually showed us the SQL that you are trying to execute we might be able to help you better. What do you think?

- Jeff

xardoz
Starting Member

3 Posts

Posted - 2006-08-08 : 14:49:57

Opps, and I had it the copy buffer too, doesnt pay to get distracted.

Set rsDataCheck = ConnSite.Execute("SHAPE ( SHAPE {SELECT Department, Product, SUM(Quanity) AS Expr1, ProductName, DepartmentName FROM " & _
                                   "CurrentDecoded GROUP BY Department, Product, ProductName, DepartmentName " & _
                                   "ORDER BY Department, Product, SUM(Quanity)}  AS ProductByDepartment COMPUTE ProductByDepartment, " & _
                                   "SUM(ProductByDepartment.'Expr1') AS SumOfProdByDept BY 'Department','DepartmentName')" & _
                                   "AS ProductByDepartment_Grouping COMPUTE SUM(ProductByDepartment_Grouping.'SumOfProdByDept') AS SumofSum, " & _
                                   "ProductByDepartment_Grouping")

And if you matters the connection string:

ConnSite.ConnectionString = "Provider=microsoft.jet.oledb.4.0;password=;persist security info=false;data source= " & strCurrentSite & "; mode=readwrite"

xardoz
Starting Member

3 Posts

Posted - 2006-08-08 : 23:37:39

It was a Mixture of my connection string / Provider Requested and latent Binding in the Data Report.
Here is the Microsoft Link

http://support.microsoft.com/?scid=kb;en-us;824256&spid=2509&sid=285

Here is the Correct Connect String:

With Cn
    .Provider = "MSDataShape"
    .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source").Value = strCurrentSite
    .Open
End With

So you are actually calling two providers at once, Sweet!

Thanks to BDI at: http://programmers-corner.com/forums/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources