SQL CE Transaction Problem

From: Jonathan (Jonathan_at_discussions.microsoft.com)
Date: 06/20/04

  • Next message: Michael J. Salamone [eMVP]: "Re: Whats the title bar's rec" 
    Date: Sun, 20 Jun 2004 13:08:01 -0700

    I am trying to start a transaction and I am getting this error:
    BeginTransaction requires an open and availible Connection. The connection's current state is Open, Fetching.

    I have executed previous SQL statements, but they should have been completed several lines before I try to start the transaction. Here is my code to start the transaction:

    'start the transaction
            Try
                i = 0
                SQLCmd = New SqlServerCe.SqlCeCommand
                i = 1
                SQLCmd.Connection = SqlConn
                i = 2
                trans = SqlConn.BeginTransaction
                i = 3
                SQLCmd.Transaction = trans
            Catch ex As Exception
                MsgBox("Error with trans " & i & vbCrLf & DBOpen)
                MsgBox(ex.Message)
            End Try

    SQLConn is a global varialbe that I am using for my connection. I thought that this was the best way to do it. Can SQL CE databases have multiple connections open at once?

    Also, what is the maximum number of queries that can be part of a transaction? My program could possibly add about 32,000 for a transaction. Do I need to break it into smaller peices? Thanks for the help.

  • Next message: Michael J. Salamone [eMVP]: "Re: Whats the title bar's rec"

    Relevant Pages

    • Re: DataReader and Transactions
      ... They are being done on the server.. ... >> BeginTransactionfunction on my connection object). ... i need to execute more SQL statements. ... >> But then, to keep my transaction processing, i'd have to create a new ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Working Transactions somehow started not to work
      ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
      (microsoft.public.access.adp.sqlserver)
    • Setting Locks on Rows in MSSQL Database (Currenlty Unsolvable Problem and Trying)
      ... I am beginning a transaction from PHP and setting a lock on a record ... I then commit the transaction I started when the user clicked ... There is no way in PHP where I can start a persistent ms sql connection ...
      (alt.php)
    • TransactionScope Transactions not commiting
      ... be) using the same SQL Connection. ... and dtermines the Connection String to use to create the connection to ... MyClass obj = new MyClass; ... not involved in a Transaction. ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: DataReader and Transactions
      ... > BeginTransactionfunction on my connection object). ... > Inside of this loop, i need to execute more SQL statements. ... > But then, to keep my transaction processing, i'd have to create a new ...
      (microsoft.public.dotnet.framework.adonet)