help with code used to update multiple records from datagrid 1

[kskinne]

I have the following code in a VB6 project, which is used to update certain fields within selected rows within a datagrid tied to an ADO recordset. We are running into issues where this code seems to run very slowly, taking several seconds just to update one of the records. Does anyone see any issues with this code that would cause it to not run efficiently? Or any suggestions on how to make it run more quickly? One more thing to add, it seems to run slower when there are more results in the data grid, but even when it runs slowly, there are not that many more records. For example it will run very fast when a couple records are updated in a data grid with 50 rows, but will run very slowly in a data grid with 140 rows. Here is the code:

Private Sub cmdMarkSelected_Click()
10    On Error GoTo cmdMarkSelected_Click_Error
20    If cboMarkAs.Text = "" Then
30      MsgBox "You must select the item to be marked as either Paid or Unpaid.", vbCritical, "Paid or Unpaid?"
40      cboMarkAs.SetFocus
50      Exit Sub
60    End If
      Dim i As Integer ' Counter
      Dim cID As Long
      Dim intCount As Integer
      Dim iRet As String
70    intCount = DataGrid2.SelBookmarks.Count - 1
80    If intCount > -1 Then
90      iRet = MsgBox("Mark selected item commissions as " & UCase(cboMarkAs.Text) & "?", vbYesNo, "Mark as " & UCase(cboMarkAs.Text))
100     If iRet = vbYes Then 'Yes mark as either Paid or Unpaid depending on the selection made in the form
110       ReDim arrselbk(intCount) 'Declare array to hold bookmarks.
120       For i = 0 To intCount
130         arrselbk(i) = DataGrid2.SelBookmarks(i)
            'MsgBox ("array bookmark " + Str(i))
140       Next i
          'MsgBox ("Intcount " + Str(intCount))
150       i = i - 1
160       Do While i > -1
            'MsgBox (Str(i))
            'MsgBox (Str(arrselbk(i)))
170         cID = DataGrid2.Columns(7).CellValue(arrselbk(i))
            'MsgBox (Str(cID)) '
180         Adodc3.ConnectionString = glbStrConnect
190         Adodc3.RecordSource = "SELECT paid, datePaid, comment FROM c_CommPaid WHERE ID = " + Str(cID)
200         Adodc3.Refresh
210         If Adodc3.Recordset.RecordCount > 0 Then
220           If cboMarkAs.Text = "Paid" Then
230             Adodc3.Recordset.Fields(2).Value = Trim$(txtComment.Text)
240             Adodc3.Recordset.Fields(1).Value = dtSetPaidDate.Value
250             Adodc3.Recordset.Fields(0).Value = 1
260             Adodc3.Recordset.Update
270           Else
280             Adodc3.Recordset.Fields(2).Value = Trim$(txtComment.Text)
290             Adodc3.Recordset.Fields(1).Value = "1905-06-22 00:00:00.000"
300             Adodc3.Recordset.Fields(0).Value = 0
310             Adodc3.Recordset.Update
320           End If
330           Adodc2.Refresh
340         End If
350         i = i - 1
360       Loop
370     Call Format_DataGrid2
380     End If
390   Else
400     MsgBox "There Are No Items Marked.", vbCritical, "No Items Marked"
410   End If
420   On Error GoTo 0
430   Exit Sub
cmdMarkSelected_Click_Error:
440   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdMarkSelected_Click of Form frmCommPaid - Error on Line number: " & Erl
End Sub

Thank you,
Kevin

 

[Andrzejek]

To be honest, I never use ADODC, I always use ADODB with connection string to deal with the data base.
And Filter works a lot faster than going to DB many times like you have originally done.

With ADODB I would just create a dynamic UPDATE statement and execute it.
Would you be open to use ADODB instead?
It could be a good way for you to find out if you like ADODB...


---- Andy

There is a great need for a sarcasm font.

 

[kskinne]

Andy sorry for the delay in responding - I have no problem in using ADODB instead of ADODC, however I would definitely need some help in re-writing the code to use ADODB instead, including my code in other parts of the project that connect to the server and open the database in the first place. Is this something you could assist me with? Let me know what if anything else you could need me to post.

Thank you,
Kevin

 

[Andrzejek]

I would probably start a small VB6 Project on the side and see if ADODB would work for me.
Start new VB6 Project
Add a Reference to: Microsoft ActiveX Data Object X.X Library
(I use the latest version of 6.1)
Add a standard Module to your Project, and this code to your Module:

Option Explicit
Public Cn As ADODB.Connection

Public Sub OpenConnection()
    Set Cn = New ADODB.Connection
    With Cn
        .ConnectionString = [red]Get connection string to your DB from [b]ConnectionStrings.com[/b][/red]
        .CursorLocation = adUseClient
        .Open
    End With
End Sub

Public Sub CloseCn()
    Cn.Close
    Set Cn = Nothing
End Sub

And what did was:
Add a command button to your Form1, and this code:

Option Explicit

Private Sub Command1_Click()
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    
    Call OpenConnection
    
    strSQL = [blue]"Select SYSDATE From DUAL"[/blue]
    
    With rst
        .Open strSQL, Cn
        
        If .RecordCount > 0 Then
            Debug.Print [blue]!SYSDATE.Value[/blue]
        End If
        
        .Close
    End With
    Set rst = Nothing
    
    Call CloseCn
End Sub

Since I usually connect to ORACLE, my Select statement asks for the system date and time from ORACLE.
But you can use any Select statement to get whatever you want from whatever data base you connect to.



---- Andy

There is a great need for a sarcasm font.

 

[kskinne]

Andy, I apologize for my delay in replying. This should work, but for starters, I need some help on how to populate my datagrid with the data from ADODB recordset. Using ADODC, my code looks like this:

Set DataGrid1.DataSource = Adodc1
DataGrid1.Refresh

What would the correct syntax be using ADODB?

Thanks,
Kevin

 

[Andrzejek]

Assuming you can successfully connect to your DB using proper Connection String (in the code in the Module above), it should be as simple as:

Option Explicit

Private Sub Command1_Click()
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    
    Call OpenConnection
    
    strSQL = "Select WhatEver From SomeTable"
    
    With rst
        .Open strSQL, Cn
        [blue]
        Set DataGrid1.DataSource = rst
        DataGrid1.Refresh 
        [/blue]
        .Close
    End With
    Set rst = Nothing
    
    Call CloseCn
End Sub

---- Andy

There is a great need for a sarcasm font.

 

[kskinne]

Thanks Andy, this works as well - so then using ADODB what would be the proper syntax to update fields in the recordset, in order to update my database? Using ADODC I'm doing something like this for example:

adodc1.recordset.fields(0).value = "some value"
adodc1.update
adodc1.refresh

Thanks,
Kevin

 

[Andrzejek]

Me, myself, I always go straight to the DB to Update stuff.

strSQL = "Update TableA Set FieldX = " & txtSomeText & _
"Where SomeOtherField = " & txtSomeOtherField

Cn.Execute strSQL

This way you don't need to Update record by record, you can Update 1000 records at once if you need to.
Just use the correct WHERE part of the Update statement


---- Andy

There is a great need for a sarcasm font.

 

[kskinne]

Although I wish I could just do this, I don't think this will work, because in my application, the user is manually selecting specfic rows in the datagrid they want to update. If you look back at the code I originally posted, those selected rows are getting bookmarked, and it is those bookmarked rows which are updated. Since the rows can just be randomly selected by the user, the records don't necessarily have one thing in common that I can use to identify just those rows for updating in one SQL statement. I hope that makes sense?

Thanks,
Kevin

 

[Andrzejek]

It would work just fine with ADODB.
You don't HAVE to update 1000 records at once, you can do one record, selected records by user, whatever, no problem.

So let's say you have:
[pre]
c_CommPaid table
ID paid datePaid comment
1 1 2018-08-03 Abcd
2 0 1905-06-22 <NUll>
3 0 1905-06-22 <Null>
4 1 2018-08-01 Xyzx
[/pre]
With the code your have right now:

Adodc3.RecordSource = "SELECT paid, datePaid, comment FROM c_CommPaid WHERE ID = " + Str(cID)
Adodc3.Refresh
If Adodc3.Recordset.RecordCount > 0 Then
  If cboMarkAs.Text = "Paid" Then
    Adodc3.Recordset.Fields(2).Value = Trim$(txtComment.Text)
    Adodc3.Recordset.Fields(1).Value = dtSetPaidDate.Value
    Adodc3.Recordset.Fields(0).Value = 1
    Adodc3.Recordset.Update
  Else
    Adodc3.Recordset.Fields(2).Value = Trim$(txtComment.Text)
    Adodc3.Recordset.Fields(1).Value = "1905-06-22 00:00:00.000"
    Adodc3.Recordset.Fields(0).Value = 0
    Adodc3.Recordset.Update
  End If
...

You new code would look something like:

rst.Filter = "ID = " & cID

If rst.RecordCount > 0 Then
  If cboMarkAs.Text = "Paid" Then
    strSQL = "Update c_CommPaid Set paid = 1, datePaid = #" & dtSetPaidDate.Value & "#, comment = '" & Trim$(txtComment.Text) & "' Where ID = " & cID
  Else
    strSQL = "Update c_CommPaid Set paid = 0, datePaid = #1905-06-22#, comment = '" & Trim$(txtComment.Text) & "' Where ID = " & cID
  End If
  Cn.Execute strSQL
...

And that's just an example.

Of course there are people who would look down on this code. Mainly because of SQL Injection.
But if you can get the gist of it, you can easily convert it and use it with Parameters instead.

BTW, Please, don't think I am pushing you to ADODB, I just want you to know what worked for me for last 20 years or so...


---- Andy

There is a great need for a sarcasm font.

 

[kskinne]

No worries Andy, I think ADODB will actually work great - so for in the testing I've done, using ADODB has eliminated the speed issues I was running into ADODC. I was able to use your ADODB code example to update all the records in my recordset, or update just one single record which I selected in my datagrid. The one item I'm still having issues with is if I select at random certain multiple rows in my datagrid and try to update only those selected rows. Is there a way I can create a new ADODB recordset which only contains the selected rows which I bookmarked in the datagrid? Then I could just ADODB to update all the records in that recordset.

Thanks,
Kevin

 

[Andrzejek]

I was able to […] update just one single record which I selected in my datagrid. "
If you can update one, you can do the same to multiple, random records the same way.

“I select at random certain multiple rows in my datagrid”
I would assume you have some unique qualifier / field (PK?) for the selected records from the grid that would identify particular records in your table you want to update.
So going down the grid:

If the record in the grid is selected for update,
“Update TableA Set FieldToBeUpdated = 123 WHERE PKField = whatever”
Execute this statement.

I usually do not update the recordset, I do the above. I use the values from the grid in place of [tt]123[/tt] and [tt]whatever[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

Maybe an example will help.

Let's say you have 2 tables: [pre]
Table: Status
StaID Description
0 New
1 Active
2 Retired

Table: People
ID StaID FName
12 0 Bob
13 1 Nick
14 1 Susie
15 2 Joe
[/pre]
and you want to show a list of Active (1) people that you want to Update to Retired (2) people.

So you populate your Listbox1 with the query from your DB:
[tt]Select * From People
Where StaID = 1
Order By FName[/tt]

And you show the FNames in listbox1, and you put their ID into ItemData of the Listbox1

You allow user to select which random people should be updated to Retired and you run this code:

Private Sub cmdUpdate_Click()
Dim strSQL As String
Dim i As Integer
Dim strIDs As String

With List1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            If Len(strIDs) = 0 Then
                strIDs = .ItemData(i)
            Else
                strIDs = strIDs & ", " & .ItemData(i)
            End If
        End If
    Next i
End With

strSQL = "Update People Set StaID = 2 Where ID IN (" & strIDs & ")"
Cn.Execute strSQL

End Sub

or you can update one selected (random) record at the time:

Private Sub cmdUpdate_Click()
Dim strSQL As String
Dim i As Integer

With List1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            strSQL = "Update People Set StaID = 2 Where ID = " & .ItemData(i)
            Cn.Execute strSQL
        End If
    Next i
End With

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[kskinne]

Andy, here is the code I used to bookmark just the records I selected in the datagrid, to only update those bookmarked records, identifying them in the SQL update statement by their ID:

Private Sub cmdMarkSelected_Click()
10    On Error GoTo cmdMarkSelected_Click_Error
20    If cboMarkAs.Text = "" Then
30      MsgBox "You must select the item to be marked as either Paid or Unpaid.", vbCritical, "Paid or Unpaid?"
40      cboMarkAs.SetFocus
50      Exit Sub
60    End If
70    intCount = DataGrid2.SelBookmarks.Count - 1
80    If intCount > -1 Then
90      iRet = MsgBox("Mark selected item commissions as " & UCase(cboMarkAs.Text) & "?", vbYesNo, "Mark as " & UCase(cboMarkAs.Text))
100     If iRet = vbYes Then 'Yes mark as either Paid or Unpaid depending on the selection made in the form
110       ReDim arrselbk(intCount) 'Declare array to hold bookmarks.
120       For i = 0 To intCount
130         arrselbk(i) = DataGrid2.SelBookmarks(i)
140       Next i
150       i = i - 1
180       Do While i > -1
190         cID = DataGrid2.Columns(7).CellValue(arrselbk(i))
200         If cboMarkAs.Text = "Paid" Then
210           glbSqlStr3 = "UPDATE c_CommPaid SET Paid = 1, DatePaid = '" & dtSetPaidDate.Value & "', Comment = '" & Trim$(txtComment.Text) & "' WHERE ID = " & cID
220         Else
230           glbSqlStr3 = "UPDATE c_CommPaid SET Paid = 0, DatePaid = '1905-06-22 00:00:00.000', Comment = '' WHERE ID = " & cID
240         End If
250         glbADOConn.Execute glbSqlStr3
260         i = i - 1
270       Loop
280       glbADORS2.Requery
290       Call Format_DataGrid2
300       MsgBox "All Selected Items Marked Paid.", vbOKOnly, "All Selected Items Marked Paid"
310     End If
320   Else
330     MsgBox "There Are No Items Selected.", vbCritical, "No Items Selected"
340   End If
350   On Error GoTo 0
360   Exit Sub
cmdMarkSelected_Click_Error:
370   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdMarkSelected_Click of Form frmCommPaid - Error on Line number: " & Erl
End Sub

This is similar to what I was trying to do with ADODC in my original code, just updated it using ADODB instead and this seems to work well. If however you see a better way of accomplishing the same thing, please let me know.

Thank you very much for your continued help and for sticking with this, I appreciate it.

Kevin

 

[Andrzejek]

All that looks good to me.

I am sure that someone may suggest a better, more 'elegant' way to accomplish the same, but my rule is: "If I can understand the (correctly working) code I am looking at, it is good enough"

With that being said, if somebody offers you a better way, and you say: "It IS better and I like it (and understand it just fine)" - take it.

One very minor thing (you may like it)

...
If [blue]vbYes = MsgBox[/blue]("Mark selected item commissions as " & UCase(cboMarkAs.Text) & "?", _[blue]
    vbYesNo Or vbQuestion Or vbDefaultButton2,[/blue] "Mark as " & UCase(cboMarkAs.Text)) Then[green]
    'Yes mark as either Paid or Unpaid depending on the selection made in the form[/green]
    ....
End If
...

No need to declare and use a variable [tt]iRet[/tt]

BTW - Don't eat too much at the fair

---- Andy

There is a great need for a sarcasm font.