Problem with ADO Parameter value [resolved]

[ahara]

Anyone out there know how to pass a sql statement as a parameter value?

Set para = cmdCommand.CreateParameter("estimate_id", adNumeric, adParamReturnValue, 20, "SELECT NVL(MAX(estimate_id) + 1,1) FROM time_estimates")
cmdCommand.Parameters.Append para

I'm sure there is a way to do this - maybe with the right data type and parameter type combination (2nd and 3rd arguments in CreateParameter method)....I'll keep checking the web, but if anyone has done this before, please demonstrate. Thanks.

[Buzby]

Why not run the SQL query first, return the recordset with the number you want, and then use this number as the parameter:

eg: (I haven't got VB handy so my syntax may not be 100%)

Dim tmpRS As New ADODB.Recorset
Dim tmpResult AS Double
 
tmpRS.Open "SELECT NVL(MAX(estimate_id) + 1,1) AS TempResult FROM time_estimates",[connection],adOpenStatic, adOpenReadOnly
 
tmpResult = tmprs.Fields("TempResult")
tmpRS.Close
 
Set para = cmdCommand.CreateParameter("estimate_id", adNumeric, adParamReturnValue, 20, tmpResult)
cmdCommand.Parameters.Append para

[ahara]

appreciate your reply, but it was like that before I began changing it....we have decided here to modify some of our apps and take advantage of Oracle's caching capabilities - we are trying to minimize db queries....parameter objects are being implemented everywhere, but this is the first time I am trying to pass a statement.

[apps_tech]

Why dont you store the sql statement in a variable and call the variable instead?? Something like:

Dim strSQL as String
strSQL="SELECT NVL(MAX(estimate_id) + 1,1) FROM time_estimates"
Set para = cmdCommand.CreateParameter("estimate_id", adNumeric, adParamReturnValue, 20, strSQL)
cmdCommand.Parameters.Append para

I hope this helps.

[ahara]

yeah I hear ya, and thanks for looking at it - I am actually trying to see if I can do it all in one statement without hitting the db. I suspect however that it is not possible to do afterall.
Thanks again

[brucevde]

The last argument of the CreateParameter function is the Value of the parameter not an SQL statement.

The SQL Statement in your original post does not even have to use parameters.

Here is how to use the ADO Command object to execute a procedure( that requires parameters).

Set objCMD = New ADODB.Command
With objCMD
    Set .ActiveConnection = Some ADO Connection object
    .CommandText = "Select * From SomeTable Where Field1 = ? And Field2 = ?"
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter("RetValue", adInteger, adParamReturnValue)
 
    .Parameters.Append .CreateParameter("Field1", Datatype, Direction, Size, Value)
    .Parameters.Append .CreateParameter("Field1", Datatype, Direction, Size, Value)
 
    .Execute ,,adExecuteNoRecords 'Action queries
 
    Set RecordSetObject = .Execute 'Row returning queries
 
End With

The ADO Command object always returns an Read Only Forward Only cursor (for row returning queries).

Use the RecordSet.Open method to create a specific cursor type.

RecordsetObject.Open objCMD, , adOpenStatic, adLockReadOnly

[ahara]

thanks very much for the info brucevde (especially the cursor bit - makes a lot of sense) cheers

[rplcmint]

This is a very informative subject that I easily forgot about! I can use this in my app.