Solved

Querying Access database in VB.Net

Posted on 2013-02-03
11
602 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:AssetFX
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 84
Comment Utility
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 400 total points
Comment Utility
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
Comment Utility
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 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now