Reply to thread

Message

[QUOTE="bagel, post: 7984, member: 615"]
Sorry here is the code that I am using

[CODE] 
   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
[/CODE]
[/QUOTE]

Verification