Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Querying Access database in VB.Net

Posted on 2013-02-03
11
Medium Priority
?
625 Views
Last Modified: 2013-02-05
I have the following code:
Try
  dbMaintPortable = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & portable_maintenance_db)
Catch ex As Exception
  Try
    dbMaintPortable = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & portable_maintenance_db)
  Catch ex2 As Exception
    txtProcess.Text = txtProcess.Text & vbCrLf & "Could not open:" & vbCrLf & vbCrLf & portable_maintenance_db & vbCrLf & vbCrLf & ex.Message
    MsgBox("Could not open:" & vbCrLf & vbCrLf & portable_maintenance_db & vbCrLf & vbCrLf & ex.Message)
    Exit Sub
  End Try
End Try
dbMaintPortable.Open()

Dim daWorkRS1 As OleDb.OleDbDataAdapter
Dim dsWorkRS1 As DataSet

For x As Integer = 1 To chkboxCtrlArray.Length - 1
  module_prefix = Strings.Left(chkboxCtrlArray(x).Text, 2)

  last_sync_date = CDate("1/1/1990 1:00:00 AM")
  makeNewSyncDate = False
  updateSyncDate = False

  sqlStr = "Select * from SYSTEM_SYNC_DATE Where MODULE_CODE=""" & module_prefix & """ "
  txtProcess.Text = txtProcess.Text & vbCrLf & sqlStr
  daWorkRS1 = New OleDb.OleDbDataAdapter(sqlStr, dbMaintPortable)
  dsWorkRS1 = New DataSet("SYS_SYNC_DATE_" & module_prefix)

  daWorkRS1.Fill(dsWorkRS1)

  If (dsWorkRS1.Tables.Count > 0) Then
    If (dsWorkRS1.Tables(0).Rows.Count > 0) Then
      Dim drRow As DataRow = dsWorkRS1.Tables(0).Rows(0)
      If (IsDBNull(drRow("SYNC_DATE"))) Then
        updateSyncDate = True
        txtProcess.Text = txtProcess.Text & vbCrLf & module_prefix & " update"
      Else
        last_sync_date = drRow("SYNC_DATE")
        txtProcess.Text = txtProcess.Text & vbCrLf & module_prefix & " " & last_sync_date
      End If
    Else
      makeNewSyncDate = True
      txtProcess.Text = txtProcess.Text & vbCrLf & module_prefix & " makeNewSyncDate"
    End If
  End If

  If (updateSyncDate) Then
    txtProcess.Text = txtProcess.Text & vbCrLf & "update"
    sqlStr = "Update SYSTEM_SYNC_DATE Set SYNC_DATE = CDate(""" & last_sync_date & """) Where MODULE_CODE = """ & module_prefix & """ "

    sync_query = New OleDb.OleDbCommand
    sync_query.Connection = dbMaintPortable
    sync_query.CommandType = CommandType.Text
    sync_query.CommandText = sqlStr
    sync_query.ExecuteNonQuery()
  End If

  If (makeNewSyncDate) Then
    txtProcess.Text = txtProcess.Text & vbCrLf & "insert"
    sqlStr = "Insert Into SYSTEM_SYNC_DATE (MODULE_CODE, SYNC_DATE) Values (""" & module_prefix & """, CDate(""" & last_sync_date & """))"

    sync_query = New OleDb.OleDbCommand
    sync_query.Connection = dbMaintPortable
    sync_query.CommandType = CommandType.Text
    sync_query.CommandText = sqlStr
    sync_query.ExecuteNonQuery()
  End If
Next
dbMaintPortable.Close()

Open in new window


SYSTEM_SYNC_DATE definitely has
MODULE_PREFIX  |  SYNC_DATE
-------------------------------------------------------------
AR                        |  1/1/1990 1:01:00 AM
BD                       |  1/1/1990 1:02:00 AM
CM                       |  1/1/1990 1:03:00 AM

The output to txtProcess is:
->Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="AR"
->AR 1/1/1990 1:01:00 AM
->Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="BD"
->BD makeNewSyncDate
->insert

Then an error occurs saying that this will create a duplicate on the index.
If I take out the insert and update commands, the output to txtProcess is:
->Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="AR"
->AR 1/1/1990 1:01:00 AM
->Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="BD"
->BD makeNewSyncDate
->insert
->Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="CM"
->CM 1/1/1990 1:03:00 AM

The row for BD should be found.

The strange thing is, if I put in a msgbox() somewhere into the loop to slow it down it finds all three.
->Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="AR"
->AR 1/1/1990 1:01:00 AM
->Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="BD"
->BD 1/1/1990 1:02:00 AM
->Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="CM"
->CM 1/1/1990 1:03:00 AM

Please any advice would be greatly appreciated!!!
Kind regards

In addition: I have found that the record "not found" is the last record in the table. If that helps. It is a MS Access DB
0
Comment
Question by:AssetFX
11 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 38851533
There is a lot of code there, so it is difficult to get an understanding of your problem.  Where is the SQL that is executing to cause that problem?
0
 

Author Comment

by:AssetFX
ID: 38853486
Hi, yes I was a little stumped so I pasted alot. To give you more of an idea:

 The application needs to be compiled as a 32bit app. I am doing this on an old XP machine, but it will be used on Win7 and Win8 as well, in compatibility mode.

It is the connection string that is causing the problem. I am wanting to test if I should use ACE or JET.

something like:
Private Function test_ace_or_jet(ByVal mdb_path As String) As String
        Dim connString As String
        Dim dbMaintPort As OleDb.OleDbConnection

        connString = ""

        Try
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mdb_path

            dbMaintPort = New OleDb.OleDbConnection(connString)
            dbMaintPort.Open()

            dbMaintPort.Close()

            MsgBox("1 was found")
        Catch ex As Exception
            MsgBox("Could not open 1" & vbCrLf & vbCrLf & ex.Message)
            Try
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdb_path
                dbMaintPort = New OleDb.OleDbConnection(connString)
                dbMaintPort.Open()
                dbMaintPort.Close()

                MsgBox("2 was found")
            Catch ex2 As Exception
                connString = ""
                MsgBox("Could not open 2" & vbCrLf & vbCrLf & ex2.Message)
            End Try
        End Try
        test_ace_or_jet = connString
    End Function

Open in new window


The issue that I've found is that on the XP machine, the first connection string does not fail. But it does produce some strange results.

How can I test whether to use one or the other? Am I able to get the above function to give me the correct connection string?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38854046
I'd be inclined to modify your SQL to create one select statement rather than 3, or rather than an unknown amount of checked checkboxes.
' outside the loop ( prime your sql string )
sqlStr = "Select * from SYSTEM_SYNC_DATE Where MODULE_CODE=""" 

' inside the loop (concatenate your whereclause )
if x=1 then
  sqlStr  = sqlStr   & module_prefix & """
else
  sqlStr  = sqlStr  &  " OR MODULE_CODE=""" & module_prefix & """
end if

' should end up with a concatenated whereclause something like:
Select * from SYSTEM_SYNC_DATE Where MODULE_CODE="AR" OR MODULE_CODE="BD" MODULE_CODE="CM"

' outside the loop bind to some adaptor or sqldatasource ( one round trip to the server )
daWorkRS1 = New OleDb.OleDbDataAdapter(sqlStr, dbMaintPortable)
...

Open in new window


 
Alan
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:AssetFX
ID: 38854075
Please ignore my previous comment, that was completely wrong. I have both Jet and ACE on this machine and both are doing the same thing
0
 

Author Comment

by:AssetFX
ID: 38854181
Thanks alan
new update:
I output the connection string to screen from the data adapter, which is correct.
Opened the database checked the records, there are three

Did the following SQL
sqlStr = "Select * from SYSTEM_SYNC_DATE"

daWorkRS1 = New OleDb.OleDbDataAdapter(sqlStr, dbMaintPortable)
dsWorkRS1 = New DataSet()
daWorkRS1.Fill(dsWorkRS1)
msgbox("Rows=" & dsWorkRS1.Tables(0).Rows.count)
For y As Integer = 0 To dsWorkRS1.Tables(0).Rows.Count - 1
   msgbox("module code=" & dsWorkRS1.Tables(0).Rows(y)("MODULE_CODE"))
Next

Open in new window


Output:
Rows=2
module code=AR
module code=CM

BD is missing, however looking in the table it is there.It is also the last entered record.

I have tried entering another record and perform the above test again. It nows finds AR, CM and BD but not the new one BA.

Output:
Rows=2
module code=AR
module code=CM
module code=BD
0
 
LVL 85
ID: 38854423
Where is the Access database located on your dev machine? Wondering if this is the result of folder virtualization. To test that, move the Access db to your Documents folder and modify your connect string to point to that one.
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 1600 total points
ID: 38855139
If you opened the table and the "new" record is there, that's proof enough that the insert was successful, which suggests you have a fairly good connection.

You did however, close the connection after doing the insert, which doesn't completely destroy dbMaintPortable, it's not nothing.

I'm wondering if you opened the connection again prior to interrogating for the existence of the new record (which we know exists) in dbMaintPortable?

You may be querying a dis-connected recordset.

Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38857515
Hi FX,

I'm suspecting your connection is closed when you are executing this sql against the adaptor.
sqlStr = "Select * from SYSTEM_SYNC_DATE"

daWorkRS1 = New OleDb.OleDbDataAdapter(sqlStr, dbMaintPortable) 'I  think this is a closed connection
dsWorkRS1 = New DataSet()
daWorkRS1.Fill(dsWorkRS1)
msgbox("Rows=" & dsWorkRS1.Tables(0).Rows.count)
For y As Integer = 0 To dsWorkRS1.Tables(0).Rows.Count - 1
   msgbox("module code=" & dsWorkRS1.Tables(0).Rows(y)("MODULE_CODE"))
Next 

Open in new window


Try re-instantiating the connection prior to executing the sql to query the results.
if dbMaintPortable  isnot nothing then ' could possibly just check the open state of the connection here, but destroying it will do the trick.
dbMaintPortable.Dispose()
end if

' re-instatiate the connection
dbMaintPortable = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & portable_maintenance_db)

' run you sql to query the results here
...

Open in new window

Alan
0
 
LVL 11

Assisted Solution

by:datAdrenaline
datAdrenaline earned 400 total points
ID: 38857576
Short on time ... but I took the liberty to modify your original code very quickly.  Take a look at the placement of the .Open() of the connection, and the SQL command strings ...

Try
  dbMaintPortable = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & portable_maintenance_db)
  dbMaintPortable.Open()
Catch ex As Exception
  Try
    dbMaintPortable = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & portable_maintenance_db)
    dbMaintPortable.Open()
  Catch ex2 As Exception
    txtProcess.Text = txtProcess.Text & vbCrLf & "Could not open:" & vbCrLf & vbCrLf & portable_maintenance_db & vbCrLf & vbCrLf & ex.Message
    MsgBox ("Could not open:" & vbCrLf & vbCrLf & portable_maintenance_db & vbCrLf & vbCrLf & ex.Message)
    Exit Sub
  End Try
End Try

Dim daWorkRS1 As OleDb.OleDbDataAdapter
Dim dsWorkRS1 As DataSet

For x As Integer = 1 To chkboxCtrlArray.Length - 1
  module_prefix = Strings.Left(chkboxCtrlArray(x).Text, 2)

  last_sync_date = CDate("1/1/1990 1:00:00 AM")
  makeNewSyncDate = False
  updateSyncDate = False

  sqlstr = "Select * from SYSTEM_SYNC_DATE Where MODULE_CODE='" & module_prefix & "'"
  txtProcess.Text = txtProcess.Text & vbCrLf & sqlstr
  daWorkRS1 = New OleDb.OleDbDataAdapter(sqlStr, dbMaintPortable)
  dsWorkRS1 = New DataSet("SYS_SYNC_DATE_" & module_prefix)

  daWorkRS1.Fill (dsWorkRS1)

  If (dsWorkRS1.Tables.Count > 0) Then
    If (dsWorkRS1.Tables(0).Rows.Count > 0) Then
      Dim drRow As DataRow = dsWorkRS1.Tables(0).Rows(0)
      If (IsDBNull(drRow("SYNC_DATE"))) Then
        updateSyncDate = True
        txtProcess.Text = txtProcess.Text & vbCrLf & module_prefix & " update"
      Else
        last_sync_date = drRow("SYNC_DATE")
        txtProcess.Text = txtProcess.Text & vbCrLf & module_prefix & " " & last_sync_date
      End If
    Else
      makeNewSyncDate = True
      txtProcess.Text = txtProcess.Text & vbCrLf & module_prefix & " makeNewSyncDate"
    End If
  End If

  If (updateSyncDate) Then
    txtProcess.Text = txtProcess.Text & vbCrLf & "update"
    sqlstr = "Update SYSTEM_SYNC_DATE Set SYNC_DATE = #" & last_sync_date & "# Where MODULE_CODE = '" & module_prefix & "'"

    sync_query = New OleDb.OLEDBCommand
    sync_query.Connection = dbMaintPortable
    sync_query.CommandType = CommandType.Text
    sync_query.CommandText = sqlstr
    sync_query.ExecuteNonQuery()
  End If

  If (makeNewSyncDate) Then
    txtProcess.Text = txtProcess.Text & vbCrLf & "insert"
    sqlstr = "Insert Into SYSTEM_SYNC_DATE (MODULE_CODE, SYNC_DATE) Values ('" & module_prefix & "', #" & last_sync_date & "#)"

    sync_query = New OleDb.OLEDBCommand
    sync_query.Connection = dbMaintPortable
    sync_query.CommandType = CommandType.Text
    sync_query.CommandText = sqlstr
    sync_query.ExecuteNonQuery()
  End If
Next
dbMaintPortable.Close()

Open in new window

0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38857642
You could use the OleDbDataAdapter to insert the rows, then invoke .AcceptChanges() on the adaptor to synch in-memory data with the data source.

Use OleDbDataAdapter to insert a row into Access database

Alan
0
 

Author Comment

by:AssetFX
ID: 38857811
Hi Guys, brilliant deductions. I wasn't managing my open connections very well. And it also was happening slightly outside of the code I posted first. But wouldn't have got it without your advice.

Best practise 101: manage your connections strictly.

That was a frustrating one but so simple to fix!

Take care and thankyou!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question