|
My application tracks the amount of time that one of my company's agents spends on a particular project. Everything is fine and dandy until it comes time to compile a bill. The application query's the SQLServer database for all records whose 'BillID' is 0, meaning that it hasn't been billed yet (when a record is billed, it'll get a new BillID). The application uses this disconnected recordset to build an Excel spreadsheet. The third and final step of the billing process is to go back and update the 'BillID' fields of the billed records.
This should work 99.9% of the time. Where this plan fails is if another agent adds a new record to the database in the middle of the billing cycle. For example, a record might be added during the Excel phase (if you've ever done anything with the Excel Object Library, you know that it takes a few seconds to build a substantial report). When the application goes back to update the database, the new record will have a 'BillID' of 0, since it's a new record and hasn't been billed, and will thus be updated with a new 'BillID'. This is bad, because the new record hasn't really been billed (it wasn't added to the report). The whole process works like this in summary:
1. Get disconnected recordset containing all records whose 'BillID' is 0
2. Compile an Excel Spreadsheet based on this recordset
3. Set all records whose 'BillID' is 0 to a new non-zero number (here is where a record might be mis-billed)
My solution for the time being was to add a couple of methods to my 'Update' COM Object (I have two objects, Query and Update) called BeginBilling and EndBilling that look like this (in VB):
Public Sub BeginBilling()
GetObjectContext.DisableCommit
End Sub
Public Sub EndBilling()
GetObjectContext.EnableCommit
End Sub
I call BeginBilling at the start of the billing process to prevent MTS from closing the transaction (some what wrapping the Excel procedure in the transaction), and then calling EndBilling when everythings has completed. This seems somewhat hoky to me, though, and thus, the posted question.
Jamie Nordmeyer
Portland, Oregon, USA
|
|
|
|
|
Just a suggestion here.
Instead of retreiving all the rows with BillID = 0, initially update all rows with BillID = 0 but set the BillID to something like -1. Then retreive all rows with -1, produce the report and the update the -1 BillID to the new non zero positive value.
Alternatively, if you can add a new column to the table to indicate processing status or state, that would be the most desirable solution.
Chris
|
|
|
|
|
Good idea, Chris. Thanks! 
Jamie Nordmeyer
Portland, Oregon, USA
|
|
|
|
|
How the heck did I get here? There seem to be eddies in the Space-Time continuum.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
|
Sorry I can't help with specific SQL Server stuff, but in an Oracle database I would do something like the following;
create or replace procedure FindBillingID
as
cursor BillingID /* define your Recordset here */
begin
for BillRec in BillingID cursor loop
uspBillClient(BillRec.BillingID);
end loop;
end FindBillingID;
The uspBillClient is then pretty much as you wrote except that it's IN parameter is of a different type and it get's repetitively called.
Hope this is kinda what you are looking for.
Chris
|
|
|
|
|
http://www.csharpindex.com/articles/2001/01/MiddleTier.htm
|
|
|
|
|
I am writing a VB app (called FISWDC) with a data environment (called DataEnv). I
believe I should be able to do:
DataEnv.Connection.connectionString = "File Name=" & App.Path & "\FISWDC.UDL;"
or, at least
DataEnv.Connection.connectionString = "File Name=FISWDC.UDL;"
or maybe
DataEnv.Connection.Open "File Name=" & App.Path & "\FISWDC.UDL;"
or something like that, but whatever I do I just keep getting the error
"The parameter is incorrect"
or
"Connection was denied. New connection you requested has different characteristics than the one already in use."
Can anyone help?
Dave
|
|
|
|
|
Have you double-checked the UDL-files contents?
Try using the UDL on a clean connection object:
Dim cnn As New ADODB.Connection
cnn.Open "File Name=c:\test\file.udl"
|
|
|
|
|
Hi,
I'm looking for a young SQL programmer that would be interested to work on a new exciting project. Should speak german or french.
if you're interested, please drop me an email: pierre@runser.com
Thanks,
Pierre 
|
|
|
|
|
As a former (and current) UNIX programmer, I am finding myself in a state of confusion when it comes to DB programming in windows. In UNIX I get the libraries I need to talk to the database and use SQL statements to do it all. I connect, query, then parse the results. I'm looking for something as simple to do un Windows. If it helps, I'm looking for something very php (www.php.net) like in syntax: connect(params), execute(sql statement), fetch_array(results), disconnect(). Most important no note that the fetch_array returns tabel data as record=fetch_array(query_result); record[field]="some val". Alternative to that is fetch_row which you can use the index (column number) of the field to get that value. Is there anything like that in Microsoft? Do I have to use classes of CDatabase and CRecordset? It looks like I have to jump through a lot of hoops to get something simple working.
Secondly, what are the installation requirements for the particular methods? Must I always require Microsoft Access be installed? Under UNIX I can make such a requirement because there always is a UNIX admin handy to install it. But on a PC I cannot make that assumption. I have to assume that any inexperianced user can handle it. Which also raises the question, how do I initialize a database under windows? Could I use hard coded SQL statements?
Thanks in advance
|
|
|
|
|
Take a look at http://download.microsoft.com/download/vc60pro/sample/1/WIN98/EN-US/Adovc1.exe
It's a simple sample using the #import directive, which makes it pretty easy to use ADO to do all the stuff you want...
Simply include MDAC in your setup's, it can be downloaded from http://www.microsoft.com/data/
All database drivers are included in MDAC, so as long as you are using Access databases or SQL Server, you know the drivers are there...
Hope this helps.
- Anders
|
|
|
|
|
Given 2 machines, one running IIS, the other SQLServer, with connections to SQLServer via named pipes (as opposed to TCP/IP) on a LAN, is there any way to determine why a connection to the SQLServer from the Web server would be failing at random times?
The error messages reported by SQLServer and IIS are less than inspiring. Just wondering if anyone else has come across this random fail situation before.
cheers,
Chris Maunder
|
|
|
|
|
Just chage the client on both machines to use TCP instead of Named Pipes and the problem is gone (I hope)
Well, at least, if the errormessages is something about a "Overlapped Result" it works...
- Anders
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
