Solved

Update Sql Help

Posted on 2002-03-13
7
144 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now