Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

Update Sql Help

Hello Experts:

I have a listview that displays invoice information (Invoice Number, Date, Payment Status, Amount).  When it comes time to post payment to each invoice, a check box should be checked indicating that payment should be posted to this invoice.

Question is, what is the most efficient way to run the update statement to reflect payment on the items that are checked?

Im using vb6 and sql 7

Here is the layout of my table:

tbl_InvoiceTable
---------------------
ExamNumber <---This is Invoice Number
CustomerID
ContactID
InvoiceDate
Status <--- Integer value with the following values 0 = unpaid, 1 = paid, 2 = logical delete.

So if the listview displayed the following information:

ExamNumber   Date     Total
62000        3/10/02  75.00
62001        3/10/02  75.00
62002        3/10/02  75.00

And all three values were checked, whats the best way to update the table so the status on each exam number is updated to 1?

E
0
escheider
Asked:
escheider
  • 3
  • 2
  • 2
1 Solution
 
bruintjeCommented:
Hi eschneider,

You could loop through the listview building a complete record and passing that record to a command object for the update stored procedure or sql statement assuming you use ADO

Is this for a web application ?

:O)Bruintje
0
 
rpaiCommented:
How about using the IN clause? Something like this:-

UPDATE tbl_InvoiceTable
SET Status = 1
WHERE ExamNumber in (62000,62001,62002)        
0
 
escheiderAuthor Commented:
No, this is not a web application, its a desktop application.  

How does one build the complete record to pass it to a command object?  Like a stored procedure?  Please give me an example.

E
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
escheiderAuthor Commented:
I may have up to 500 entries in the listview.  Will the In clause be sufficient with this many updates?  
0
 
bruintjeCommented:
ok don;t have any listview code here, but you can find something on the internet basically it's like this (in pseudocode)

Public sub UpdateAllRecords()
for each item in listview
  get field like
  invnumber=listview.itm
  date=listview.itm
  paymstatus=listview.itm
  amount=listview.itm
  Call UpdateRecord(invnumber, date, paymstatus,amount)
next
end sub


private sub UpdateRecord(Byval invnumber as Long, _
                     Byval date as date, _
                     Byval paymstatus as int, _
                     Byval amount as double)
Dim strSQL as string
Dim adoconn as ADODB.Connection
  set adonconn= adoconnection(i guess you have already an active connection????)

  ' this string will update all examnumbers not on status 1 yet
  strSQL="UPDATE tblInvoice where examnumber=" & invnumber & "and status <> 1"
  ' this will update the row
  adoconn.execute(strSQL)
end sub

got no error handling here because it's a raw sketch

HTH:O)Bruintje
0
 
rpaiCommented:
IN clause can handle 500 entries.
0
 
escheiderAuthor Commented:
Thanks all for the input.  
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now