Solved

Update Sql Help

Posted on 2002-03-13
7
155 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…
Suggested Courses
Course of the Month9 days, 23 hours left to enroll

624 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