add record to access db and then get primary key has error

[Ian Burton]

Hi,
I have a problem with the following:
Using vb.net I add a record to a Microsoft access 2000 table, and then I
immediately search for the record just added in order to get its primary
key. It can't find the primary key when I run the code normally, but if I
step through it, it works.
Any ideas? It is not a threaded application. Some of the code for the two
functions I am playing with is below.
Thanks in advance,
Ian Burton.

Public Function add(ByRef Organisation As OrganisationInfo) As Boolean
'returns the ID field into the company object

Dim oRequest As New dataRequest

Dim oParam As New dataRequest.Parameter

Dim oFactory As dataAbstractFactory

Dim result As Integer = 0

' Select the appropriate Concrete Factory to match your connection string

'oFactory = New dataSQLFactory

oFactory = New dataOleDbFactory

With oRequest

..Command = "INSERT INTO Organisation
(Organisation,Address,Postcode,Tel,Fax,DeletePassword) VALUES (" _

& "'" & Organisation.Organisation & "','" & Organisation.address & "','" _

& Organisation.postcode & "','" & Organisation.Tel & "','" &
Organisation.fax & "','" & Organisation.DeletePassword & "');"

..CommandType = CommandType.Text

..Transactional = False

End With

Try

result = oFactory.executenonquery(oRequest)

Catch ex As Exception

Debug.WriteLine(ex.Message)

Exit Function

End Try

If result <> 0 Then

'success

'now get the ID field from the database and put into the company object

Dim OrganisationID As Integer

'if i step through the next line It works, if I let it run it doesn't work.



OrganisationID = GetID(Organisation.Organisation)

If OrganisationID <> 0 Then

Organisation.ID = OrganisationID

Else

'it failed to get the id from the table for the just added ID, so there is
an error

Debug.Write("Added Organisation, but can't get the ID")

End If

Return True

Else

'failure - no rows affected

Return False

End If

End Function

Public Function GetID(ByVal Organisationname As String) As Integer

'returns the company table ID corresponding to the company name

Dim oRequestID As New dataRequest

Dim oParam As New dataRequest.Parameter

Dim oFactory As dataAbstractFactory

Dim oDataReader As dataAbstractDataReader

Dim dr As IDataReader

Dim OrganisationID As Integer

' Select the appropriate Concrete Factory to match your connection string

'oFactory = New dataSQLFactory

oFactory = New dataOleDbFactory

'set the request object

With oRequestID

..Command = "Select Orgc FROM Organisation WHERE Organisation='" &
Organisationname & "';"

..CommandType = CommandType.Text

..Transactional = False

End With

'run the command

Try

oDataReader = oFactory.ExecuteDataReader(oRequestID)

Catch ex As Exception

Debug.WriteLine(ex.Message)

Exit Function

End Try

'get the data out of the returned datareader

dr = oDataReader.ReturnedDataReader

While dr.Read

OrganisationID = IIf(IsDBNull(dr("Orgc")), 0, dr("Orgc"))

End While

dr.Close()

Return OrganisationID

End Function

 

[William Ryan eMVP]

Ian:

There's a few issues here. First and foremost is all of the dynamic SQL.
That makes it very difficult to follow on this end and does nothing good for
your end.
http://www.knowdotnet.com/articles/dynamisql.html . I have another article
there about calling a parameterized Stored Procedure...the way you to it
with Dynamic SQL Is identical with the exception of the commandtype
difference.

The first thing I see wrong outside of the dynamic sql is the result. You
are testing for <>0. You could conceivably get a -1 if a transaction was
rolled back (not entirely sure if you are but it looks like you may be).

The Questionname needs to be verfied b/c that could potentially cause some
problems. I'd consider using ExecuteScalar here b/c you don't really need to
loop through a reader, it's only going to return 1 value or 0 since it's a
key field right?

Also, turn on Option Strict ..there's many reasons for this, but that weak
typing could help to conceal the nature of the problem. Also make sure you
close your connections. -- To help too I'd litter the code with
Debug.Assert statements so I verify all of my suppositions at each pass.

As far as the issue that it works when you step through but not otherwise,
are you sure it's apples to apples? I suspect not. Either way, litter the
thing with assertions and run it. Let those guide you to the specific
problem.

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp

 

[Ian Burton]

thanks for your advice. I shall try all these and report back in a few days
with the results.
regards,
Ian.