Using a SQL Transaction in VB6 1

[cturland]

Hi all,

I'm trying to use an SQL Transaction in my VB module. I need the transaction to build a database, for example:

BEGIN TRANSACTION
CREATE TABLE dbo.Table1
{
Field_Name char(10) NULL
}ON [PRIMARY]
GO
COMMIT

How do I run this from VB? I've tried using it like an SQL statement but it is not recognised. Anyone have any ideas?!

Rgds,
Carl

 

[CybOrg]

The 'Transaction' is just a way to 'package' syntactically correct database updates, in order to reduce network traffic, and to gain a chance to roll back a sequence of updates. So - it's not the 'transaction' that does the job - it's the SQL. Start in a database query window and get the SQL syntactically correct, then use that SQL in your VB app. There are some restrictions on what you can do inside a transaction, I think, but I can't think of them off hand...
One way to solve your problem would be to code a stored procedure in SQL Server, and execute the proc from within your VB app - efficient and snappy.

 

[chiph]

Try using the BeginTrans, CommitTrans, and RollbackTrans methods on the ADO Connection object.

BTW, if you're running inside MTS or COM+, you need to use the transaction methods provided by those api's, not the one on the ADO api.

Chip H.

 

[cturland]

Sorry everyone!! I obviously wasn't being too clever when trying to sort this problem out - I realise now that I just had to get rid of the transaction around the SQL to get it to work.

Using the following in an ADO connection worked fine!:

CREATE TABLE dbo.Table1
{
Field_Name char(10) NULL
}ON [PRIMARY]

Cheers,
Carl