ADO Query Question

[Joe Delphi]

Hi,

I am trying to retrieve a single value from the database using ADO.
The problem is that the value in the table is $6,000,000 but the number that
always gets returned is 0. Compiles and executes with no errors, but gets
wrong answer.

Private Sub CalculateInfraCosts(ScenarioID As Integer)
Dim TotalConstCost As Double
Dim strSQL As String
Dim cmdCommand As ADODB.Command
Dim prmConstCost As ADODB.Parameter

strSQL = "SELECT TOTAL_CONST_COST " & _
"FROM tblMiscAssumptions " & _
"WHERE SCENARIO_ID = " & CStr(ScenarioID)

Set cmdCommand = New ADODB.Command
Set prmConstCost = cmdCommand.CreateParameter(adDouble, adParamOutput)
cmdCommand.Parameters.Append prmConstCost


If DBConn.State = adStateClosed Then
DBConn.Open ConnStr
End If

Set cmdCommand.ActiveConnection = DBConn
cmdCommand.CommandText = strSQL
cmdCommand.Execute

TotalConstCost =
ameters(0) -----------TotalConstCost always comes back 0 when
it should not.

MsgBox CStr(TotalConstCost)
End Sub
--------------------------------------------


Any help appreciated.

JD

 

[Terry Kreft]

I don't know where you got that code from but it's complete rubbish. It
should be something like:-

Private Sub CalculateInfraCosts(ScenarioID As Integer)
Dim TotalConstCost As Double
Dim strSQL As String
Dim rs As ADODB.Recordset

strSQL = "SELECT TOTAL_CONST_COST " & _
"FROM tblMiscAssumptions " & _
"WHERE SCENARIO_ID = " & ScenarioID

If DBConn.State = adStateClosed Then
DBConn.Open ConnStr
End If

Set rs = DBConn.Execute(strSQL)

With rs
If Not(.Eof and .BOF) Then
TotalConstCost = .Fields(0)
End If
End With
MsgBox CStr(TotalConstCost)
End Sub