Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

can anyone help me how to use begintrans in ADO with Remote Provider

Hello sir, i am using an access2000 database in my local server and an MSSQL
Server database in net, while data transactions, i 'll be selecting some
records and inserting in my net database, for that i have to use begin trans
with my net server, i have connected to the database with connString ="provider=MS
Remote;dsn='Online';uid='user';pwd='';remote provider='SQLOLEDB.1'"
like wise but f i give db.begintrans i got an error as the provider doesnt
support ... can anyone help me...bcoz, i have to ensure the data transactions....if
anything
goes off, it should be rollback....
with expectations
Karthik.P


0
am_karthik
Asked:
am_karthik
1 Solution
 
dirkmartinCommented:
Place your begintrans on the recordset, not the database.  If you have:
Dim RS as recordset
Dim DB as database

Rather than db.begintrans, do: rs.begintrans
0
 
BahnassCommented:
Not Sure but

db.cursortype = aduseclient
0
 
inthedarkCommented:
If using ADO, transactions are controled through the connection object.

e.g.

Dim CN as ADODB.Connection
Dim SQL as string
Dim OpFailed as Boolean

OpFailed=False
On Error Resume Next

CN.BeginTrans

SQL="Insert Into MYTable (Field1,Field2) Values(10, 20)"

CN.Execute SQL
if Err.Number = 0 Then
   CN.CommitTrans
End If
If Err.Number <> 0 Then
   CN.RollBack
   OpFailed=True
End If

If OpFailed Then
   Msgbox "Transaction aborted"
   exit sub
End if

You can also create tables and do all sorts of things within transactions.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
VincentWongCommented:
If you are using SQL Server 2000, SET XACT_ABORT would be a alterative for BeginTrans.

0
 
am_karthikAuthor Commented:
Hello sir, thanx for your information...but i was using a connection object and i will be connecting to a net server(SQL Server)and i will be checking for a registration tag and i shall be creating tables and inserting values for five tables one by one....so if any disconnection i have to roll back all the transactions....and if i going for registration again... the process should be started from first table...so.. i cant go by ecordset.. instead i have to go with conn.begintrans.
 i am not clear abt your alternate information that is
"SET XACT_ABORT "..can you guide me more...
with thanx and expectations...
karthik.P
0
 
VincentWongCommented:
SET XACT_ABORT

Specifies whether Microsoft® SQL Server™ automatically rolls back the current transaction if a Transact-SQL statement raises a run-time error.

Syntax
SET XACT_ABORT { ON | OFF }

Remarks
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

It is required that XACT_ABORT be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. For more information, see Distributed Queries and Distributed Transactions.

The setting of SET XACT_ABORT is set at execute or run time and not at parse time.

Examples
This example causes a foreign key violation error in a transaction that has other Transact-SQL statements. In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed. In the second set of statements, the SET XACT_ABORT setting is turned ON. This causes the statement error to terminate the batch and the transaction is rolled back.

CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO

SET XACT_ABORT ON
GO

BEGIN TRAN
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (5) /* Foreign key error */
INSERT INTO t2 VALUES (6)
COMMIT TRAN
GO

/* Select shows only keys 1 and 3 added.
   Key 2 insert failed and was rolled back, but
   XACT_ABORT was OFF and rest of transaction
   succeeded.
   Key 5 insert error with XACT_ABORT ON caused
   all of the second transaction to roll back. */

SELECT *
FROM t2
GO

DROP TABLE t2
DROP TABLE t1
GO
0
 
am_karthikAuthor Commented:
Hello Sir... thanx for your information... i shall workit out and get you....once again i thank you....
with Love
karthik.P
0
 
twalgraveCommented:
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- points to VincentWong
Please leave any comments here within the
next seven days.
0
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now