Almost always when presenting data you will have to supply two essential
elements to your user interface: header search and detail presentation.
Supply criteria to the user to search through all those invoice headers,
like date, open/closed status, vendor, etc. (I typically supply a default
condition of showing only the top 300 rows if no search condition is
provided, sorting by create date descending when applicable). When the user
clicks on a row, have another section of your form load the invoice details
for that particular header.
If you still want to show invoice details based on a search (and not an
individual header), the query should look something like this:
SELECT TOP 300 d.*
FROM InvoiceHeader h
INNER JOIN InvoiceDetail d
ON d.InvoiceID = h.InvoiceID
WHERE
h.InvoiceDate = <your date variable>
AND <any other search conditions>
ORDER BY h.InvoiceID, d.InvoiceLineNumber
I put in the TOP 300 because users don't want to page through a large result
set - that's what a search feature is for. You can tell your users in a note
on the form that the result set has been truncated when necessary ("only the
top 300 search hits shown"). If a user really does want to see all 200,000
rows, this is a report, something to be printed; not a form. As such I never
user a datareader, because my queries never return large result sets.
As a side note, I don't recommend having any SQL at all in compiled code,
use stored procedures if you can; they supply a useful layer of abstraction,
boost performance, and allow you to make DB changes without having to
recompile your deployment. Also, take some time to buy a book or two on SQL
and really learn it. It will really make a big difference.
HTH,
Bob
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
this time I understand . But I got another question. in my Invoiceheader,I
can load the current month's data
but .. in my invoicedetail, there is no field to store the"date"
How can I select from invoicedetail with the "date condition" ....
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader from
month = currentmonth;select * from invoicedetail where ???, conSea)
thanks
"Tom Dacon" <td****@community.nospam> ¦b¶l¥ó
news:ui*************@TK2MSFTNGP11.phx.gbl ¤¤¼¶¼g... I'll just bet that it does seem quite slow. You're bringing over a ton
of data, little of which the user is ever actually going to be looking at.
So...how about not loading the whole works all at once? Maybe you might
load just the most recent data, perhaps enough to fill your grid or whatever
you're using, and then wait until your user wants to 'page back' or do a
filtered search on records, using some user-specified criteria. Then go
back and load the data of interest. I know it's a little more work, but
nothing comes for free.
No offense intended, but this is kind of like those people who add
100,000 items to a drop-down list box and wonder why it doesn't work too pretty
good.
Trying hard not to be irritable and not quite succeeding,
Tom Dacon
Dacon Software Consulting
"Agnes" <ag***@dynamictech.com.hk> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl... In my form load()
Dim daSeaInvoice As New SqlDataAdapter("select * from InvoiceHeader
;select * from invoicedetail, conSea)
dim myDataSet as new dataset()
daSeaInvoice.fill(mydataset).
As I got over 50000 records in invoiceheader and over 200000 record in
invoicedetail
It seems quit slow to load the form...
