troubleshooting Question

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

Avatar of dhjensen
dhjensen asked on
Microsoft Access
10 Comments1 Solution10811 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
als315

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros