Hi,

Look at the code below:

---------------------

Example 1

Dim cm As ADODB.Command
Dim rs As ADODB.Recordset

Set cm = New ADODB.Command

cm.ActiveConnection = "bla bla"

cm.CommandText = "SELECT [Col_A], SUM([Col_B]) AS 'Sum_B' FROM [vw_SomeView] WHERE ([Col_A]=? or [Col_A]=?) GROUP BY [Col_A] HAVING Avg([Col_C]) < ?"

'Code below returns error. Sometimes GP-fault
cm.Parameters(0).Value = 4
cm.Parameters(1).Value = 5
cm.Parameters(2).Value = 0.5
Set rs = cm.Execute
Debug.Print rs.GetString(adClipString)

---------------------------------

Example 1

Set cm = New ADODB.Command

cm.ActiveConnection = "bla bla"

cm.Prepared = True

cm.CommandText = "SELECT [Col_A], SUM([Col_B]) AS 'Sum_B' FROM [vw_SomeView] WHERE ([Col_A]=? or [Col_A]=?) GROUP BY [Col_A] HAVING Avg([Col_C]) < ?"

cm.Parameters.Append cm.CreateParameter("P1", adInteger)
cm.Parameters.Append cm.CreateParameter("P2", adInteger)
cm.Parameters.Append cm.CreateParameter("P3", adDouble)

cm.Parameters(0).Value = 4
cm.Parameters(1).Value = 5
cm.Parameters(2).Value = 0.5

Set rs = cm.Execute

Debug.Print rs.GetString(adClipString)

----------------------

I've tried the code on many machines. With ADO 2.6, 2.7, 2.8.

If i remove the HAVING part of the SQL-string, then it works.

Also, if I remove the parenthesis in

WHERE ([Col_A]=? or [Col_A]=?)

, it works..... But sometimes you need the parenthesis to express something.

I've used MS SQL Profiler. ADO doesn't even attempt to discover parameter info in Example 1. If i remove the HAVING part or parenthesises it does and the query completes.

The query works 100% if I replace the parameters with values direct in the query.

Errors range from GP-fault to ADO raising error saying the SQL string has errors (see 'comment in the source code, example 1)

Has anyone experienced anything similar?

/ Fredrik

Col_C is not defined in the commandText.

I believe that it must be part of the SELECT statement to make it work..

This could be true, but since the query works if I send a string to ADO containing the values it works, I don't think that's the problem.

SELECT [Col_A], SUM([Col_B]) AS 'Sum_B' FROM [vw_SomeView] WHERE ([Col_A]=? or [Col_A]=?) GROUP BY [Col_A] HAVING Avg([Col_C]) < ?

Doesn't work

SELECT [Col_A], SUM([Col_B]) AS 'Sum_B' FROM [vw_SomeView] WHERE ([Col_A]=1 or [Col_A]=2) GROUP BY [Col_A] HAVING Avg([Col_C]) < 3

Works

Any other suggestions?

/ F