OLE DB stored procedure problem

Discussion in 'ASP General' started by sbw@sarinarusso.com.au, May 17, 2005.

  1. Guest

    Hi.

    I have an app which uses multiple databases and I would like to know if
    there is a way to connect to different databases through OLEdb (or any
    other connection string) which allows me to connect to a different
    database.

    My connection string connects to HelpDesk db.

    For example
    ..ActiveConnection = xconn
    ..CommandType = adCmdStoredProc
    ..CommandText = "HR.dbo.proc_Get_Region"
    ..Parameters.Refresh
    ..Parameters("@iRegionID") = 0
    ..Parameters("@iUserID") = userid
    set rsregion = .Execute

    Using this syntax, I get the following error.
    Procedure 'proc_Get_Region' expects parameter '@iRegionID', which was
    not supplied.

    Stored Procedure syntax is:
    CREATE Procedure proc_Get_Region
    (
    @iRegionID int = 0,
    @iUserID int
    )
    AS
    BEGIN

    SELECT iRegionID, tRegionDesc tName, tOpsMngr, iStaffID, tOpsMngr,
    tFirstName OpsManFName, tSurname OpsManSName, tEmail OpsManEmail,
    FROM HR.dbo.tblRegion R
    INNER JOIN HR.dbo.tblStaff S ON S.tStaffID = R.tOpsMngr
    WHERE iRegionID = @iRegionID
    ORDER BY tName

    -- I use the @iUserid variable elsewherr

    END
    GO

    Let me give you some background. I want to do this so that I can
    re-use my hr.dbo.<storedprocedures>. As you can understand, if I
    create helpdesk.dbo.<storedprocedures>, I would have to create these
    same sp's in every other db that connects to the HR db. However, if I
    can re-use the hr.dbo.<storedprocedures> I only need to edit them in
    one place and all others are updated also.

    Any help would be greatly appreciated.

    PS Permissions are fine. The same db login has permissions to both
    databases.
    , May 17, 2005
    #1
    1. Advertising

  2. wrote:
    > Hi.
    >
    > I have an app which uses multiple databases and I would like to know
    > if there is a way to connect to different databases through OLEdb (or
    > any other connection string) which allows me to connect to a different
    > database.
    >


    The Connection object has a property called DefaultDatabase
    (http://msdn.microsoft.com/library/en-us/ado270/htm/mdprodefaultdatabase.asp)
    which allows you to basically switch databases after the connection has been
    opened.


    > My connection string connects to HelpDesk db.
    >
    > For example
    > .ActiveConnection = xconn
    > .CommandType = adCmdStoredProc
    > .CommandText = "HR.dbo.proc_Get_Region"
    > .Parameters.Refresh


    BAD BAD BAD
    You are forcing a separate trip to the database to be made in order to
    retrieve the parameter metadata. You should use CreateParameter to create
    your parameters and append them to the Parameters collection. I wrote a free
    tool to generate this code for you - it's available here:
    http://www.thrasherwebdesign.com/downloads1/sp_parameters.zip. The
    description is here:
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear


    > .Parameters("@iRegionID") = 0
    > .Parameters("@iUserID") = userid
    > set rsregion = .Execute
    >
    > Using this syntax, I get the following error.
    > Procedure 'proc_Get_Region' expects parameter '@iRegionID', which was
    > not supplied.
    >
    > Stored Procedure syntax is:
    > CREATE Procedure proc_Get_Region
    > (
    > @iRegionID int = 0,
    > @iUserID int
    > )


    Hmm - no output parameters, and you don't seem to need to read the Return
    parameter ... you don't need to use an explicit Command object. You can do
    this:

    xconn.DefaultDatabase = "HR"
    set rsregion=createobject("adodb.recordset")
    xconn.proc_Get_Region 0, userid, rsregion
    if not rsregion.eof then
    'process the recordset
    else
    'handle the empty-recordset situation
    end if

    Don't forget to close and destroy your recordset and connection objects ...

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], May 17, 2005
    #2
    1. Advertising

  3. Guest

    Brilliant. Thanks very much for the info.

    I will make sure my stored procs are fashioned according to your
    suggestion in future.
    , May 17, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike P
    Replies:
    0
    Views:
    3,267
    Mike P
    Jun 19, 2006
  2. Patrick.O.Ige

    Ole ole

    Patrick.O.Ige, Jul 16, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    468
    Patrick.O.Ige
    Jul 16, 2006
  3. Drew Pihera
    Replies:
    0
    Views:
    614
    Drew Pihera
    Feb 4, 2004
  4. Replies:
    0
    Views:
    107
  5. Lance Hoffmeyer
    Replies:
    0
    Views:
    220
    Lance Hoffmeyer
    Nov 17, 2003
Loading...

Share This Page