Help with executing access query

[Guest]

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

 

[Paul Clement]

¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)