Need Help Resolving Parameter Issue 
Author Message
 Need Help Resolving Parameter Issue

Howdy all!

We are using parameterized commands in ADO to pass T-SQL statements to
a SQL 2000 Server.  We have several commands that use subqueries, as
in the following example:

------   CODE SAMPLE ------

Public gCN           as ADODB.Connection
Public gCMD          as ADODB.Command

...

With gCMD
   If gCN.State = adStateClosed Then
      gCN.Open cDSN_PM2002
   End If
   Set .ActiveConnection = gCN
   For nI = 0 To .Parameters.Count - 1
      .Parameters.Delete 0
   Next
   .Parameters.Append .CreateParameter(, adVarChar,, 9,
Mid(sContractNum, 1, 9))

sSQL = "UPDATE ContrPaymentLog SET AmtPaid=" _
     & "(SELECT SUM(IsNull(PDA.CurrEstmChrg,0) +
(IsNull(PDA.CurrRetainAmt,0)" _
     & "- IsNull(PDA.CurrReleaseAmt,0))) " _
     & "FROM ProjDetailAcct PDA, ProjDetail PD " _
     & "WHERE PDA.ProjDetailID = PD.ProjDetailID AND PD.ContractNum=?
" _
     & "AND PD.IsPayable='Y') " _
     & "WHERE ContrPaymentLogID=?"

   .Parameters.Append .CreateParameter(, adInteger, , ,
Val(sContrPaymentLogID))
   .CommandText = sSQL

------ END CODE SAMPLE -----

When we ran the code using the ODBC driver (which used MSDASQL.1),
everything ran smoothly.  When we switched to a hard-coded connection
string:

   gcn.ConnectionString="Provider=SQLOLEDB;Data Source=SERVER1;" _
      & "Initial Catalog=SampleDB;Integrated Security=SSPI"

... we got the following error:

Error -2147467259
Parameter Information cannot be derived from SQL statements with
sub-select queries. Set parameter information before preparing
command.

Now... we can eliminate this error by continuing to use ODBC, or by
simply saying gCMD.Prepared=False... but that defeats the entire
purpose of using a parametrized query.  I have read the MS Knowledge
Base article 235053 ( http://www.*-*-*.com/ ;en-us;q235053)
and frankly, this article is of no help to me.

Any suggestions for a better work-around here?  And can anyone address
the fundamental question of WHY the ODBC driver can handle the
subquery, but the sophisticated OLEDB provider does not?

Thanks!

-- Mike Brannon



Wed, 25 May 2005 03:26:27 GMT  
 Need Help Resolving Parameter Issue
Mike,

Try not to delete parameter from collection, but reset gCMD object variable

Set gCMD=New ADODB.Command

then set all other parameters. see if it works

--
Val Mazur
Microsoft MVP


Quote:
> Howdy all!

> We are using parameterized commands in ADO to pass T-SQL statements to
> a SQL 2000 Server.  We have several commands that use subqueries, as
> in the following example:

> ------   CODE SAMPLE ------

> Public gCN           as ADODB.Connection
> Public gCMD          as ADODB.Command

> ...

> With gCMD
>    If gCN.State = adStateClosed Then
>       gCN.Open cDSN_PM2002
>    End If
>    Set .ActiveConnection = gCN
>    For nI = 0 To .Parameters.Count - 1
>       .Parameters.Delete 0
>    Next
>    .Parameters.Append .CreateParameter(, adVarChar,, 9,
> Mid(sContractNum, 1, 9))

> sSQL = "UPDATE ContrPaymentLog SET AmtPaid=" _
>      & "(SELECT SUM(IsNull(PDA.CurrEstmChrg,0) +
> (IsNull(PDA.CurrRetainAmt,0)" _
>      & "- IsNull(PDA.CurrReleaseAmt,0))) " _
>      & "FROM ProjDetailAcct PDA, ProjDetail PD " _
>      & "WHERE PDA.ProjDetailID = PD.ProjDetailID AND PD.ContractNum=?
> " _
>      & "AND PD.IsPayable='Y') " _
>      & "WHERE ContrPaymentLogID=?"

>    .Parameters.Append .CreateParameter(, adInteger, , ,
> Val(sContrPaymentLogID))
>    .CommandText = sSQL

> ------ END CODE SAMPLE -----

> When we ran the code using the ODBC driver (which used MSDASQL.1),
> everything ran smoothly.  When we switched to a hard-coded connection
> string:

>    gcn.ConnectionString="Provider=SQLOLEDB;Data Source=SERVER1;" _
>       & "Initial Catalog=SampleDB;Integrated Security=SSPI"

> ... we got the following error:

> Error -2147467259
> Parameter Information cannot be derived from SQL statements with
> sub-select queries. Set parameter information before preparing
> command.

> Now... we can eliminate this error by continuing to use ODBC, or by
> simply saying gCMD.Prepared=False... but that defeats the entire
> purpose of using a parametrized query.  I have read the MS Knowledge
> Base article 235053

(http://support.microsoft.com/default.aspx?scid=KB;en-us;q235053)

- Show quoted text -

Quote:
> and frankly, this article is of no help to me.

> Any suggestions for a better work-around here?  And can anyone address
> the fundamental question of WHY the ODBC driver can handle the
> subquery, but the sophisticated OLEDB provider does not?

> Thanks!

> -- Mike Brannon



Wed, 25 May 2005 03:45:27 GMT  
 Need Help Resolving Parameter Issue

Hello,

Thank you for using the Microsoft Newsgroups.

Please refer to: PRB: E_FAIL Returned from Prepare() When SQL Statement
Contains a Parameter in a Subquery
 http://support.microsoft.com/default.aspx?scid=KB;EN-US;235053

If you have any questions, please reply to this post.

Regards,  

Jian-Wei Yu
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.



Sat, 28 May 2005 14:19:48 GMT  
 Need Help Resolving Parameter Issue
Jian-Wei --

As you can see from my original post, I did discover this kB article,
and it was of little or no use to me.  Their recommended solution:

**    You must call CCommand::SetParameterInfo() or
**    ICommandWithParameters::SetParameterInfo()
**    before calling Prepare().

... makes no sense to me within a VB context.  Am I mistaken on this
topic?  Is there a clear cut way to resolve the subquery parameter
issue within VB using ADO?

-- Mike


Quote:
> Hello,

> Thank you for using the Microsoft Newsgroups.

> Please refer to: PRB: E_FAIL Returned from Prepare() When SQL Statement
> Contains a Parameter in a Subquery
>  http://support.microsoft.com/default.aspx?scid=KB;EN-US;235053

> If you have any questions, please reply to this post.

> Regards,  

> Jian-Wei Yu
> Microsoft Support

> This posting is provided "AS IS" with no warranties, and confers no rights.



Mon, 30 May 2005 04:46:34 GMT  
 Need Help Resolving Parameter Issue

Hi Mike,

This solution is not applied to VB, and it is a limitation of ADO.

We can use stored procedure to walk around this issue.

Regards,  

Jian-Wei Yu
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.



Mon, 30 May 2005 15:16:03 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. AAA - Mail Merge Resolved - but still need help

2. Need help in resolving circular references between objects

3. URGENT! Help needed in resolving an installation problem

4. URGENT! Help needed in resolving an installation problem

5. Help needed: Setting parameters in parameter queries

6. Help needed: Setting parameters in parameter queries

7. Need help - ASP build issue

8. Need help on Read Only issue

9. Need help with ADO recordset sorting issue

10. Help needed on VB6 issue

11. Winsock application issue: NEED HELP

12. annoying clipboard issue...need some help

 

 
Powered by phpBB® Forum Software