asked on

Querying Access database in VB.Net

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

Bob Learned

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?

AssetFX

ASKER

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?

Alan Warren

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

AssetFX

ASKER

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

AssetFX

ASKER

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

Scott McDaniel (EE MVE )

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.

ASKER CERTIFIED SOLUTION

Alan Warren

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alan Warren

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

SOLUTION

datAdrenaline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alan Warren

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

AssetFX

ASKER

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!