Weird Database Problem

[Mighty]

Guys,

I am trying to make a copy of a database from an ASP page and then delete all the records that exist in three tables. The copy goes fine and I then connect to the copy and attempt to delete all records from the three tables as follows:

' Connect to the database copy
set conn = Server.CreateObject("ADODB.Connection&quot
conn.Open strConnect

' Delete all the records from the Lead Details Table
sql = "DELETE FROM [Lead Details]"
conn.Execute sql, , adCmdText

' Delete all the records from the Lead Master Table
sql = "DELETE FROM [Lead Master]"
conn.Execute sql, , adCmdText

' Delete all the records from the Company Master Table
sql = "DELETE FROM [Company Master]"
conn.Execute sql, , adCmdText


However, for some reason it never deletes the records from the third table - regardless of what order I place the delete statements in. i.e. if [Lead Details] is last, it will delete the records from the other two tables but not the [Lead Details] table. In the code above, records in the [Company Master] table are not deleted!!

Any ideas??? Mise Le Meas,

Mighty

 

[RushiShroff]

If you are using SQL server, concatenate string with each other.Like

DELETE FROM Lead DetailsDELETE FROM Lead MasterDELETE FROM Company Master

Then execute it once..

Also avoid [] around tables.

Give seperate name to all 3 strings like SQL1, SQL2 AND SQL3.


Try to use ODBC drivers for OLEDB because sometimes OLEDB native creates problem.(while opening connections with ADO)

Rushi Shroff Rushi@emqube.com
"Life is beautiful."

 

[derren]

I think this is down to the fact that the speed of the database operations takes longer to perform than the asp can bother to wait for (it is still performing operations on the database when the delete commands come in - so it has nothing to delete)

My advice, and what I always do in this case, is to do it in two stages, copy your data, then response.redirect to the page again to delete the records. This tends to give enough time for the processes to complete. Derren
[Mediocre talent - spread really thin]

 

[Mighty]

Guys,

I have sorted the problem. The way my program works is that once it has deleted all the records from the three tables it then copies the "COPY" to a new location. However, I was copying the DB before I close the ASP program's connection to it and so it wasn't waiting for it to complete the last command. So I just made sure to close it and then copy it and it works fine.

Thanks for the suggestions. (RushiShroff - I was using MS Access) Mise Le Meas,

Mighty