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
..ActiveConnect ion = xconn
..CommandType = adCmdStoredProc
..CommandText = "HR.dbo.proc_Ge t_Region"
..Parameters.Re fresh
..Parameters("@ iRegionID") = 0
..Parameters("@ iUserID") = userid
set rsregion = .Execute
Using this syntax, I get the following error.
Procedure 'proc_Get_Regio n' 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.tblRegio n 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.<storedp rocedures>. As you can understand, if I
create helpdesk.dbo.<s toredprocedures >, 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.<storedp rocedures> 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. 2 1511 sb*@sarinarusso .com.au 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...ltdatabase.asp)
which allows you to basically switch databases after the connection has been
opened.
My connection string connects to HelpDesk db.
For example .ActiveConnecti on = xconn .CommandType = adCmdStoredProc .CommandText = "HR.dbo.proc_Ge t_Region" .Parameters.Ref resh
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/dow...parameters.zip. The
description is here: http://www.thrasherwebdesign.com/ind...asp&c=&a=clear
.Parameters("@i RegionID") = 0 .Parameters("@i UserID") = userid set rsregion = .Execute
Using this syntax, I get the following error. Procedure 'proc_Get_Regio n' 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.DefaultDa tabase = "HR"
set rsregion=create object("adodb.r ecordset")
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"
Brilliant. Thanks very much for the info.
I will make sure my stored procs are fashioned according to your
suggestion in future. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Michael Trosen |
last post by:
Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the cursor is defined as: SQL_CURSOR delpt_cursor
|
by: Rhino |
last post by:
I've spent the last couple of hours trying to figure out how to debug a Java
stored procedure and am just going in circles. The last straw came when I
got "Cannot open input stream for default" when I launched the IBM
Distributed Debugger via
D:\IBMDebug>idebug.exe -qdaemon -quiport=8000,8001
First, a bit of background.
I am running DB2 V7.2 with Fixpack 9 applied on Windows XP Professional (all
critical service applied). I've written...
|
by: Jeff Wang |
last post by:
Hi all,
Can someone help me out? I've been struggling with this for almost a
week and still have no clue what's wrong.
Basically I want to write a DB2 stored procedure for OS/390 in REXX.
In this procedure it reads a dataset and return the first line of the
dataset. I met two problems:
Problem 1:
|
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;
works from a query tool.
|
by: Kent Lewandowski |
last post by:
hi all,
Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered problems doing this.
I wanted to implemented a generic "Helper" class like this:
/**
* Helper
| |
by: Wojciech Wendrychowicz |
last post by:
Hello to All,
I'm trying to retrieve records from AS/400 in an VBA application.
So, I've made an RPG program, then a stored procedure wchich calls that RPG
program, and finally some VBA code to call the stored procedure and retrieve
data from AS/400.
The problem is, that when I finally run my VB code, it just hangs.
But when I call the same stored procedure from "pure" SQL - it works
perfect. (I evaluate Aqua Data Studio 3.7)
What I...
|
by: Eli |
last post by:
Hi all
We currently have a strange problem with calling a Stored Procedure
(SQL Database) in our C# Project. The only error I get is "System
error" which says a lot :)
Background:
We have several stored procedures to Insert and update datas in our
SQL database. Some stored procedures are smaller (insert datas in only
one table) and some of them are quite big (insert datas in several
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
expects parameter '@EMail', which was not supplied.
The field value was null in the database and not changed in the FormView so
is null going back into the stored procedure. I'm stumped and would greatly
appreciate any suggestions.
|
by: nishi57 |
last post by:
I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine but when he
runs the stored procedure, he gets the following error message.
"SYSPROC".CSGCSB54 - Run started.
Data returned in result sets is limited to the first 100 rows.
Data returned in result set columns is limited to the first 20...
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table.
When calling the stored procedure from VB.NET, in the CommandText, can I
just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with
"INSERT_INTO_MYTABLE" then do the following :
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |