• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

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!
0
andrewl77
Asked:
andrewl77
  • 8
  • 7
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now