CODEWITHSUNDEEP
sql-servervbams-accessstored-procedures
argent65
argent65

Reputation: 33

VBA Execution of SQL Stored Procedure Fails with adDecimal Datatype

I would appreciate any help on this problem that has me stumped: I am attempting to execute a SQL Server 2008 stored procedure from Access 365 VBA and keep faulting out with "Multiple-step OLE DB operation generated errors". This fault began when I changed a column in the target table from int datatype to decimal(3,1). (I now need to be able to store a single digit to the right of the decimal).

For troubleshooting/ testing, I stripped the stored procedure down to update this column only. (OCR_Freq is the update column, OcrxId is the record id).

I have verified/tried: 1) The table column is set to decimal(3,1). 2) The data type in the stored procedure variable is decimal(3,1). 3) The stored procedure executes without issue from SQL Server Management Studio. 4) Changing the column datatype to decimal(18,4) had no effect. 4) The vba code below executes without issue if the DataType is adInteger. 5) I use this code to execute a number of other stored procedures without issue.

'VBA CODE:
Dim Comm As ADODB.Command
Dim lngRecordsAffected As Long
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
'************************************************
Dim ocrxid As Long
Dim OCR_Freq As Variant
Dim x As Single

'testing the formatting
x = 7.2 'doesn't work
'OCR_Freq = Round(x, 1) 'doesn't work
'OCR_Freq = CDec(x) 'doesn't work
'OCR_Freq = Round(OCR_Freq, 1) 'doesn't work
OCR_Freq = CDec(Format(x, "00.0")) 'doesn't work

'connection stuff
If con.State = adStateClosed Then
    con.ConnectionString = conConnection
    con.Open
End If

Set Comm = New ADODB.Command
With Comm
    .ActiveConnection = con
    .CommandType = adCmdStoredProc
    .CommandText = "up_EOCR_TEST"

    '--- ADD PARAMETERS --------------------------------
    'OCR_Freq decimal(3,1)
    Set param1 = Comm.CreateParameter("@OCR_Freq", adDecimal, 
        adParamInput, , OCR_Freq)
    Comm.Parameters.Append param1
    'test record id
    Set param2 = Comm.CreateParameter("@OcrxId", adInteger, 
        adParamInput, , 8053)
    Comm.Parameters.Append param2

    .Execute lngRecordsAffected
End With
'END VBA CODE

//SQL Stored Procedure:
@OCR_Freq decimal(3,1) = null, 
@OcrxId int = null
as
begin
  UPDATE dbo.OCRX SET OCR_Freq=@OCR_Freq  WHERE OCR_ID=@OcrxId;
END

The error I am getting is "Multiple-step OLE DB operation generated errors" The above leads me to conclude that I am not properly "preparing" the value in vba for the stored procedure execution- adDecimal is not happy with my variable... but I am at loss as how to move forward. Any help would be appreciated.

Upvotes: 0

Views: 1183

Answers (1)

argent65
argent65

Reputation: 33

Well, the solution was staring me in the face- I forgot to set the NumericScale and precision on param1 before appending it:

'VBA CODE CORRECTED:
Dim Comm As ADODB.Command
Dim lngRecordsAffected As Long
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
'************************************************
Dim ocrxid As Long
Dim OCR_Freq As Variant
Dim x As Single

'testing the formatting
x = 7.5
OCR_Freq = x

'connection stuff
If con.State = adStateClosed Then
    con.ConnectionString = conConnection
    con.Open
End If

Set Comm = New ADODB.Command
With Comm
    .ActiveConnection = con
    .CommandType = adCmdStoredProc
    .CommandText = "up_EOCR_TEST"

    '--- ADD PARAMETERS ---------------------------------------------------
    'OCR_Freq decimal(3,1)
    Set param1 = Comm.CreateParameter("@OCR_Freq", adDecimal, adParamInput, , 
OCR_Freq)
    param1.NumericScale = 1
    param1.Precision = 3
    Comm.Parameters.Append param1

    Set param2 = Comm.CreateParameter("@OcrxId", adInteger, adParamInput, , 
8053)
    Comm.Parameters.Append param2

    .Execute lngRecordsAffected
End With

Upvotes: 1

Related Questions