Wrox Programmer Forums
BOOK: Beginning Access 2007 VBA ISBN: 978-0-470-04684-5
This is the forum to discuss the Wrox book Beginning Access 2007 VBA by Denise M. Gosnell; ISBN: 9780470046845
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Access 2007 VBA ISBN: 978-0-470-04684-5 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 30th, 2011, 10:10 PM
Registered User
Join Date: Dec 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem passing parameters to stored procedure

I have been trying everything I can think of to get this to work and have been unable to do so, therefore, I through myself on the mercy of the forum! I have been given a project due to my familiarity with SQL and dBase/Clipper programming years ago. An example of what I am trying to do follows.

I have a main form where I have text boxes to collect the invoice number and date range to be used throughout the application which performs various checks on the current month invoice. One of the tests is to produce a pivot table of hours by type of job for each employee for the month. I have this listed by week in a SQL view and then have created a stored procedure to produce the monthly result. The stored procedure is the recordsource for the report where I add totals by type of labor, job, and grand totals.

The following code was added to the On Click event of a command button to produce the report. The main form is a tabbed form with the first tab being where I collect the variables which are global. Other tabs are where I perform the calculations by labor types.

    Dim cmdCommand As ADODB.Command
    Dim prmStart As ADODB.Parameter
    Dim prmEnd As ADODB.Parameter
    Dim dtStart As Date
    Dim dtEnd As Date
    dtStart = Me.txtInvBegDt
    dtEnd = Me.txt_InvEndDt
    Set cmdCommand = New ADODB.Command
    cmdCommand.ActiveConnection = CurrentProject.Connection
    cmdCommand.CommandType = adCmdStoredProc
    cmdCommand.CommandText = "usp_AH_ByJob_Pivot"
    Set prmStart = cmdCommand.CreateParameter("@BegDt", adDate, adParamInput)
    Set prmEnd = cmdCommand.CreateParameter("@EndDt", adDate, adParamInput)
    prmStart.Value = dtStart
    prmEnd.Value = dtEnd
    cmdCommand.Parameters.Append prmStart
    cmdCommand.Parameters.Append prmEnd
There may be something basic which I am missing here, but I have stepped through the code and it seems the parameter is being passed but the sproc doesn't seem to be running. Since it is the recordsource for the report, should I try opening the report instead? I am a little confused by some of the new object oriented programming. I have many of the SQL queries written but need to get this date logic working to make an efficient application for the users. I appreciate any assistance that any of you may provide. Thank you in advance! And, happy new year!

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedure Parameters Blank brettdalldorf Crystal Reports 0 September 20th, 2007 02:17 AM
passing parameters to stored procedure in crystal prankur ASP.NET 1.0 and 1.1 Basics 0 January 10th, 2006 03:29 AM
Passing parameters to a sql stored procedure digby_dog VB.NET 2002/2003 Basics 0 July 11th, 2005 08:35 AM
stored procedure with parameters Danmalam VB Databases Basics 0 February 27th, 2005 03:52 PM
Passing Parameters >32k to Oracle stored procedure gp_mk ADO.NET 2 December 22nd, 2003 12:49 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.