Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: 3708 Error in parameter for ADO Stored Procedure

    I have the following code and it crashes everytime at the 3rd parameter no matter if I hard code it or pass a variable - If I exclude the 3rd parameter the code runs

    Public Function refreshADO(intclient As Double, Optional intProject As Double, Optional STRUSER As String, Optional BLNuseclient)
    Dim cmd As New ADODB.Command, _
    prm As ADODB.Parameter
    STRUSER = GetUser() ' environ function
    Set cmd.ActiveConnection = CurrentProject.Connection

    cmd.CommandText = "RefreshPeriodicDataDimensions"
    cmd.CommandType = adCmdStoredProc

    ' Create a parameter.
    Set prm = cmd.CreateParameter(intclient, adInteger, adParamInput)
    prm.VALUE = intclient
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter(intProject, adInteger, adParamInput)
    prm.VALUE = intProject
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter(STRUSER, adVarChar, adParamInput)
    prm.VALUE = "jone4502"
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter(BLNuseclient, adBoolean, adParamInput)
    prm.VALUE = 1
    cmd.Parameters.Append prm

    ' ' Append the parameter to the command object
    ' cmd.Parameters.Append prm1
    ' cmd.Parameters.Append prm2
    ' cmd.Parameters.Append prm3
    ' cmd.Parameters.Append prm4

    cmd.Execute


    End Function
    Dale Houston, TX

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    needed a prm.size =
    and it was fine
    Dale Houston, TX

  3. #3
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by axsprog View Post
    needed a prm.size =
    and it was fine
    Try This:

    Code:
    Public Function refreshADO(intclient As Double, Optional intProject As Double, Optional STRUSER As String, Optional BLNuseclient)
    
    
    Dim cmd As New ADODB.Command, _
    prm As ADODB.Parameter
    
    Set cmd.ActiveConnection = CurrentProject.Connection
    
    cmd.CommandText = "RefreshPeriodicDataDimensions"
    cmd.CommandType = adCmdStoredProc
    
    ' Create a parameter.
    Set prm = cmd.CreateParameter("intclient", adInteger, adParamInput)
    prm.VALUE = intclient
    
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("intProject", adInteger, adParamInput)
    prm.VALUE = intProject
    
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("STRUSER", adVarChar, adParamInput)
    prm.VALUE = STRUSER
    
    cmd.Parameters.Append prm
    Set prm = cmd.CreateParameter("BLNuseclient", adBoolean, adParamInput)
    prm.VALUE = BLNuseclient
    
    cmd.Parameters.Append prm
    
    ' ' Append the parameter to the command object
    ' cmd.Parameters.Append prm1
    ' cmd.Parameters.Append prm2
    ' cmd.Parameters.Append prm3
    ' cmd.Parameters.Append prm4
    
    cmd.Execute
    
    
    End Function


    A problem I see with your code is that if you do not pass a optional parameter to the function, your code still tried to set it as a parameter without a value. Is that really what you want?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •