Timing issues with ExecuteNonQuery

[bagel]

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?ThanksJohn

 

[kulrom]

Please post some code ...
Cheers

 

[bagel]

Sorry here is the code that I am using

   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

 

[jmcilhinney]

I'm not sure why the results should differ depending on the timing. You could try creating an OleDbTransaction for your initial Insert command and calling Commit() before trying to Select again. Depending on the specifics of your application, you could also use OleDbDataAdapters and a DataSet or a DataTable instead of OleDbDataReaders. That way, when you commit changes to the database you still have a local copy of the data and you wouldn't have to retrieve it again.

 

[kulrom]

Hi bagel,
It seems to me that your code is a too long for reading in details ... but you could explain your idea and i'll try to make a demo for you ... btw, using data reader class in this case is absolutely OK ... http://www.vbdotnetforums.com/showthread.php?t=3051

Cheers