473,748 Members | 2,574 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Check if Stored Procedure already exists in Access Database?

I am successfully adding stored procedures to an Access database. However,
I need to be able to check if the stored procedure of the same name already
exists.

Is there a way to do this other than waiting for the OleDbException caused
when adding one that already exists?

Here is the code snippet:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList

alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS SELECT
* FROM qryResults WHERE ID = inID")

If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If

dbCommand.Conne ction = dbConnection
dbConnection.Op en()

Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

Any help is greatly appreciated.

carl
Nov 21 '05 #1
9 7262
I don't know how you'd do it in Access, but I can show you what the SQL
would look like if you were doing it in SQL Server 2000. From this hint,
maybe you can snoop around in the system tables in the access database and
compose something similar. To check for a stored procedure named, for
instance, my_procedure, we'd do something like this:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[my_procedure]') and OBJECTPROPERTY( id, N'IsProcedure') =
1)

begin

end

GO

"Carl Fenley" <clfenley-X-@-X-wcpci.com> wrote in message
news:eY******** *****@TK2MSFTNG P09.phx.gbl...
I am successfully adding stored procedures to an Access database. However, I need to be able to check if the stored procedure of the same name already exists.

Is there a way to do this other than waiting for the OleDbException caused
when adding one that already exists?

Here is the code snippet:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList

alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS SELECT * FROM qryResults WHERE ID = inID")

If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If

dbCommand.Conne ction = dbConnection
dbConnection.Op en()

Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

Any help is greatly appreciated.

carl

Nov 21 '05 #2
Hi,

List access stored procedures
http://www.windowsformsdatagridhelp....3-528cf4c49e94

Ken
--------------------
"Carl Fenley" <clfenley-X-@-X-wcpci.com> wrote in message
news:eY******** *****@TK2MSFTNG P09.phx.gbl...
I am successfully adding stored procedures to an Access database. However,
I need to be able to check if the stored procedure of the same name already
exists.

Is there a way to do this other than waiting for the OleDbException caused
when adding one that already exists?

Here is the code snippet:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList

alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS SELECT
* FROM qryResults WHERE ID = inID")

If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If

dbCommand.Conne ction = dbConnection
dbConnection.Op en()

Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

Any help is greatly appreciated.

carl

Nov 21 '05 #3
"Carl Fenley" <clfenley-X-@-X-wcpci.com> schrieb
I am successfully adding stored procedures to an Access database. However,
I need to be able to check if the stored procedure of the
same name already exists.


Please note that there is a group for language unrelated but ADO.Net related
questions:
microsoft.publi c.dotnet.framew ork.adonet

Armin

Nov 21 '05 #4
Armin,

Did you see that site of two guys both very active in this newsgroup.

(There is nothing wrong of course telling that there is an AdoNet newsgroup,
than I show our site as well there when there are this kind of questions)

:-)

Cor
Nov 21 '05 #5
"Cor Ligthert" <no************ @planet.nl> schrieb
Armin,

Did you see that site of two guys both very active in this
newsgroup.

(There is nothing wrong of course telling that there is an AdoNet
newsgroup, than I show our site as well there when there are this
kind of questions)

:-)

One hint just like the others.

Armin
Nov 21 '05 #6
On Wed, 29 Jun 2005 12:44:49 -0700, "Carl Fenley" <clfenley-X-@-X-wcpci.com> wrote:

¤ I am successfully adding stored procedures to an Access database. However,
¤ I need to be able to check if the stored procedure of the same name already
¤ exists.
¤
¤ Is there a way to do this other than waiting for the OleDbException caused
¤ when adding one that already exists?

Yes. You can use GetOleDbSchemaT able. I don't recommend using the Access system tables.

Dim DatabaseConnect ion As New System.Data.Ole Db.OleDbConnect ion
Dim SchemaTable As DataTable

DatabaseConnect ion.ConnectionS tring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=E:\My Documents\db1.m db"

DatabaseConnect ion.Open()

'Use one of the two statements below depending upon the type of QueryDef

SchemaTable =
DatabaseConnect ion.GetOleDbSch emaTable(System .Data.OleDb.Ole DbSchemaGuid.Pr ocedures, _
New Object() {Nothing, Nothing, "qupdtUpdateTab le"})

'SchemaTable =
DatabaseConnect ion.GetOleDbSch emaTable(System .Data.OleDb.Ole DbSchemaGuid.Vi ews, _
' New Object() {Nothing, Nothing, "qyrQueryTable" })

If SchemaTable.Row s.Count <> 0 Then
Console.WriteLi ne("Table " & SchemaTable.Row s(0)!NAME.ToStr ing & " Exists")
Else
Console.WriteLi ne("Table does not exist")
End If

DatabaseConnect ion.Close()
Paul
~~~~
Microsoft MVP (Visual Basic)
Nov 21 '05 #7
On Wed, 29 Jun 2005 22:02:50 -0400, Ken Tucker [MVP] wrote:
Hi,

List access stored procedures
http://www.windowsformsdatagridhelp....3-528cf4c49e94

Ken


Site looks pretty bad in Firefox, if you care
Nov 21 '05 #8
Ross,

Because that it is a Net development side is every non IE compatible part
even deleted.

However there is not so much JavaScript in it, so we can think about
including it again.

Cor
Nov 21 '05 #9
Ross,

I forgot it almost, thanks for making us attent on this.

Cor
Nov 21 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
24085
by: geoff | last post by:
Is there a stored procedure installed by sql server 2000 that I can call and just pass in the name of a new database and have it create the database for me? If not, how do I do it in sql? Thanks.
4
3859
by: Jim in Arizona | last post by:
I'm wanting to do a simple controlled voting page. I too our webserver off anonymous and everyone who accesses the website is a domain authenticated user. I've already done some control structure pages based on Request.ServerVariables("AUTH_USER"), which works great. That's also how I would do this page, in my basic thinking. My idea is to have an access database with two tables. One table will have the vote written to it and the other...
6
9905
by: Peter Neumaier | last post by:
Hi, I am trying to select some data through a stored procedure and would like to store the result in a local access table. Is that possible? Can somebody provide an example? Thanks&regards! Peter
3
11047
by: byeung | last post by:
Hi, I am trying to check if a particular record already exists in an Access database through Excel vba code. Through code obtained at another forum, I got the following: *********************************************************************** Sub TheButton()
0
1170
by: Nitin Kshirsagar | last post by:
how to use stored procedure in ms-access through the vb6.0?
5
5998
by: ashurack | last post by:
I found a stored procedure online a while back and want to inplement it. The only problem is that it doesn't check to see if the number generated is currently in use in the DB. I know it's really unlikely for the same number to be generated twice (with a 10 digit number) but it's for a shopping cart so I need it to be 100% reliable. I thought about opening a cursor then looping through each distinct record in the DB but couldn't figure...
2
14659
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how can I pass null value as parameter to the database stored procedure programattically using C#? Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value...
3
7865
by: rash123 | last post by:
Hi all , Please help me on the the following query How can we Check if Oracle stored procedure already running
0
1190
by: perdijc | last post by:
The wizard to create dataset, automatically creates a method to update table if the dataset is based on table. If i create a dataset based on sotored procedure where this is based on more one table, it doesn´t create the update method. Any bodyelse, knows any wizard or addin where i can create update method based on stored procedure? Im ms access project if I bound a form to stored procedure, in the properties form, I have option to...
0
8996
marktang
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...
0
8832
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9386
jinu1996
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...
1
9333
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,...
0
6078
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4879
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3319
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
2
2791
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2217
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.