Timing for ExecuteNonQuery

[Guest]

Hi,

I am adding a record to the database using an ExecuteNonQuery, which adds
without problem. Now after the record is added I run a method passing in
some info as well as the curretnly opened connection (byRef cn as
OleDbConnection). Am using an Access 2000 database. Now in this new method I
create a command object using the passed in connection object. I then create
a DataReader to read the database that has the record added in the
ExecuteNonQuery section. Now if I step through my program everything works,
but if I let it run the dataReader doesn't see the newly created record!! If
I add a for loop (1000 steps, sometimes longer) the DataReader see the
record. So I can I be sure that the record added is actually in the database
before the dataReader attempts to read the database? By the way the
RecordsAffected in the ExecuteNonQuery does show 1.

Any ideas?

Thanks
John

 

[Marina]

You need to post the relevant pieces of code.

 

[Guest]

Sorry here is the code in question

Private Function SaveNewSteps() As Boolean
Dim myCN As New OleDbConnection(myConnectionString)
Dim cmd As New OleDbCommand
Dim SQL As String
Dim rID As Long
Dim RecordsAffected As Integer
Dim i As Integer
Dim sc As Byte
Dim retval As Boolean = True

cmd.Connection = myCN
myCN.Open()

sc = CType(1, Byte) + m_StepCode ' Take step code and add 1 to it
SQL = "INSERT INTO ztblCustomRequirementSteps (ProjectID,
RequirementID, StepNum, " & _
"StepName, StepDesc, StepCode, StepInternal)" & _
" VALUES " & _
"(" & m_ProjID & "," & m_reqID & "," & _
m_StepNum & ",'" & _
Replace(txtNewStep.Text, "'", "''") & "" & "','" & _
Replace(txtNewStepDesc.Text, "'", "''") & "" & "'," & _
sc & ",True)"
cmd.CommandText = SQL

Try
RecordsAffected = cmd.ExecuteNonQuery()
' we need to add the timeframe for this new step to tblTimeframe
If RecordsAffected > 0 Then

' Need to update the StepCode values
updateSteps(m_reqID, m_StepNum, myCN)

End If
Catch ex As Exception
retval = False
MessageBox.Show("Error Adding New Steps:" & ControlChars.CrLf &
ControlChars.CrLf & ex.ToString & ControlChars.CrLf & "An exception of type "
& ex.GetType().ToString() & _
" was encountered while adding new steps.",
"Adding New Steps")
Finally
myCN.Close()
End Try
Return retval
End Function

Private Sub updateSteps(ByVal rID As Long, ByVal lNum As Long, ByRef fCN
as OleDbConnection)
' Create the Command object
Dim cnS As New OleDbConnection(myConnectionString)
Dim cmdSQL As New OleDbCommand("SELECT * FROM
ztblCustomRequirementSteps where ProjectID = " & m_ProjID & _
" AND RequirementID = " & rID & _
" AND StepNum = " & lNum & " ORDER BY
StepCode, TempStepCode DESC", fCN)
Dim cmdInsert As New OleDbCommand
Dim SQL As String
Dim RecordsAffected As Integer
Dim sId As Long
Dim sCode As Byte
Dim tCode As Byte
Dim bReorder As Boolean = False

cmdInsert.Connection = cnS
cnS.Open()

' Dummy loop to make sure that all data has been written
' If this loop is not present the following DataReader will not see
the record
' that was added in this calling function SaveNewSteps()
Dim i as Integer
For i = 0 to 1000
debug.WriteLine (i)
Next

Dim rdr As OleDbDataReader =
cmdSQL.ExecuteReader(CommandBehavior.CloseConnection)
With rdr
While .Read
sId = .GetValue(0) '
Read StepID
sCode = .GetValue(6) '
StepCode
If Not IsDBNull(.GetValue(7)) Then tCode = .GetValue(7)
'Temp Step Code
If bReorder Then
' We have reached a point where steps need to be reordered
SQL = "UPDATE ztblCustomRequirementSteps SET
StepCode=StepCode + 1 WHERE " & _
"StepID = " & sId
cmdInsert.CommandText = SQL
Try
RecordsAffected = cmdInsert.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
Else
' If the TempStepCode is not 1, do nothing
If tCode = 1 Then
' Reorder all following records
bReorder = True
SQL = "UPDATE ztblCustomRequirementSteps SET
TempStepCode = 0 WHERE " & _
"StepID = " & sId
cmdInsert.CommandText = SQL
Try
RecordsAffected = cmdInsert.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End If
End If
End While
End With
rdr.Close()
cnS.Close()
End Sub

John