Solved

Update Sql Help

Posted on 2002-03-13
7
148 Views
Last Modified: 2010-05-02
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
Comment
Question by:escheider
  • 3
  • 2
  • 2
7 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6861756
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
 
LVL 5

Expert Comment

by:rpai
ID: 6861997
How about using the IN clause? Something like this:-

UPDATE tbl_InvoiceTable
SET Status = 1
WHERE ExamNumber in (62000,62001,62002)        
0
 
LVL 4

Author Comment

by:escheider
ID: 6862027
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 4

Author Comment

by:escheider
ID: 6862093
I may have up to 500 entries in the listview.  Will the In clause be sufficient with this many updates?  
0
 
LVL 44

Accepted Solution

by:
bruintje earned 25 total points
ID: 6862197
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
 
LVL 5

Expert Comment

by:rpai
ID: 6862266
IN clause can handle 500 entries.
0
 
LVL 4

Author Comment

by:escheider
ID: 6877181
Thanks all for the input.  
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question