Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Angry Unanswered: Passing Values to SP from ADO

    Hi,

    I'm very confused.
    I have a front end Access Project with a Backend SQL Server DB. I have a parameter stored procedure with 5 parameters which basically searches a database and dumps the data into a Temp table, here is the stored procedure:

    ===========================================
    CREATE PROCEDURE dbo.SP_Report_ComIssue_TempForPrint
    (
    @RCaller varchar(50) = '%',
    @ROpsMgr varchar(50) = '%',
    @RPropMgr varchar(50) = '%',
    @RDate varchar(50) = '%',
    @RComStat varchar(50) = '%',
    @RBID varchar(50) = '%')
    AS
    IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name ='##TempForPrint') DROP TABLE [##TempForPrint]


    SELECT dbo.tblComIssue.ComID, CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
    ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
    dbo.tblProperty.AVPID,
    dbo.tblProperty.OpsMgrID,
    dbo.tblProperty.ProjectMgrID,
    dbo.tblProperty.SupervisorID,
    dbo.tblComIssue.IssueTitle,
    dbo.tblProperty.Division,
    dbo.tblComIssue.ComDate,
    dbo.tblComIssue.ComType,
    dbo.tblComIssue.IssueTxt,
    ISNULL(tblContact_1.FirstName, '')+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
    dbo.tblComResponse.ResponseTxt,
    dbo.tblComResponse.ResID,
    dbo.tblProperty.BuildingID,
    dbo.tblProperty.BldgName,
    dbo.tblComIssue.ContactID,
    dbo.tblProperty.ContactID AS PropMgr,
    dbo.tblComIssue.IssueClosed

    INTO dbo.[##TempForPrint]
    FROM dbo.tblComResponse LEFT OUTER JOIN
    dbo.tblStaff ON dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
    dbo.tblContact tblContact_2 RIGHT OUTER JOIN
    dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
    dbo.tblContact tblContact_1 RIGHT OUTER JOIN
    dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID


    WHERE (dbo.tblComIssue.ContactID LIKE @RCaller) AND (dbo.tblProperty.ContactID LIKE @RPropMgr) AND (dbo.tblComIssue.IssueClosed LIKE @RComStat)
    AND (dbo.tblProperty.OpsMgrID LIKE @ROpsMgr) AND (dbo.tblProperty.BuildingID LIKE @RBID) AND (CONVERT(Varchar(10),
    dbo.tblComIssue.ComDate, 101) LIKE @RDate)
    GO


    ===================================

    On my Access front end I have a Search form where the users selects the parameters he wants to search for and I have a code which passes the parameters to SQL, here is the code:

    ************************************************** *******
    Dim cmd As ADODB.Command
    Dim PVCaller As ADODB.Parameter
    Dim PVBID As ADODB.Parameter
    Dim PVPropMgr As ADODB.Parameter
    Dim PVDate As ADODB.Parameter
    Dim PVComStat As ADODB.Parameter
    Dim PVOpsMgr As ADODB.Parameter

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "SP_Report_ComIssue_TempForPrint"


    Set PVCaller = cmd.CreateParameter("@RCaller", adInteger, adParamInput)
    Set PVBID = cmd.CreateParameter("@RBID", adVarChar, adParamInput, 50)
    Set PVPropMgr = cmd.CreateParameter("@RPropMgr", adInteger, adParamInput)
    Set PVDate = cmd.CreateParameter("@RDate", adDate, adParamInput)
    Set PVComStat = cmd.CreateParameter("@RComStat", adVarChar, adParamInput)
    Set PVOpsMgr = cmd.CreateParameter("@ROpsMgr", adInteger, adParamInput)

    If Me!ChCaller = True Then
    cmd.Parameters.Append PVCaller
    PVCaller.Value = Me!RCaller
    End If

    If Me!ChOpsMgr = True Then
    cmd.Parameters.Append PVOpsMgr
    PVOpsMgr.Value = Me!ROpsMgr
    MsgBox Me!ROpsMgr
    End If


    If Me!ChBID = True Then
    cmd.Parameters.Append PVBID
    PVBID.Value = Me!RBID
    End If

    If Me!ChPropMgr = True Then
    cmd.Parameters.Append PVPropMgr
    PVPropMgr.Value = Me!RPropMgr
    End If

    If Me!ChDate = True Then
    cmd.Parameters.Append PVDate
    PVDate.Value = Me!RDate
    End If

    If Me!ChComStat = True Then
    cmd.Parameters.Append PVComStat
    PVComStat.Value = Me!RComStat
    End If


    cmd.Execute

    ************************************************** *****
    My problem is that:
    The First two parameters are passed correctly to the SQL Server (RCaller and RPropMgr), however for some reason no matter what I do the other 3 parameters are not passed.

    Do you guys have any idea why?

  2. #2
    Join Date
    May 2003
    Posts
    5
    Have you tried this format:

    If Me!ChCaller = True Then
    cmd.Parameters.Append PVCaller
    PVCaller.Value = Me!RCaller
    ElseIf Me!ChOpsMgr = True Then
    cmd.Parameters.Append PVOpsMgr
    PVOpsMgr.Value = Me!ROpsMgr
    MsgBox Me!ROpsMgr
    ElseIf Me!ChBID = True Then
    cmd.Parameters.Append PVBID
    PVBID.Value = Me!RBID
    ElseIf Me!ChPropMgr = True Then
    cmd.Parameters.Append PVPropMgr
    PVPropMgr.Value = Me!RPropMgr
    ElseIf Me!ChDate = True Then
    cmd.Parameters.Append PVDate
    PVDate.Value = Me!RDate
    ElseIf Me!ChComStat = True Then
    cmd.Parameters.Append PVComStat
    PVComStat.Value = Me!RComStat
    Else

    End If


    cmd.Execute

    I have had similar things occur and sometimes just a change of format from individual IF statements to a string of ElseIf statements and an ending Else clause did the trick.

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    But that will only let one parameter passed. I need to be able to pass one or more parameters based on the users prefrenses

Posting Permissions

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