C# - SQL Update keyword LAG!

[EFileTahi-A]

What could lead an simple "UPDATE _table SET _field = 'bla'" query to lag? That is, When I execute this throught an OleDbCommand the record will only update about 2/3 seconds after.

 

Do I have a problem with my connection to the database?

What can cause update lag?

 

Help desperately needed...

Thanks in advance!

[Nerseus]

Can you define what you mean by lag? To me, you're saying the code finishes executing but the database doesn't reflect the change for a few seconds? That's crazy!

 

Are you saying that the command takes a few seconds to run? You'll have to give us more info. Maybe you're updating 10,000 rows (you didn't specify a WHERE clause) and it just takes that long. Maybe you're saying that the code seems to be "doing something" for a few seconds before actually doing the update. You'll have to track that down, if you're seeing it. Maybe you're network connection is 56k modem connecting to a DB across the Atlantic.

 

Also, am I to assume that 2/3 is not two thirds, but 2 or 3?

 

-ner

[EFileTahi-A]

Can you define what you mean by lag? To me, you're saying the code finishes executing but the database doesn't reflect the change for a few seconds? That's crazy!

 

Are you saying that the command takes a few seconds to run? You'll have to give us more info. Maybe you're updating 10,000 rows (you didn't specify a WHERE clause) and it just takes that long. Maybe you're saying that the code seems to be "doing something" for a few seconds before actually doing the update. You'll have to track that down, if you're seeing it. Maybe you're network connection is 56k modem connecting to a DB across the Atlantic.

 

Also, am I to assume that 2/3 is not two thirds, but 2 or 3?

 

-ner

 

Here is the code and I will describe in detail what happens:

//*******************************************************************
// 7 - Updates the document number
//*******************************************************************
sSQL = "UPDATE DocLinTemp SET numdoc = " + this.txt_numDoc.Text + ", coddoc = '" + this.txt_codDoc.Text + "'";  
OleDbCommand cmd3 = new OleDbCommand(sSQL, DB_Engine.MyConnDataTemp);
cmd3.ExecuteNonQuery();


//MessageBox.Show(dtDocLinTemp.Rows[0]["numdoc"].ToString());


//*******************************************************************
// 8 - Copies the records from doclintemp to InDocLin
//*******************************************************************
sSQL = "INSERT INTO InDocLin SELECT * FROM doclintemp IN '" + Application.StartupPath + "\\DataTemp.mdb" + "'";
OleDbCommand cmd4 = new OleDbCommand(sSQL, DB_Engine.MyConnDataTemp);
cmd4.Connection = DB_Engine.MyConnData;
cmd4.CommandText = sSQL;
cmd4.ExecuteNonQuery();

 

See the deactivated MessageBox? When its ON the code executes with no prob, but when OFF both updated "numdoc" and "coddoc" will be blank in InDocLin table when the records are copied...

 

So my guess is that "UPDATE" query is not totaly processed by the time the program reaches "INSERT INTO" query... And we are talking about ONLY 1 record from DocLinTemp to InDocLin...

Edited February 14, 2005 by EFileTahi-A

[EFileTahi-A]

Also, the Application.Events() does not resolve this issue...

 

Is there a way of performing a pause in the code? I used to use API.Sleep or somthing in VB6, only this freezes the CPU...

[Nerseus]

Is the database in step 7 the same as that pointed to in step 8? If it is, why use the "IN" keyword? Is there another need for this, such as having to do this across databases in the future? If so, let us know - maybe you need to "flush" Access before it commits the changes?

 

Also, above step 7, do you have any code that would start a transaction or do anything that would make you think it's not committing the data immediately?

 

-ner

[EFileTahi-A]

Hi nerseus... They were in diferent Access data bases with independent connenctions. That is DocLinTemp and InDocLin were from diferent Access Data Bases

 

I mean "were" because I am converting the program to mySQL, and I am no longer using the DataTemp database which was holding the DocLinTemp table, instead, I will use an DataTable image of InDocLin in memory (that explains the other thread of mine about copying rows between tables) instead of storing it on an independent DataBase...

 

This was the way the software am currently working on was done in VB6, and am converting it to C# and things can go diferent now...

[Nerseus]

I was asking because the SQL didn't look like it was doing what you'd expect, if it were truly two different databases.

 

Suppose the SQL built in step 8 looks like this (I made up the path):

INSERT INTO InDocLin SELECT * FROM doclintemp IN 'c:\DataTemp.mdb'

 

The table DocLinTemp is coming from the database c:\DataTemp.mdb, not the other one. At least, that's what my tests show.

 

I think the code is assuming that it's using the DocLinTemp table from Step 7 - the one getting updated. I can't explain why the MessageBox would change this though.

 

Here was my test:

Create two databases: db1.mdb and db2.mdb.

db1 has table DocLinTemp ONLY

db2 has table InDocLin ONLY

 

Using similar code to yours I create a connection to db1 and perform the UPDATE. Then, still using that connection, I issue the INSERT INTO command that includes the "IN c:\db2.mdb" query. I get an error that table DocLinTemp doesn't exist.

 

So even though I'm using a connection to db1 which has the table, it seems like the query isn't really using that table.

 

What do you make of that?

 

-ner

[EFileTahi-A]

I realy can't tell, What I do know, is that it works prefectely in VB6! :confused: :confused: :confused:

[EFileTahi-A]

- THREAD RESOLVED -