Delete records from SQL 1

[Anesthaesia]

Hi,

I'm trying to delete a record using the DELETE FROM command. My code is

cn.execute "DELETE FROM table WHERE id=1"

When executing this command I get a timeout, but running it through Query Analyzer it works fine (takes < 1sec).

All my INSERT INTO commands work fine, but I cannot delete in this way from VB.

Any ideas ?

Cheers,
D

 

[fredericofonseca]

Is this ADO/RDO/DAO?

is cn a command object, a recordset object or a connection object?

what is the database? looks like SQL Server but you should explicity mention that.

if ADO try and use a command object if this is not the case already.


And full code including all declarations is always the best way to get help on a problem, as doing otherwise will just be guessing.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Anesthaesia]

fredericofonseca,

As stated in the subject, it's an SQL database.

cn is a connection, which is already established.

Dim cn as new ADODB.Connection
cn.ConnectionString = "Driver=SQL Server;uid=USR;pwd=PASS;Server=SVR;Database=DB"
cn.ConnectionTimeout = 120
cn.CommandTimeout = 120
cn.Open

cn.execute "DELETE FROM table WHERE id=1"

 

[fredericofonseca]

Saying it is a SQL does not mean a thing.

ORACLE, DB2 UDB, SYBASE, ADABAS, MySql and SQL Server all use SQL.


That command should work fine. and it does with ADO 2.7/2.8 and SQL Server 2000.

as I said try and use the command object to see if you have the same problem.

Try also to set cn.CursorLocation = adUseClient just to see if it matters. Shouldn't.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Anesthaesia]

I am getting the same problem using a command object, and cn.CursorLocation doesn't hwlp either.

I am using ADO 2.7

Again I testes the command using Quesry Analyzer and it worked fine.

The other option is to open a recordset and use the .delete and .update commands, but that will slow the app down.

 

[fredericofonseca]

Hum..
The following works fine with SQL Server 2000, VB6 (SP5) and ADO 2.7.
Dim dbconnstring As String
Dim DBConn As New ADODB.connection
DBConn.CursorLocation = adUseClient
DBConn.ConnectionTimeout = 120
DBConn.CommandTimeout = 120
dbconnstring = "dsn=LocalServer;uid=frederico;pwd=fred1;database=factucli;"
DBConn.Open dbconnstring
DBConn.Execute "delete from paises where pais = 151"

Table has 240 records, and primary key is "pais".

How many records does your table has, and is ID the key (looks like it is).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Anesthaesia]

Thanks Frederico, got it working now and figured out the problem...

The database has 400,000+ records, and ID wasn't set as the key. I didn't check this before as it executed instantly when using Query Analyzer.

The reason this happened is that I had copied a blank table using VB, and the key wasn't set on the target table.

My question now is this: how can I set the primary key after copying a table using VB ? I am copying the table as follows :
sSQL = "SELECT * INTO " & cTable & " FROM srctable"
CreateTable = cn.Execute(sSQL)

 

[fredericofonseca]

That's plain SQL.

First create the table

create table dbo.srctable (
ID numeric(4) not null,
FIELDX char(50) null,
timestamp datetime null default getdate()
)


then add a cronstraint (primary key on this case).
alter table dbo.srctable
add constraint srctable_KEY1 primary key clustered (ID)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Anesthaesia]

Thanks again Frederico,

Works great.

Cheers,
Donovan