HAving Problems with Stored Procedure and Output Value

Posted on 2004-04-30
Medium Priority
Last Modified: 2008-03-17
Need another set of eyes.  Trying to pass a single parameter to a stored procedure and get an output value to a VB code.  The procedure counts all occurences of equipment belonging to a repair center.  Currently I nothing returned.

Stored Procedure:

Alter Procedure SP_PM_ALL

                        @RC char(4), -- This is the input parameter.
                        @Count int output  -- This is the output parameter.


-- Get the priority, scheduled, and completion dates and
-- assign it to the output parameter.

SELECT  @Count = count (dbo.f_Equipment.FM_RISK_FACT)

FROM dbo.f_Equipment

WHERE (dbo.f_Equipment.FM_RC_CODE = @RC)

return @Count

My VB code is:

    Dim strRC As String
    Dim Priority As Double
    Dim Count As Integer
    Dim objCmd As New ADODB.Command
    Dim objCn  As ADODB.Connection
    Dim objRs As ADODB.Recordset

'Populate Date Range and Repair Center From Switchboard Form
    BeginDate = Forms![F_Switchboard]![Month]
    EndDate = Forms![F_Switchboard]![Years]
    RC = Forms![F_Switchboard]![RC]
    Set dbs = CurrentDb
'Count All Equipment
    Count = 0

  'Returns Total of All Records
    'Set the Command object properties
      With objCmd
        .CommandText = "SP_PM_ALL"
        .CommandType = adCmdStoredProc
        Set .ActiveConnection = objCn
        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
        .Parameters.Append .CreateParameter("RC", adVarChar, adParamInput, 4, strRC)

        .Parameters.Append .CreateParameter("Count", adInteger, adParamOutput)
    End With
    With objRs
        .CursorLocation = adUseClient
        .Open objCmd, , adOpenDynamic, adLockReadOnly
        Set .ActiveConnection = Nothing
    End With
    'Execute the Command object
        Exec_SP_PM_All = objCmd.Parameters("RETURN_VALUE")
    'Dereference the database objects
        Set objCn = Nothing
        Set objCmd = Nothing
98    MsgBox " Count: " & Count

Suggestions, comments, punt?

Thanks...  Brent

Question by:bjwebb556
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 51

Expert Comment

by:Steve Bink
ID: 10962448
I don't know alot about your project, but is there a reason you want to send such a simple task over to a stored procedure?  Why not build the Count SQL in VB?  

       .CommandText = "SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount " & _
                                 "FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = '" & strRC & "'"
        .CommandType = adCmdText
        etc., etc.


Author Comment

ID: 10963656
I am so new to this that I don't even know that I don't know.

I was planning on building even more complex queries later, however, this looks like somethin I might handle.

Q1: Is there a good reference.

Q2: How do I call the query, it looks incomplete.

Q3: What variable contains the value or how do I get it into a value that I can use in VB.  Is this the variable MyCount?  Me!CI1 = MyCount

Q4: I suspect I need to declare MyCount as well.
LVL 51

Expert Comment

by:Steve Bink
ID: 10963996
A1:  I have a great reference called "Beginning Access 2002 VBA" from Wrox Publishing.  They make many other books in the series, and all the texts I've seen from them have been outstanding for beginners and techies alike.  Downside?  It doesn't explain ADO...in fact, it hardly even mentions it.  Not a big problem unless you are forced to use ADO.

A2-A4: I don't normally program using ADO in Access (or anything else, really), so I can't be of much help there unless I sat down and tested some code myself.  For DAO, however, here's some sample code that should do what you need.  If you prefer to use ADO, tell me and I'll take the time to convert this to what you need.  I am assuming you are connecting to currently open database.  If you need to connect to an external database, I will need to know the type of database and where it is physically located.

Public Function GetRecordCount as Integer
' I declared a function because you seem to need the count value.  If you don't need the return value outside of
' of this function, you can change this to a sub.

' Declare the variables.  Note I changed Count to MyCount.  strRC is unnecessary.
    Dim strRC As String
    Dim MyCount As Integer
    Dim db as DAO.Database
    Dim rs as DAO.Recordset

    ' Instead of using strRC, I'll insert the control's value directly into the SQL.
    ' strRC = Forms![F_Switchboard]![RC]
    ' Initialize MyCount, set up our database object and open the recordset.
    MyCount = 0
    Set db = CurrentDb
    set rs = db.OpenRecordset("SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount " & _
                  "FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = '" & _
                   Forms![F_Switchboard]![RC] & "'")

    ' The recordset now hold one field in one record.  Grab the count, then close everything down.
    MyCount = rs!MyCount
    ' This is where you could continue processing.  What do you want to do with the MyCount value?  Put it here!
    ' Otherwise, return the value, and ALWAYS clean up after yourself.  You return the value by:
    ' <FunctionName> = <Value>

    set rs = Nothing
    set db = Nothing
    GetRecordCount = MyCount

End Function

You would use this function like this:

Public Sub Example()
Dim x

x = GetRecordCount
msgbox "There are " & x & " records."

End Sub
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 10965573
I attempted to incorporate the code.  I get the following message:

There are  records.

Is it not finding the data or is the select not passing the correct values.
LVL 51

Accepted Solution

Steve Bink earned 150 total points
ID: 10967830
Where is your table dbo.f_Equipment?  Is the field FM_RC_CODE a number or string data type?  Since we're not using strRC, we can use it for some debugging.  Make this change:

    MyCount = 0
    set db = CurrentDB
    strRC = "SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount " & _
                "FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = '" & _
                 Forms![F_Switchboard]![RC] & "'"
    Debug.Print strRC
    set rs = db.OpenRecordset(strRC)

As it runs, it will print out the SQL for this query to the Immediate window.  You can view this window by pressing CTRL-G or selecting "View | Immediate Window" from the menu bar.  Post the result here, and try pasting it in the query builder to see what records Access is returning.

Author Comment

ID: 10968011
I appreciate the help

This is a adp project (MS Access frontend, SQL Backend) with the database located on a SQL Server. (I am trying to debug with a database located on my home machine, I have loaded a Microsoft product  to have it act like a server.  

The final project will reside on several individual machines and access the TMA satabase on the TMAServer\shstma.  THis will be a custom report writer tool (readonly).

I have several repair centers "RC" char(4)

SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = 'SLCH - PE'

Looks like it is passing the variable correctly.

Would it be smart to verify the table connection or will we get an error message.

Hmmm... can we modify this to print to the debug window dbo.f_Equipment.FM_RISK_FACT values?


Author Comment

ID: 10969954
Hmmm...  I used the debugger to report the db value.  It is reports "nothing" at the time of the Select call.  Could it be that this value is not being passed?
LVL 51

Expert Comment

by:Steve Bink
ID: 10978089
The table you are reading from is in a different database, so now we have to build a connection to it.  From here you have two options, linking and connection through code.

By linking, I mean simply linking the tables to the remote database.  That means you'll let Access handle the connection state and you just query the local link for the table, which then gets passed on to the server in which the real table resides.  It is fairly simple to do through the [ File | Get External Data... ] wizard from the menu bar.  Downside is the users can screw you up at this point.  If they mess with the linked tables, well...let's just say things can  go downhill very quickly

If you want to build the connection through code, that's pretty easy too.  Again, there are a few ways to accomplish this, and this is but one of them.  Since you will have multiple client machines with a single server, I'm showing an example for an ODBC connection.  It's been awhile since I've done this, so this may need some editing/fine tuning.  This becomes easier if you wanted to create a DSN on each client, but that is an unnecessary step, and can be pretty tiresome with many clients.

' Declare the local variables
Dim cn as DAO.Connection
Dim ws as DAO.Workspace
Dim rs as DAO.Recordset

' Create a workspace using ODBC.  The workspace name is "MyODBCWorkspace".
' It is being created by the username "admin" with a blank password.  Change this if necessary.
' You can change adUseODBC to adUseJet if you prefer to work with Jet instead.

set ws = DBEngine.CreateWorkspace("MyODBCWorkspace","admin","",adUseODBC)
' The next step is not necessary, but can be helpful in working with the workspace
DBEngine.Workspaces.Append ws

' Now open the connection.  "Conn1" will be the name of the connection.  dbDriverNoPrompt tells
' Windows to generate an error if the connection string is wrong (as opposed to popping up the
' drive window.  The missing parameter is for read-only or shared access, defaulting to shared.
' Last is the ODBC connection string...you will need to edit this to use your own information.
set cn = ws.OpenConnection("Conn1", dbDriverNoPrompt, , _
            "ODBC;Driver={SQL Server};Server=MyServerName;Database=myDatabaseName;" & _

' Now we can open the recordset!  This part should look familiar.  :)
strRC = "SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount " & _
                "FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = '" & _
                 Forms![F_Switchboard]![RC] & "'"
Debug.Print strRC
set rs = cn.OpenRecordset(strRC)

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question