473,326 Members | 2,125 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Help with executing access query

I have written 6 Queries in an Access DB, which are executed in a For
Each…Next loop to populate DataSet object. A new DataTable object is created
in DataSet for each query, as follows

Private Sub GetRawLiRatios()
Dim cnn As OleDbConnection
Dim cmd As New OleDbCommand
Dim dad As OleDbDataAdapter
Dim dstRawFigures As New DataSet

Dim arrRatios As String() = [Enum].GetNames(GetType(LiRatios))
Dim obj As New DataStructureConverter

Try
'! Initialize OleDbConnection object.
cnn = CreateConnection()

'! Set connection property for OleDbCommand object.
cmd.Connection = cnn

'! Add parameters to the paramter collection.
cmd.Parameters.Add("IndustryName", cboIndustry.Text)
cmd.Parameters.Add("Year", nudYear.Value)

'! Set the command type property of the command object.
cmd.CommandType = CommandType.StoredProcedure

For Each ratio As String In arrRatios
'! Set the command text property of the command object
'! to the name of the query.
cmd.CommandText = ratio

'! Initialize data adapter object with command object.
dad = New OleDbDataAdapter(cmd)

'! Fill the dataset.
dad.Fill(dstRawFigures, ratio)

Next ratio

Catch ioExcep As IO.IOException
MessageBox.Show(ioExcep.Message, Application.ProductName,
MessageBoxButtons.OK, MessageBoxIcon.Error)

Catch ex As Exception
Call HandleException(Me.Name, ex, "GetRawLiRatios")
End Try

End Sub

Where arrRatios is an Enumeration which contains name of the queries in
access db. If any of the queries get deleted from the access db, For
Each...Next loop is exited and exception is thrown.

Now I want my For Each...Next loop to continue processing for reaming
queries in the db, rather than throwing an exception. How can I check whether
queries exists in the db before using Fill method?

Thanks

Jul 21 '05 #1
1 1631
On Mon, 10 Jan 2005 23:11:01 -0800, "Job Lot" <Jo****@discussions.microsoft.com> wrote:

¤ I have written 6 Queries in an Access DB, which are executed in a For
¤ Each…Next loop to populate DataSet object. A new DataTable object is created
¤ in DataSet for each query, as follows
¤
¤ Private Sub GetRawLiRatios()
¤ Dim cnn As OleDbConnection
¤ Dim cmd As New OleDbCommand
¤ Dim dad As OleDbDataAdapter
¤ Dim dstRawFigures As New DataSet
¤
¤ Dim arrRatios As String() = [Enum].GetNames(GetType(LiRatios))
¤ Dim obj As New DataStructureConverter
¤
¤ Try
¤ '! Initialize OleDbConnection object.
¤ cnn = CreateConnection()
¤
¤ '! Set connection property for OleDbCommand object.
¤ cmd.Connection = cnn
¤
¤ '! Add parameters to the paramter collection.
¤ cmd.Parameters.Add("IndustryName", cboIndustry.Text)
¤ cmd.Parameters.Add("Year", nudYear.Value)
¤
¤ '! Set the command type property of the command object.
¤ cmd.CommandType = CommandType.StoredProcedure
¤
¤ For Each ratio As String In arrRatios
¤ '! Set the command text property of the command object
¤ '! to the name of the query.
¤ cmd.CommandText = ratio
¤
¤ '! Initialize data adapter object with command object.
¤ dad = New OleDbDataAdapter(cmd)
¤
¤ '! Fill the dataset.
¤ dad.Fill(dstRawFigures, ratio)
¤
¤ Next ratio
¤
¤ Catch ioExcep As IO.IOException
¤ MessageBox.Show(ioExcep.Message, Application.ProductName,
¤ MessageBoxButtons.OK, MessageBoxIcon.Error)
¤
¤ Catch ex As Exception
¤ Call HandleException(Me.Name, ex, "GetRawLiRatios")
¤ End Try
¤
¤ End Sub
¤
¤ Where arrRatios is an Enumeration which contains name of the queries in
¤ access db. If any of the queries get deleted from the access db, For
¤ Each...Next loop is exited and exception is thrown.
¤
¤ Now I want my For Each...Next loop to continue processing for reaming
¤ queries in the db, rather than throwing an exception. How can I check whether
¤ queries exists in the db before using Fill method?
¤
¤ Thanks

You should be able to use the GetOleDbSchemaTable method of the OleDbConnection object. Can't recall
exactly what the difference is but check for Procedures and Views:

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()

'Retrieve schema information about Catalog.

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

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

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

'displays the properties
DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Jul 21 '05 #2

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

Similar topics

3
by: Neil Hindry | last post by:
I wonder if you can help me. I have setup an address-book database in Access XP. I have the first name & surname as separate fields. As I wanted to sort my database by surname and then by first...
5
by: Art | last post by:
Hi, Can anyone point me to an example of how I would execute a query I've created in an Access DB. I've copied the SQL down to my VB.net application and that works fine, but it's ugly. I'd like...
1
by: Job Lot | last post by:
I have written 6 Queries in an Access DB, which are executed in a For Each…Next loop to populate DataSet object. A new DataTable object is created in DataSet for each query, as follows Private...
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
8
by: daD | last post by:
I'm trying to write a small database that tracks people coming and going from a small campground. I need to have the current guests in the "current" table" and then have the ability to check them...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
8
by: SAL | last post by:
Hello, Using the designer in my project, I created a DataTable for one of the tables in an Access database. I created a TableAdapter for that DataTable by adding queries. For the most part, the...
1
by: jesmi | last post by:
my code is: U]employee.cfm <html> <head> <title>Employee List</title> </head> <body> <h1>Employee List</h1>
1
by: contactrajib | last post by:
I have a situation where one of the app server thread is executing an update statement on A record and at the same time another thread is trying to read it by executing a select query. Now we are...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.