Updating Records Dilema

Hi Experts,

I have a bit of a dilema that i need to come up with a solution between now (Friday night) and monday night.

I have created a system that invoices a series of pays.  Generates a report saying how much to pay each person.  Consequently there is about 1000 records that need to be updated after each payrun.  

I have got a standard datagrid that displays all of the information, however it is used for purely viewing information not editing/updating.  I don't want to have to manually change/type the update.  

My thoughts/options are:
select a group of transactions and do a batch update on the value
find and configure a datagrid that has a check box, then use the check box to update the records

Any thoughts and assist will be greatly appreciated.  Given the urgency of this i am allocation max points!
andrewl77Asked:
Who is Participating?
 
JohnBPriceCommented:
Sorry again for firing off quick replies (working & all that), I didn't notice how you were doing the update

When I said conn.Execute "Whatever your SQL Update is" what I had intended was executing the update statement directly without returning a recordset, such as:

   conn.Execute "update transactions set umppayok= 1 where id = " & trans.ItemData(I)


so the actual update is in the Execute.  Using Execute to return a record is always read only.  If you want a writable recordset, you must use the recorsets Open method

    rs5.Open "Select * from transactions where id = " & trans.ItemData(I), conn, adOpenDynamic, adLockOptimistic

and THEN you could do

    rs5!umppayok = 1
   
    rs5.update


(also note the syntax for execute returning a recordset is Set recordset = connection.Execute (CommandText, RecordsAffected, Options), so you have adCmdText in the wrong parameter position)
0
 
JohnBPriceCommented:
Assuming you are picking some individuals to process but not all, "select a group of transactions" is easier on the user, you don't have to click on each of up to 1000 records.

In addition: Once they process a batch, refresh the screen.  Exclude (or at least sort and indicate) which records don't need processing so the user sees the feedback.  Add a check in the code to ensure you do not double process a persons pay, unless it is your own :-)

0
 
andrewl77Author Commented:
Hi John,

Thank you for steering me in this direction.  I have created a form with 3 controls for my criterias.  The first one allows the user to select the name of the person (this is a combo box).  Once this is selected, shows a list of paydates against the invoices (this too is a combo box).  Once the paydate has been selected.  Once this is done it display all of the transaction to be updated in a list box.  It also does displays the total of the transactions in a std field.

My code looks like this:

Private Sub Form_Load()
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
     
    Dim rs1 As ADODB.Recordset
    Set rs1 = New ADODB.Recordset
   
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "DSN=umas;database=umas;uid=umas;pwd=umas"
    conn.Open
   
    rs1.Open "select transactions.umpireno, (select displayname from umpire where transactions.umpireno = umpire.umpireno) as umpname from transactions where umpireno <> 0 group by transactions.umpireno order by umpname", conn, adOpenDynamic, adLockOptimistic
   
    Set Me.umpire.DataSource = rs1
    Do Until rs1.EOF
    Me.umpire.AddItem rs1!umpname & ""
    Me.umpire.ItemData(umpire.NewIndex) = rs1!umpireno
    rs1.MoveNext
    Loop
   
    rs1.Close
   
    conn.Close
    Set conn = Nothing
End Sub

Private Sub paydate_Click()
    trans.Clear
   
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
     
    Dim rs3 As ADODB.Recordset
    Set rs3 = New ADODB.Recordset
   
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "DSN=umas;database=umas;uid=umas;pwd=umas"
    conn.Open
   
    rs3.Open "select id, umpireno, invoiceno, transtype, umppay, umppayok from transactions where transactions.umppayok = 0 and transactions.paydate <= " & Format(paydate.Text, "yyyymmdd") & " and transactions.umpireno = " & umpire.ItemData(umpire.ListIndex), conn, adOpenDynamic, adLockOptimistic
   
    Set Me.trans.DataSource = rs3
    Do Until rs3.EOF
    Me.trans.AddItem rs3!transtype & " - " & Format(rs3!umppay, "$#.00") & " - " & rs3!invoiceno
    Me.trans.ItemData(trans.NewIndex) = rs3!id
    rs3.MoveNext
    Loop
   
    rs3.Close
   
    Dim rs4 As ADODB.Recordset
    Set rs4 = New ADODB.Recordset
   
    rs4.Open "select id, umpireno, invoiceno, transtype, umppay, umppayok, Sum(transactions.umppay) as sumumppay from transactions where transactions.umppayok = 0 and transactions.paydate <= " & Format(paydate.Text, "yyyymmdd") & " and transactions.umpireno = " & umpire.ItemData(umpire.ListIndex) & " group by umpireno", conn, adOpenDynamic, adLockOptimistic
   
    Me.transtotal = Format(rs4!sumumppay, "$#0.00")
   
    rs4.Close
   
    conn.Close
    Set conn = Nothing
End Sub

Private Sub umpire_Click()
    paydate.Clear
   
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
     
    Dim rs2 As ADODB.Recordset
    Set rs2 = New ADODB.Recordset
   
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "DSN=umas;database=umas;uid=umas;pwd=umas"
    conn.Open
   
    rs2.Open "select transactions.paydate from transactions where umppayok = 0 and transtype = 'Match Payment' AND umpireno = " & umpire.ItemData(umpire.ListIndex) & " group by paydate", conn, adOpenDynamic, adLockOptimistic
   
    Set Me.paydate.DataSource = rs2
    Do Until rs2.EOF
    Me.paydate.AddItem rs2!paydate
    rs2.MoveNext
    Loop
   
    rs2.Close
   
    conn.Close
    Set conn = Nothing
End Sub


I am upto the point of doing a batch update of the transactions.  I want to change the value of umppayok from 0 to 1 in the transactions table.  How would I acheive this with 1 set of code?  I have never written a batch update code before in vb6.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JohnBPriceCommented:
I don't understand, it looks like you have got it.  You simply need to process Me.trans, the assembled list of umpire-paydata, and make the database entries.  Do you mean how can you apply the entries in one DB statement, perhaps for transactional control?  I would do the transaction control explicitly, like this:

conn.BeginTrans
For I = 1 to Me.Trans.Count
    conn.Execute "Whatever your SQL Update is"
Next
conn.CommitTrans

You could build one humongous SQL by assebling all the items into a list, but you run the risk of blowing out some max SQL length constraints not to mention building a SQL statement that would be hard to debug.
0
 
andrewl77Author Commented:
John,

i have written the code like this...i hope i am but don't think its right.  I get an error at this point.  The error is a compile error, method or data member not found

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
     
    Dim rs5 As ADODB.Recordset
    Set rs5 = New ADODB.Recordset
   
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "DSN=umas;database=umas;uid=umas;pwd=umas"

    conn.BeginTrans
    For i = 1 To Me.trans.Count <== error here.
   
    Set rs5 = conn.Execute("Select * from transactions where id = " & trans.ItemData(trans.ListIndex), adCmdText)

    rs5!umppayok = 1
   
    rs5.update
   
    Next
    conn.CommitTrans
   
    conn.Close

0
 
andrewl77Author Commented:
I have progressed.  

The code looks like this but gives an runtime error 381 invalid property array index

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
     
    Dim rs5 As ADODB.Recordset
    Set rs5 = New ADODB.Recordset
   
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "DSN=umas;database=umas;uid=umas;pwd=umas"
    conn.Open
   
    conn.BeginTrans
    For i = 1 To Me.trans.ListCount
   
    Set rs5 = conn.Execute("Select * from transactions where id = " & trans.ItemData(trans.ListIndex), adCmdText) <= run time error here

    rs5!umppayok = 1
   
    rs5.update
   
    Next
    conn.CommitTrans
   
    conn.Close
0
 
JohnBPriceCommented:
What is Trans?  A list box?  Perhaps it is zero-based, I always forget what is zer0-base and what is one-based, What I usually do is break and try (0) and (1) and see which is correct.  In that case, you want:

For i = 0 To Me.trans.ListCount -1
0
 
andrewl77Author Commented:
trans is a list box.
0
 
andrewl77Author Commented:
no good...tried i = 0 and i = 1 still getting the error at the point of

Set rs5 = conn.Execute("Select * from transactions where id = " & trans.ItemData(trans.ListIndex), adCmdText)

0
 
JohnBPriceCommented:
Oh, sorry, I missed it the first time, try

Set rs5 = conn.Execute("Select * from transactions where id = " & trans.ItemData(I), adCmdText)

It is I you are iterating, not the listindex, which might very well be -1 if no records were selected.
0
 
andrewl77Author Commented:
Now getting runtime error 3251:

Current recordset does not support updating.  This maybe a limitation of the provider or of the selection locktype
0
 
andrewl77Author Commented:
hmm...now getting runtime error 3705.  Object is not allowed when object is open.  What would this mean?
0
 
JohnBPriceCommented:
What line ?  Sounds like you are trying to open a recordset or connection that is already open, perhaps there is a missing rsxx.Close?
0
 
andrewl77Author Commented:
Thanks john.  You are a legend!

I will post the full code shortly for anyone else and future reference.
0
 
JohnBPriceCommented:
Your welcome.  Thanks for the A
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.