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 CreateStoredProcedures()
   Dim alSql As New ArrayList

   alSql.Add("CREATE PROC usp_ProjectResultsByID(inID VARCHAR(50)) AS SELECT
* FROM qryResults WHERE ID = inID")

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

   dbCommand.Connection = dbConnection
   dbConnection.Open()

   Dim i As Integer
   For i = 0 To alSql.Count - 1
      dbCommand.CommandText = DirectCast(alSql(i), String)
      dbCommand.ExecuteNonQuery()
   Next
   dbConnection.Close()
End Sub

Any help is greatly appreciated.

carl

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





Show quoteHide quote

Hi,

List access stored procedures
http://www.windowsformsdatagridhelp.info/default.aspx?ID=9c2ca38c-92c1-410f-b753-528cf4c49e94

Ken
--------------------
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 CreateStoredProcedures()
   Dim alSql As New ArrayList

   alSql.Add("CREATE PROC usp_ProjectResultsByID(inID VARCHAR(50)) AS SELECT
* FROM qryResults WHERE ID = inID")

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

   dbCommand.Connection = dbConnection
   dbConnection.Open()

   Dim i As Integer
   For i = 0 To alSql.Count - 1
      dbCommand.CommandText = DirectCast(alSql(i), String)
      dbCommand.ExecuteNonQuery()
   Next
   dbConnection.Close()
End Sub

Any help is greatly appreciated.

carl

On Wed, 29 Jun 2005 22:02:50 -0400, Ken Tucker [MVP] wrote:

Site looks pretty bad in Firefox, if you care

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

Ross,

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

Cor

Please note that there is a group for language unrelated but ADO.Net related
questions:
microsoft.public.dotnet.framework.adonet

Armin

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

One hint just like the others.

Armin

¤ 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 GetOleDbSchemaTable. I don't recommend using the Access system tables.

        Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
        Dim SchemaTable As DataTable

        DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=E:\My Documents\db1.mdb"

        DatabaseConnection.Open()

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

        SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Procedures, _
                      New Object() {Nothing, Nothing, "qupdtUpdateTable"})

        'SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Views, _
        '              New Object() {Nothing, Nothing, "qyrQueryTable"})

        If SchemaTable.Rows.Count <> 0 Then
            Console.WriteLine("Table " & SchemaTable.Rows(0)!NAME.ToString & " Exists")
        Else
            Console.WriteLine("Table does not exist")
        End If

        DatabaseConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)

"Selecting" from a data table
Strangest damn error - database at fault?
CRC checksum algorithm
2 different numbers.....
Directinput
Dir Function just stopped working!
Closing event
Finding a webservice server
persistent data source (light footprint, secure, supports sql query engine)
weird problem with XMLSerializer