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. Advertisements

  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. Advertisements

  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. Advertisements

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. Leon Shaw

    Stored Procedure Problem

    Leon Shaw, Jul 28, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    454
    Jurjen de Groot
    Jul 29, 2003
  2. ElmoWatson

    Stored Procedure/Parameter problem

    ElmoWatson, Aug 4, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    601
    Jerry
    Aug 6, 2003
  3. Bilbo
    Replies:
    3
    Views:
    570
    Bilbo
    Nov 20, 2003
  4. The Clansman

    Stored Procedure problem

    The Clansman, May 19, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    441
    Julie
    May 19, 2004
  5. Mike P
    Replies:
    0
    Views:
    3,619
    Mike P
    Jun 19, 2006
  6. Patrick.O.Ige

    Ole ole

    Patrick.O.Ige, Jul 16, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    702
    Patrick.O.Ige
    Jul 16, 2006
  7. Drew Pihera
    Replies:
    0
    Views:
    912
    Drew Pihera
    Feb 4, 2004
  8. Lance Hoffmeyer
    Replies:
    0
    Views:
    536
    Lance Hoffmeyer
    Nov 17, 2003
Loading...