Using VS2003, VB.NET,
Here's some pseudo code that I'm trying to get to work...
if <a specific record existsthen
do nothing
else
<insert the specific record>
Endif
I've got the code written, but Its not working for me...
1. when I check for the existence of the record, I always get False (even if
the record does exist) -- in the code below: the dataset returned from
GetOleDbDataSet in function RecordExist always contains 0 as the result from
the SELECT count(*).SQL -- even when I manually add a record to the actions
table. What am I doing wrong here?
2. related to #1. If I find that the record does not exist, I INSERT it
using an InsertCommand (in the AddRecord sub), then call the RecordExist
function have it return zero again. But when I open Access I verify that the
record exists. Seems like my connection to the database not picking up the
changes... I would not expect this because I am creating new connection
objects with each call to RecordExist... any idea what I'm missing here?
3. Unrelated to 1,2... when I open my connection to the MS Access mdb, I
get, as expected, a lock file (ldb). The problem is that when I dispose of
and close the adapters & connection objects, the lock file persists. Then
when I go to manually open the mdb, I'm get a message stating that the mdb is
exclusively locked... ??
Any pointer's to examples, answers or feedback on my issues would be
appreciated. See code snippets below.
Thanks for your attention to this matter.
Here are the functions & subs I wrote to accomplish my (simple) task:
'Implementation of pseudo code...
If RecordExist() Then 'Do nothing
Else
AddRecord()
End If
Public Function RecordExist() As Boolean
RecordExist = False
Dim ds As DataSet
Try
ds = GetOleDbDataSet(strConnection, _
"SELECT Count(*) FROM Actions WHERE Pk=1") 'Pk=primary key
on Actions table
If Int64.Parse(ds.Tables(0).Rows(0).Item(0).ToString) 0 Then
RecordExist = True
End If
Catch ex As Exception
ds.WriteXml(System.Console.Out)
End Try
End Function
Public Sub AddRecord()
Try
Dim da As OleDbDataAdapter = New OleDbDataAdapter
Dim cmd As OleDbCommand
Dim conn As New OleDbConnection(strConnection)
cmd = New OleDbCommand( _
"INSERT INTO Actions (Pk) VALUES (1)", conn)
da.InsertCommand = cmd
conn.Open()
da.InsertCommand.ExecuteNonQuery()
conn.Close()
da.Dispose()
conn = Nothing
cmd = Nothing
da = Nothing
Catch ex As Exception
Stop
End Try
End Sub
Public Function GetOleDbDataSet(ByVal strConn As String, ByVal strSQL As
String) As DataSet
Try
Dim conn As New OleDbConnection(strConn)
Dim adapter As New OleDbDataAdapter
Dim dataSet As New DataSet
adapter.SelectCommand = New OleDbCommand(strSQL, conn)
adapter.Fill(dataSet)
conn.Close()
adapter.Dispose()
conn = Nothing
adapter = Nothing
Return dataSet
Catch ex As Exception
Return Nothing
End Try
End Function