StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Since this was answered in the comments I wanted to turn it into a better answer</strong></p> <p>Your problem seems to be the <code>MyConn.BeginTrans()</code> has no <code>MyConn.CommitTrans()</code> or <code>MyConn.RollbackTrans()</code> after the Insert Statement in your select case; therefore, an error is thrown when you try to update the data. If you commit or Rollback after that insert execute then your next execute should work just fine. The fact that the MyConn.BeginTrans() is before a simple select statement you might consider moving it after the select. </p> <p>I would do something like this (if you want to use transactions):</p> <pre><code>'MyConn.BeginTrans() sql = "Select someReport from MyTable" set rs = MyConn.Execute(sql) do while not rs.EOF TIMESTAMP = rs("TIMESTAMP") rev = rs("REV") select case whatChange case "Target date" MyConn.BeginTrans() sql = "Insert into " & caJustTable & _ " (TEXT, TIMESTAMP, CURRENTFLAG)" & _ " Values ( Text& "','" & COPY_TS & "', 'Y')"" MyConn.Execute(sql) MyConn.CommitTrans() 'You'll want to validate your data inserts properly before committing end select MyConn.BeginTrans() sql = "update table, set this to that" MyConn.Execute(sql) <-------- error happens here sometimes.... MyConn.CommitTrans()'You'll want to validate your data inserts properly before committing end if rs.movenext loop rs.close() set rs = nothing </code></pre> <p>Transactions are generally used for inserting/updating or deleting data. Since you commented you don't know why the BeginTrans() statement is there then yes you could remove it altogether but I would recommend reading up on transactions and making sure you don't need it after your insert and update statements which occur later in the code.</p> <p>Here is a reference for SQL transactions:</p> <p><a href="http://www.firstsql.com/tutor5.htm" rel="nofollow">http://www.firstsql.com/tutor5.htm</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload