update sequence of events - very strange behavior

[Bernie Yaeger]

I have a conversion routine which generates new rows into an sql server 2000
table from a .dbf file. I open both tables, one using the odbc driver, the
other sqlclient; I have a commandbuilder for the sql table. I loop through
the .dbf file in a for each irow loop and add rows as I loop.

Here's the strange part: I do not call update until after the for loop
completes. Yet I can go into sql query analyzer while the function is
running and do 'select count(*) from histd' and see it incrementing as it
goes - 45,000 rows; now 45,522 rows, etc. But I don't call update in my vb
..net code until the loop finishes, and it's not yet finished, as I am adding
750,000 rows! How can the rows be added to the back end while the loop is
running yet I have not yet called update?

Thanks for any help.

Bernie Yaeger

 

[Kathleen Dollard]

Bernie,

Check your code. I think you're calling the database in an unexpected place.

And passing 750,000 rows through ADO.NET is often no tthe best solution.

 

[Cor]

Hi Bernie,

Weird,

Do I understand you rigth, you are adding 750.000 rows to a dataset, and
than while there is no connection with the SQL server at all, it is
generating room for it?

Cor

 

[Bernie Yaeger]

Hi Cor,

I do have a connection to the database, but I am simply adding rows to the
dataset/datatable in a for loop; I call dataadapter.update after the loop is
done and all rows have been added to the dataset/datatable, yet the rows are
incrementing inside sql server even before the loop completes.

Bernie

 

[Bernie Yaeger]

Hi Kathleen,

For numerous reasons, it's the best solution. Notwithstanding, here's my
code:
For Each irow In ds_d.Tables(0).Rows

seekvarb = dsbranches.Tables(0).DefaultView.Find(irow("imcacct"))

arrayseekp(0) = irow("bipad")

arrayseekp(1) = irow("issuecode")

ifindp = vuep.Find(arrayseekp)

If ifindp <> -1 And seekvarb <> -1 Then

r = ds.Tables(0).NewRow()

r("bipad") = irow("bipad")

r("imcacct") = irow("imcacct")

r("issuecode") = irow("issuecode")

r("brname") = dsbranches.Tables(0).Rows(seekvarb)("brname")

r("title") = vuep(ifindp)("title")

r("ponumber") = irow("ponumber")

r("posstatus") = irow("voidvar")

' posstatus() - load this into voidvar (T in f, P in g and h),

' and get it from there

r("draw") = irow("draw")

r("preturn") = irow("preturn")

r("rreturn") = irow("rreturn")

r("net") = irow("net")

' r("net") = irow("draw") - (irow("preturn") + irow("rreturn"))

r("efficiency") = irow("efficiency")

r("billed") = irow("billed")

r("shipdt") = irow("shipdt")

r("uprice") = irow("uprice")

r("ptype") = irow("ptype")

r("sold") = irow("sold")

' r("vflag") = irow("voidvar") ' leave null

ds.Tables(0).Rows.Add(r)

icount = icount + 1

Else

r = dslost.Tables(0).NewRow()

r("bipad") = irow("bipad")

r("imcacct") = irow("imcacct")

r("issuecode") = irow("issuecode")

If seekvarb <> -1 Then

r("brname") = dsbranches.Tables(0).Rows(seekvarb)("brname")

Else

r("brname") = ""

End If

If ifindp <> -1 Then

r("title") = vuep(ifindp)("title")

Else

r("title") = ""

End If

r("ponumber") = irow("ponumber")

r("posstatus") = irow("voidvar")

' posstatus() - load this into voidvar (T in f, P in g and h),

' and get it from there

r("draw") = irow("draw")

r("preturn") = irow("preturn")

r("rreturn") = irow("rreturn")

r("net") = irow("net")

' r("net") = irow("draw") - (irow("preturn") + irow("rreturn"))

r("efficiency") = irow("efficiency")

r("billed") = irow("billed")

r("shipdt") = irow("shipdt")

r("uprice") = irow("uprice")

r("ptype") = irow("ptype")

r("sold") = irow("sold")

' r("vflag") = irow("voidvar") ' leave null

dslost.Tables(0).Rows.Add(r)

End If

Next

Try

da.Update(ds, "DS")

MsgBox("Conversion succeeded; " & icount & " records converted.",
MsgBoxStyle.Information, "Data Conversion System")

Catch ex As Exception

MsgBox(ex.Message)

MsgBox("Conversion failed.", MsgBoxStyle.Information, "Data Conversion
System")

retval = 1

End Try

Let me know what you think.

 

[Cor]

Hi Bernie,

Why not close that connection. (not dispose)

The datadapter opens the connection itself so you do not need to have it
open for that.

You ask maybe why I write this, just to see what happens

If it is still happens than, I would do a dispose of the connection and
create before the dataadapter update a new connection.

Just trying

I am currious about this?

Cor

 

[Bernie Yaeger]

Hi Cor,

I think you're on to something here. I am thinking that the connection
along with the commandbuilder is acting on its own and not interested in my
update call! Could this be? I will try it and I will let you know.

Tx

Bernie

 

[Bernie Yaeger]

Hi Cor,

No, the closing of the connection did not change anything.

Do you think it's possible that, the system being complied, ado .net knows
that the update will be called and relieves memory of all the changes
periodically, almost a garbage collection of changes to be made and makes
them? Sounds crazy, but I have not other answer.

Tx for you interest.

Bernie

 

[Kathleen Dollard]

Bernie,

Wow. I think if ADO.NET is doing what it appears to be doing its an out and
out bug.What I think should happen is ADO.NET throw up its hands and spring
a memory exception at some point in handling these records. This will also
ensure that the code you're looking at is the code actually running. I don't
think you can handle 750,000 records of any reasonable size in a single
update.Of course, I've also never tried.

Can you run this in the debugger? Can you put an increment value in and
include Debug.Writeline("***" & i.ToString & "***")? Also check whether
there is a Try/Catch in the calling code, or wrap your loop in a Try/Catch
and put a break point in the Catch.

Why do you think you can fill that much data through.