Solved

Update Sql Help

Posted on 2002-03-13
7
152 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA to copy paste columns form one file to other 20 100
Adding to a VBA? 6 84
Add a task in Outlook from access 11 43
Need Nag Screen for Reboot if system up time is over 14 days 12 81
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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

740 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