Using ADO 2.7 in VB.NET

[Guest]

Hello everyone,

Are there any issues regarding using ADO, not ADO.NET inside of VB.NET? I
have a piece of code that loads a Visual Fox Pro table but stops working
after the 30th record has been loaded.

Here is a some of the code.

adoRS = New ADODB.Recordset()
adoCN = New ADODB.Connection()
RowArray = flat.Tables(0).Select("sequence=" &
row.Item("sequence").ToString)
If RowArray.Length = 0 Then
Try
adoCN.ConnectionString = tblDestinationConn
adoCN.Open()
adoRS.CursorLocation = ADODB.CursorLocationEnum.adUseClient
adoRS.LockType = ADODB.LockTypeEnum.adLockPessimistic
adoRS.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
adoRS.Open("select * from csesend", adoCN, , ,
ADODB.CommandTypeEnum.adCmdText)
adoRS.AddNew()
For Each col In flat.Tables(0).Columns
If Not TypeOf row.Item(col.ColumnName) Is System.DBNull Then
If Trim(row.Item(col.ColumnName)) = "" Then
adoRS.Fields(col.ColumnName).Value = ""
Else
adoRS.Fields(col.ColumnName).Value = row.Item(col.ColumnName)
End If
Else
Select Case adoRS.Fields(col.ColumnName).Type
Case ADODB.DataTypeEnum.adDate
adoRS.Fields(col.ColumnName).Value = #12/30/1899#
Case ADODB.DataTypeEnum.adDBDate
adoRS.Fields(col.ColumnName).Value = #12/30/1899#
Case ADODB.DataTypeEnum.adDBTime
adoRS.Fields(col.ColumnName).Value = #12/30/1899#
Case ADODB.DataTypeEnum.adDBTimeStamp
adoRS.Fields(col.ColumnName).Value = #12/30/1899#
Case Else
adoRS.Fields(col.ColumnName).Value = ""
End Select
End If
Next
adoRS.Update()
adoRS.Close()
adoRS = nothing
adoCN.Close()
adoCN = nothing
Catch ex As Exception
MsgBox(ex.Source & vbCrLf & ex.Message)
End Try

After the 30th record is loaded I get the error message:

-2147217887
Multiple-step operation generated errors. Check each status value.

The error is occuring on the adoRS.Update() line.

Any suggestions? I am currently recoding the Application to use ADO.NET
with ADO.NET's update command.

Any help would go along way!

Markus McGee

 

[Guest]

Has anyone bothered to read the post?

Markus McGee

 

[Miha Markic [MVP C#]]

Markus,

This is a peer-to-peer ng.
There is no guarantee to get an answer whatsover (unless you are MSDN
subscriber I think).
Did you consider that you feed ado with wrong values or some other "normal"
error?
BTW, why don't you use ado.net instead?

 

[Guest]

Yeah I know the group has no guarantee of answers. Anyway I can't use
ADO.NET because of the limitations of FoxPro. You can't create an insert
string that greater than 254 characters long. By using ADO 2.7 I can iterate
over the fields and do an update at the end of the record. This works fine
but when I hit the 30th record the application crashes. I believe it has
something to do with the Update command of ADO and the way .NET handles
garbage collection. In VB6 you could do a :

rs.Update()
rs.Close()
rs = Nothing

and everything would be fine. Now, I believe .NET is holding on the
connection or recordset or something. I've tried using the GC.Collect method
but I only get 30 records. I'm going to delete the top 30 records to see if
the next 30 will go in correctly. If you have any suggestions please let me
know.


Markus McGee

 

[Guest]

Found the answer. Apparently there's a problem with VFPOLEDB.1. Works with
Microsoft.Jet.Oledb.