DataRow() not moving?

[mansii]

Hi mates.
New to DataRow stuff. Here's the listing:

Private Function AddRecord1() as Boolean
Dim conStringS As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
		 "Data Source = " & Application.StartupPath & "\System\" & SourceFile & ";Jet OLEDB:Database Password=" & dbPwd & ""
Dim connS As OleDb.OleDbConnection = New OleDb.OleDbConnection(conStringS)
Dim conStringT As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
		 "Data Source = " & Application.StartupPath & "\System\" & TargetFile & ";Jet OLEDB:Database Password=" & dbPwd & ""
Dim connT As OleDb.OleDbConnection = New OleDb.OleDbConnection(conStringT)
Dim DataAdapter_A As OleDb.OleDbDataAdapter
Dim DataAdapter_B As OleDb.OleDbDataAdapter
Dim DataAdapter_C As OleDb.OleDbDataAdapter
Dim PDataset As DataSet

Dim cmdInsert As OleDb.OleDbCommand
Dim Writer As OleDb.OleDbDataReader
Dim xP As String, xK As String, xC As String, xD As String
Dim TabelName As String, SourceTable As String
Dim i As Integer, it As Integer
Dim saveDataTbl As DataTable
Dim SingleDataTbl As DataTable
connS.Open()
connT.Open()
DataAdapter_A = New OleDb.OleDbDataAdapter("Select * from Data_A where R101B='" & tID1 & _
		 "' and R102B='" & tID2 & "'", connS)
DataAdapter_B = New OleDb.OleDbDataAdapter("Select * from Data_B where R101B='" & tID1 & _
		 "' and R102B='" & tID2 & "'", connS)
DataAdapter_C = New OleDb.OleDbDataAdapter("Select * from Data_C where R101B='" & tID1 & _
		 "' and R102B='" & tID2 & "'", connS)
If PDataset Is Nothing Then
	PDataset = New DataSet
End If

DataAdapter_A.Fill(PDataset, "Dataset_A")
DataAdapter_B.Fill(PDataset, "Dataset_B")
DataAdapter_C.Fill(PDataset, "Dataset_C")
Dim pRow As DataRow()
Dim R2D As Data.DataRow
Dim DataString As String
For i = 1 To 3
	Select Case i
		Case 1
		TabelName = "Data_A"
		SourceTable = "Dataset_A"
		Case 2
		TabelName = "Data_B"
		SourceTable = "Dataset_B"
		Case 3
		TabelName = "Data_C"
		SourceTable = "Dataset_C"
	End Select
	SingleDataTbl = PDataset.TablesSource(Table)
	it = 0[blue]
	For it = 0 To SingleDataTbl.Rows.Count - 1
		xP = SingleDataTbl.Rows(it)("r101b").ToString
		xK = SingleDataTbl.Rows(it)("r102b").ToString
		xC = SingleDataTbl.Rows(it)("r103b").ToString
		xD = SingleDataTbl.Rows(it)("r104b").ToString
		pRow = SingleDataTbl.Select("r101b='" & xP & "' and r102b='" & xK & "' and r103b='" & xC & "' and r104b='" & xD & "'")[/blue]
		[red]DataString = SetAddString(pRow(0))[/red][blue]
		Call InsertIt(DataString, TabelName, connT, xP, xK, xC, xD)
		it += 1
		DataString = Nothing
		pRow = Nothing
	Next[/blue]
	SingleDataTbl.Clear()
Next
PodesDataset = Nothing
connS.Close()
connT.Close()
Return True
End Function

Private Function InsertIt(ByVal DataString As String, ByVal TableName As String, ByVal connT As OleDb.OleDbConnection, ByVal xP As String, ByVal xK As String, ByVal xC As String, ByVal xD As String) As Boolean
Dim cmdInsert As OleDb.OleDbCommand
Dim Writer As OleDb.OleDbDataReader
cmdInsert = New OleDb.OleDbCommand("INSERT INTO " & TabelName & DataString & "", connT)
Writer = cmdInsert.ExecuteReader()
Writer.Close()
cmdInsert = New OleDb.OleDbCommand("UPDATE Village SET flag='C' where ID1='" & xP & "' and ID2='" & xK & "' and ID3='" & xC & "' and ID4='" & xD & "'", connT)
Writer = cmdInsert.ExecuteReader()
Writer.Close()
End Function

Private Function SetAddString(ByVal XRow As DataRow) As String
Dim str_A_Values As String, str_B_Values As String, ColName As String
Dim cColumn As DataColumn
Dim xLoop As Int16
For xLoop = 0 To XRow.Table.Columns.Count - 1
	ColName = UCase(XRow.Table.Columns(xLoop).ColumnName)
	If str_A_Values = "" Then
		str_A_Values = ColName
	Else
		str_A_Values &= "," & ColName
	End If
Next
str_A_Values = "(" & str_A_Values & ")"

For xLoop = 0 To XRow.Table.Columns.Count - 1
	If str_B_Values = "" Then
		str_B_Values = "'" & XRow.Table.Rows(0)(xLoop).ToString & " '"
	Else
		str_B_Values &= ",'" & XRow.Table.Rows(0)(xLoop).ToString & " '"
	End If
Next
str_B_Values = "(" & str_B_Values & ")"

Return str_A_Values & " Values " & str_B_Values
End Function

Where tID1 and tID2 are public variables, r101b, r102b, r103b, and r104b are unique primary keys. I'm sure that the SingleDataTbl has 2 (two) rows.
The problem is that the DataString was never updated (the DataRow doesn't seem to move to next record before triggering the SetAddString function.)
I tried using:

Dim tempRow as DataRow()
For each tempRow in SingleDataTbl.Rows

with no luck.

Any help would be appreciated.
mansii

 

[bboffin]

In the InsertIt function why are you calling the ExecuteReader method of the Command instead of ExecuteNonQuery which is what you should be using?

The problem is being masked because you aren't using a Try Catch block around the code which is probably failing and not telling you why.

Try this:

Private Function InsertIt(ByVal DataString As String, ByVal TableName As String, ByVal connT As OleDb.OleDbConnection, ByVal xP As String, ByVal xK As String, ByVal xC As String, ByVal xD As String) As Boolean
Dim cmdInsert As OleDb.OleDbCommand
    cmdInsert = New OleDb.OleDbCommand("INSERT INTO " & TabelName & DataString, connT)
    cmdInsert.CommandType = CommandType.Text
    Try
        cmdInsert.ExecuteNonQuery()
        cmdInsert.CommandText = "UPDATE Village SET flag='C' where ID1='" & xP & "' and ID2='" & xK & "' and ID3='" & xC & "' and ID4='" & xD & "'"
        cmdInsert.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show(string.Format("InsertIt: Error: {0} SQL: {1}", ex.Message, cmdInsert.CommandText))
    End Try
End Function

Note that you can reuse cmdInsert simply by changing the CommandText for the UPDATE statement.

See also the use of String.Format to build up the exception message. String.Format is a very powerful function that can be also used to build up the SQL strings that you want to execute. It has several advantages over the usual concatenation not least because it is much more readable and easier to spot errors especially missing single quotes around string constants.


Bob Boffin

 

[mansii]

Hi Bob!
Thanks for your explanation on reusing OleDbCommand. Great job!

Anyways, my real problem is that the DataString variable never changed (please take a look at the red line in AddRecord function above, thank's).

Any suggestion? Especially on the blue statement block?

TIA
mansii

 

[bboffin]

Have you traced the code through (or aren't you using VS.NET)? This would show up the problem very quickly.

Your code should check that there is at least one row in the array returned by the Select method before calling SetAddString otherwise SetAddString will fail but there could still be an exception being thrown in either of the subroutines so protect them with a Try Catch block.

Try:

.
.

        pRow = SingleDataTbl.Select("r101b='" & xP & "' and r102b='" & xK & "' and r103b='" & xC & "' and r104b='" & xD & "'")
        If pRow.GetLength(0) > 0 Then
            Try
                DataString = SetAddString(pRow(0))
                Call InsertIt(DataString, TabelName, connT, xP, xK, xC, xD)
            Catch ex As Exception
                MessageBox.Show(ex.message)
            End Try
        End If
.
.

Bob Boffin

 

[mansii]

Bob,
sorry for the long silence.
My mistake. I should have put teh desired row index to:

str_B_Values = "'" & XRow.Table.Rows(0)(xLoop).ToString & " '"

to

str_B_Values = "'" & XRow.Table.Rows(rIndex)(xLoop).ToString & " '"

Anyways, thanks.

Regards.
mansii