Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Querying Access database in VB.Net

Posted on 2013-02-03
11
Medium Priority
?
623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

715 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