Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Problems using ADO and HAVING statement (possible ADO Bug?)

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: Problems using ADO and HAVING statement (possible ADO Bug?)

    Col_C is not defined in the commandText.

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


    Originally posted by f_palsson
    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

  3. #3
    Join Date
    Feb 2004
    Posts
    4

    Re: Problems using ADO and HAVING statement (possible ADO Bug?)

    Originally posted by SimSoph
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •