Link to home
Create AccountLog in
Avatar of dhjensen
dhjensen

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

Avatar of als315
als315
Flag of Russian Federation image

You should set size for AdDecimal type, it can not be empty
Avatar of dhjensen
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
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Sorry, tried it, and no luck.
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!
Thanks!
But is it working if size is not filled? May be problem was in this name only?
I'll check it out and report back.
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
I think you should change points assingnment - remove points from me and mark your answer as solution