StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Well you can greatly reduce your SQL work here.</p> <pre><code>If Not IsNull(rs("value1")) Then testArray = Split(rs("value1"), ",") rs2.Open "SELECT Date FROM TBL_TestTable WHERE Record_URN = '" & testArray(1) & "'", objSystemCon.ActiveCon, adOpenStatic, adLockReadOnly If rs2.EOF Then End If If Not rs2.EOF Then rs("DateField") = Format$(rs2("Date"), "dd mmm yy h:mm:ss") rs.Update intWriteCount = intWriteCount + 1 End If rs2.Close </code></pre> <p>You're essentially, it looks to me(I haven't used VB6 & ADO in 10 years), loading up your record initial recordset, checking a value, and if that value is not null running a second select THEN updating the recordset.... You can instead of doing all this just create a command object Declare these before your loops <code>dim objComm</code> <code>set objComm = Server.CreateObject("ADODB.Command")</code> </p> <pre><code>objComm.ActiveConnection = objSystemCon.ActiveCon 'I think this is your connn. objComm.CommandType = 1 'adCmdText </code></pre> <p>Use this in your loop</p> <pre><code>objComm.CommandText = "UPDATE DateTable SET DateField = (SELECT Date FROM TBL_TestTable WHERE Record_URN = '" & testArray(1) & "'") objComm.Execute </code></pre> <p>Rather than doing a 2nd discreet select, pulling the data in, then doing an update and pushing it back out just push out an update statement. This should speed up the processing of your records.....I know i used to write stuff in VB6 like this a long while back :)</p> <p>So your code should now read like</p> <pre><code>dim objComm set objComm = Server.CreateObject("ADODB.Command")` objComm.ActiveConnection = objSystemCon.ActiveCon 'I think this is your connn. objComm.CommandType = 1 'adCmdText rs.Open "select reference,value1,datefield from datetable where field1 = 'value1' " & _ "order by reference", objAuditCon.ActiveCon, adOpenStatic, adLockPessimistic Do While Not rs.EOF intReadCount = intReadCount + 1 DoEvents If Not IsNull(rs("value1")) Then testArray = Split(rs("value1"), ",") objComm.CommandText = "UPDATE DateTable SET DateField = (SELECT Date FROM TBL_TestTable WHERE Record_URN = '" & testArray(1) & "'") objComm.Execute End If rs.MoveNext Loop rs.Close </code></pre> <p>your select statement is still there as you can see, it's a sub select now, the advantage being huge, you're not drawing records to the server, then updating them. You're sending the server a statement to do the updating. You're cutting your trips in half.</p> <p>Hope this made sense.</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