asked on

How to set NumericScale and Precision with adDecimal when calling stored procedure in VBA

I've called stored procedures that use Chars, VarChars, Integers, Dates, etc. Now I've got to pass a decimal variable declared as such in SQL2005:

@HoursToResolve decimal(9,2) = NULL

How do you set the NumericScale and Precision (or "Size") when passing the variable?

I've enclosed the code to call the SP that uses a technique researched off the internet. The code is bombing at "Set prm =", etc.

I hope that's clear enough--thanks in advance for your assistance, and let me know if you need any clarification.

Private Sub Research_Update()

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim rslt As Long
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    Set cnn = New ADODB.Connection
    cnn.Open "Data Source='" & gDSN & "';User ID='USER_ID';" & _
        "Password='PASSWORD';"

    Set cmd = New ADODB.Command
    Set prm = New ADODB.Parameter
    
    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "Research_Update"
        'Pass any required input parameters
        .Parameters.Append .CreateParameter("@UserKey", adInteger, adParamInput, 4, [gLoginKey])
        .Parameters.Append .CreateParameter("@ResearchKey", adInteger, adParamInput, 4, [txtResearchKey])
        .Parameters.Append .CreateParameter("@OriginalRequestDt", adDBTimeStamp, adParamInput, 10, [gRS_DateOfInquiry])
        .Parameters.Append .CreateParameter("@RequestKey", adInteger, adParamInput, 4, [gRS_RequestKey])
        .Parameters.Append .CreateParameter("@RoutineID", adVarChar, adParamInput, 20, [gRS_RoutineID])
        .Parameters.Append .CreateParameter("@UltimateRequestor", adVarChar, adParamInput, 100, [gRS_CustomerName])
        .Parameters.Append .CreateParameter("@DivisionID", adChar, adParamInput, 2, [gRS_Plan])
        .Parameters.Append .CreateParameter("@AccountKey", adInteger, adParamInput, 4, [gRS_AccountKey])
        .Parameters.Append .CreateParameter("@ResearchCategoryKey", adInteger, adParamInput, 4, [gRS_AccountKey])
        .Parameters.Append .CreateParameter("@ResearchDescription", adVarChar, adParamInput, 2000, [gRS_ResearchDescription])
        .Parameters.Append .CreateParameter("@ResearchComments", adVarChar, adParamInput, 2000, [gRS_ResearchComments])
        .Parameters.Append .CreateParameter("@ResearchResolution", adVarChar, adParamInput, 2000, [gRS_ResearchResolution])
        .Parameters.Append .CreateParameter("@ResearchDueDt", adDBTimeStamp, adParamInput, 10, [gRS_DueDt])
        .Parameters.Append .CreateParameter("@CompletionDt", adDBTimeStamp, adParamInput, 10, [gRS_CompletionDt])
        .Parameters.Append .CreateParameter("@ResearchSource", adVarChar, adParamInput, 50, [gRS_SourceOfInquiry])
        'HERES THE ERROR: 
        Set prm = .CreateParameter("@HoursToResolve", adDecimal, adParamInput, , [gRS_HoursToResolve])
        prm.NumericScale = 9
        prm.Precision = 2
        .Parameters.Append prm
        '.Parameters.Append .CreateParameter("@HoursToResolve", adDecimal, adParamInput, , [gRS_HoursToResolve])
        .Parameters.Append .CreateParameter("@AssignedTo", adInteger, adParamInput, 4, [gRS_AssignedTo])
        .Parameters.Append .CreateParameter("@ResearchStatus", adVarChar, adParamInput, 20, [gRS_ResearchStatus])
        .Execute
        
        MsgBox "Request " & Me.txtRequestKey & " Updated."
            
        Call RetrieveTaskDetail Me.txtRequestKey
                
    End With
    
    cnn.Close

CleanUp:
    Set prm = Nothing
    Set cmd = Nothing
    Set cnn = Nothing
    If End_ErrorProcess(Err.Number, PresentRoutine, Module_Name) = ContinueProcessing Then Resume Next
End Sub

Open in new window

als315

You should set size for AdDecimal type, it can not be empty

dhjensen

ASKER

Can you provide an example of the syntax? I'm attempting to do that, and it's failing. The variable in the SP is declared as:

@HoursToResolve decimal(9,2) = NULL

Thanks!

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dhjensen

ASKER

Sorry, tried it, and no luck.

dhjensen

ASKER

OK, this is embarrasing...I screwed up the variable name gRS_HoursToResolve--should be gRS_HoursToResolution. My bad. This code now appears to be working.

Set prm = .CreateParameter("@HoursToResolve", adDecimal, adParamInput, 9, [gRS_HoursToResolution])
prm.NumericScale = 9
prm.Precision = 2

Also found this code on the web, and might incorporate it:

For Each prm In cmd.Parameters
If prm.Type = adDecimal Then
prm.NumericScale = 9
prm.Precision = 2

End if
Next prm

Hope that helps, and thanks very much for your assistance!

dhjensen

ASKER

Thanks!

als315

But is it working if size is not filled? May be problem was in this name only?

dhjensen

ASKER

I'll check it out and report back.

dhjensen

ASKER

Took out the Size (previously 9), and Access didn't error out. Incorporated the code below:

For Each prm In cmd.Parameters
If prm.Type = adDecimal Then
prm.NumericScale = 9
prm.Precision = 2
End If
Next prm

Got "Invalid Scale Value" error from back-end. Researched and the parameters should actually be reversed, (NumericScale--the number of decimals--should be 2; Precision--total number of digits--should be 9)

Found code at the website listed below and it reads accodingly:

.Parameters.Append .CreateParameter("@HoursToResolve", adDecimal, adParamInput, 9, [txtHoursToResolution])
.Parameters("@HoursToResolve").NumericScale = 2
.Parameters("@HoursToResolve").Precision = 9

I prefer this method to the "looping" at the top. Hope that clarifies and helps out!

Found this informative post on the subject:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14773

als315

I think you should change points assingnment - remove points from me and mark your answer as solution