Solved

HAving Problems with Stored Procedure and Output Value

Posted on 2004-04-30
8
558 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
  • 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now