UPDATE Statement Not Updating

[Joe Delphi]

Hi,

I am attempting to update some rows in a table using the code below

Dim rs As ADODB.Recordset
Dim MonthID(240) As Integer
Dim i, j As Integer
Dim PctTotalCost(240) As Double
Dim MonthlyConstCost(240) As Currency
Dim strUpdate As String
Dim cmdCommand As ADODB.Command

 

[Douglas J. Steele]

I know you say that MonthlyConstCost(j) is "the correct non-zero value", but
that's impossible for any of us to tell, since you don't show any of the
calculations that are going on!

Have you checked to see what strUpdate looks like before it runs?

BTW (and totally unrelated to your problem), Dim i, j As Integer probably
isn't doing what you think it does. That defines i as a Variant, and j as an
Integer. Assuming you want both variables to be Integers, you need to use
Dim i As Integer, j As Integer

 

[Joe Delphi]

What I meant to say is that I have been running it through the debugger and
watching the variables and can tell, by that method, that the
MonthlyConstCost(j) is the correct value, The MsgBox strUpdate command
allows me to see the SQL and know that it is what I expect. With the
debugger I can also tell that it is executing the For loop properly. No
error messages, it is just that when I check the table after running this,
that nothing has been updated.

Thank you for the tip on Dim,i,j. Here is the code in its entirety.

------------------------------------------------------------------------------------------------------------------------------

Private Sub CalculateInfraCosts(ScenarioID As Integer)
Dim rs As ADODB.Recordset
Dim MonthID(240) As Integer
Dim i As Integer
Dim j As Integer
Dim PctTotalCost(240) As Double
Dim MonthlyConstCost(240) As Currency
Dim strUpdate As String
Dim cmdCommand As ADODB.Command



Set rs = New ADODB.Recordset
With rs
.CursorType = adOpenStatic
.Open "SELECT MONTH_ID, PCT_TOTAL_COST " & _
"FROM tblInfraCostAssumptions " & _
"WHERE SCENARIO_ID = " & CStr(ScenarioID),
CurrentProject.Connection
End With

'Assign to local variables.'

rs.MoveFirst
For i = 1 To rs.RecordCount
MonthID(i) = rs.Fields(0)
PctTotalCost(i) = rs.Fields(1)
rs.MoveNext
Next i

'Get the Total Infrastructure Construction Cost for this scenario.'

GetMiscAssumptions (ScenarioID)

'Compute the construction cost by month and update values in table.'

If DBConn.State = adStateClosed Then
DBConn.Open ConnStr
End If
Set cmdCommand = New ADODB.Command

For j = 1 To rs.RecordCount
MonthlyConstCost(j) = PctTotalCost(j) * TotalConstCost
strUpdate = "UPDATE tblCashFlowSummary " & _
"SET INFRA_COSTS = " & MonthlyConstCost(j) & _
" WHERE SCENARIO_ID = " & CStr(ScenarioID) & " AND
MONTHLY_ID = " & CStr(MonthID(j))

MsgBox strUpdate
Set cmdCommand.ActiveConnection = DBConn
cmdCommand.CommandType = adCmdText
cmdCommand.CommandText = strUpdate
cmdCommand.Execute , adExecuteNoRecords
Next j
Set cmdCommand = Nothing
Set rs = Nothing
End Sub

 

[Douglas J. Steele]

Try checking the ADO Errors collection.

Dim ADOErr As ADODB.Error

Set cmdCommand.ActiveConnection = DBConn
cmdCommand.CommandType = adCmdText
cmdCommand.CommandText = strUpdate
cmdCommand.Execute , adExecuteNoRecords
If DBConn.Errors.Count > 0 Then
For Each ADOErr In DBConn.Errors
MsgBox ADOErr.Number & ": " & _
ADOErr.Description
Next ADOErr
End If


And another observation on your code, again that has no bearing on the
specific problem. There's no need to repeat these 2 lines of code inside the
loop:

Set cmdCommand.ActiveConnection = DBConn
cmdCommand.CommandType = adCmdText

 

[Joe Delphi]

I tried your code and the ADO Errors collection says that there are no
errors.

I am wondering about my UPDATE statement. This is what it looks like in
the immediate window:

UPDATE tblCashFlowSummary SET INFRA_COSTS = 102000 WHERE SCENARIO_ID = 1 AND
MONTHLY_ID = 1

The 102000 is a currency value and INFRA_COSTS is a field with datatype
currency. Should I be doing anything special to that 102000 in order to
get it to update properly - does it require quotation marks?


JD

 

[Dirk Goldgar]

[...]
cmdCommand.Execute , adExecuteNoRecords

Looks ot me like you're missing a comma. Have you tried

cmdCommand.Execute , , adExecuteNoRecords

or just

cmdCommand.Execute

?

 

[Joe Delphi]

Yes, I tried both of the ways that you describe and they both give me an
error message "No Value Given For Parameter Supplied".

I realize that the syntax that I actually used, does not jive with the API
description in the Help file, but its the only syntax that does not give me
that blasted error message. Go figure.

JD

[...]
cmdCommand.Execute , adExecuteNoRecords

Looks ot me like you're missing a comma. Have you tried

cmdCommand.Execute , , adExecuteNoRecords

or just

cmdCommand.Execute

?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

Hold on. That error message implies that you've mistyped the name of one of
the fields in your Update statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, I tried both of the ways that you describe and they both give me an
error message "No Value Given For Parameter Supplied".

I realize that the syntax that I actually used, does not jive with the API
description in the Help file, but its the only syntax that does not give
me that blasted error message. Go figure.

JD

[...]
cmdCommand.Execute , adExecuteNoRecords

Looks ot me like you're missing a comma. Have you tried

cmdCommand.Execute , , adExecuteNoRecords

or just

cmdCommand.Execute

?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)