We help IT Professionals succeed at work.

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

dhjensen
dhjensen asked
on
10,605 Views
Last Modified: 2012-06-27
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

Comment
Watch Question

CERTIFIED EXPERT

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

Author

Commented:
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!
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Sorry, tried it, and no luck.

Author

Commented:
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!

Author

Commented:
Thanks!
CERTIFIED EXPERT

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

Author

Commented:
I'll check it out and report back.

Author

Commented:
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
CERTIFIED EXPERT

Commented:
I think you should change points assingnment - remove points from me and mark your answer as solution
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.