Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <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 = '" &amp; testArray(1) &amp; "'", 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 &amp; 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 = '" &amp; testArray(1) &amp; "'") 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' " &amp; _ "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 = '" &amp; testArray(1) &amp; "'") 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>
    singulars
    1. This table or related slice is empty.
    1. POVB6 Recordset update
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJordan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COJordan, I realise there are better ways of writing the code. However, the original question asked specifically about why the record count keeps incresasing by 1 record every few seconds and then reducing by 1 record immediately. I am establishing the record count by running an SQL query in SQL Studio Manager. Thanks.
      singulars
      1. PO
      1. USw0051977
    2. COYeah I realize that sorry, just had to take the opportunity to make the suggestion. There must be something else causing it. Do you have an index on the table? Perhaps the constant updates are causing the index to return some slightly divergent value. I would suspect there is NOT an extra row being created. If you really believe there is an extra row I suppose you could copy the table to another one with all the data and run a query to pull out ones that exist on the processing table vs the static backup copy. This would be very slow and I don't think it would show anything.
      singulars
      1. PO
      1. USJordan
    3. COThanks Jordan. I have been thinking about this all day. I have run some tests on the data and although it returns more rows when the program is running; it does not return any rows when I attempt to run a comparison i.e. select * from datetable where reference not in (select reference from datetabletest). The table datetabletest was created before I run the program. When I add a Primary Key the problem is no longer there i.e. the count is consistent. Do you have any other ideas?
      singulars
      1. PO
      1. USw0051977
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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