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

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

--

Terry Kreft


"Joe Delphi" <(E-Mail Removed)> wrote in message
news:QO6Kg.1702$8J2.673@fed1read11...
> 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
>
>