Solved

HAving Problems with Stored Procedure and Output Value

Posted on 2004-04-30
8
573 Views
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.
            )

As

-- 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
        objCn.Close
        Set objCn = Nothing
        Set objCmd = Nothing
       
98    MsgBox " Count: " & Count

Suggestions, comments, punt?

Thanks...  Brent





0
Comment
Question by:bjwebb556
[X]
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
8 Comments
 
LVL 50

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.
   

0
 

Author Comment

by:bjwebb556
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.
0
 
LVL 50

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
    rs.Close
   
    ' 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
0
Technology Partners: 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

by:bjwebb556
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.
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 50 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.
0
 

Author Comment

by:bjwebb556
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?

0
 

Author Comment

by:bjwebb556
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?
0
 
LVL 50

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;" & _
           "Uid=myUsername;Pwd=myPassword")

' 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)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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 specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 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