record did not update the table

[yan19454]

If LabOutConnection.State = ConnectionState.Closed Then
LabOutConnection.Open()
End If



Dim sqlStr As String = "select * from NewImportLabChar where Mid([NewImportLabChar]![LabResultString],1,1)='>' or Mid([NewImportLabChar]![LabResultString],1,1)='<' "
Dim dsLab As New DataSet
Dim da As New OleDbDataAdapter



da.SelectCommand = New OleDbCommand(sqlStr, LabOutConnection)
da.SelectCommand.Connection = LabOutConnection
da.SelectCommand.CommandType = CommandType.Text
da.Fill(dsLab, "NewImportLabChar")

Dim drLab As DataRow

Dim intRow As Int16
For Each drLab In dsLab.Tables("NewImportLabChar").Rows
drLab = dsLab.Tables("NewImportLabChar").Rows(intRow)
drLab.BeginEdit()
drLab("LabResult") = GetLabValue(drLab("LabResultString"))

Debug.Print(GetLabValue(drLab("LabResultString")))
drLab.AcceptChanges()

Next
dsLab.Tables("NewImportLabChar").AcceptChanges()
da.Update(dsLab, "NewImportLabChar")
MessageBox.Show(da.Update(dsLab, "NewImportLabChar").ToString)
da.Dispose()

I looped through the datarow and run the application without error. but the record number update is zero.

 

[mansii]

Do not update the datarow before the da.Update is executed.
drLab.AcceptChanges() should be removed.

If you are going to continue updating the datatable, then
put dsLab.AcceptChanges after da.Update(dsLab, "NewImportLabChar")

Regards.

 

[yan19454]

If LabOutConnection.State = ConnectionState.Closed Then
LabOutConnection.Open()
End If
Dim sqlStr As String = "select * from NewImportLabChar where Mid([NewImportLabChar]![LabResultString],1,1)='>' or Mid([NewImportLabChar]![LabResultString],1,1)='<' "
Dim dsLab As New DataSet
Dim da As New OleDbDataAdapter
da.SelectCommand = New OleDbCommand(sqlStr, LabOutConnection)
da.SelectCommand.Connection = LabOutConnection
da.SelectCommand.CommandType = CommandType.Text
da.Fill(dsLab, "NewImportLabChar")
Dim drLab As DataRow
'strS = drLab("LabResultString")
'

http://msdn2.microsoft.com/en-us/library/tat996zc.aspx

'

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

Dim intRow As Int16
For Each drLab In dsLab.Tables("NewImportLabChar").Rows
drLab = dsLab.Tables("NewImportLabChar").Rows(intRow)
Try
' Dim cmd As New OleDbCommandBuilder(da)
' Debug.Print(drLab("PNO"))
drLab.BeginEdit()
Debug.Print(drLab("InternalID").ToString)
If drLab("LabCode") = "MB" Or drLab("LabCode") = "CPK" Then
'as whole number
drLab("LabResult") = GetWLabValue(drLab("LabResultString"))
Else
drLab("LabResult") = GetLabValue(drLab("LabResultString"))
End If
' drLab.AcceptChanges()
Catch e As Exception
MessageBox.Show(e.Message)
End Try

Next
da.Update(dsLab, "NewImportLabChar")
dsLab.Tables("NewImportLabChar").AcceptChanges()
MessageBox.Show(da.Update(dsLab, "NewImportLabChar").ToString)
da.Dispose()
I did the change. but I still get the datarow update is 0.
data is not update.
In Debug.Print(drLab("InternalID").ToString)it only print out one row and did not move to different record.but it loop through the record.

 

[assimang]

Would it be bad idea/wrong, instead of these to use an update sql stament? Something like this for example?

sql = "update NewImportLabChar set labResultString=something where Mid([NewImportLabChar]![LabResultString],1,1)='>' or Mid([NewImportLabChar]![LabResultString],1,1)='<'"

Command = New SqlCommand(sql, MyConn)
' Execute the command.
Dim NumAffected As Integer
NumAffected = Command.ExecuteNonQuery()

 

[yan19454]

do not know why it did not update. the datarow count is 276. the string in query can return
select * from NewImportLabChar where Mid([NewImportLabChar]![LabResultString],1,1)='>' or Mid([NewImportLabChar]![LabResultString],1,1)='<' return 276 record ,too.

but one thing is strange is Debug.Print(drLab("InternalID").ToString) just print out the first row of the datarow 276 times.

Thx.

 

[yan19454]

Problem solved according the solution on the link.

http://www.vbcity.com/forums/goto.asp?show=reply&tid=154110&rid=505746