Missing record!!!
-
Hi
Please help me with this problem
I have a client who is working over a network using sql server.
He exprienced a problem with my program (vb 6, ADO) and when I checked the data I found that the program assumes the exisitence of a speifiec record at the dbo_Clients which has vanished from the database!!!
There is nothing in the code that would delete this record.
but I am suspecting that there are multiuseres who work concurrntly as this is my first time to work with multiuseres. but they mainly read this table!
1. are there any considerations for openning data and connecting to it with the network users?
2. what do you suggest has deleted the record
3. what do you suggest to prevent this problem in the future
many thanks
-
Sameh
This is a tricky one. Firstly SQL_Server handles multiple users very well and you wont get rows deleted as a result of this.
A good place to start would be to create an "audit" table then add a DELETE and UPDATE trigger to the table that is loosing data, that inserts the deleted/updated row into your audit table, along with the value of CURRENT_USER and the date/timestamp. That would give you a good starting point to see when the row is dissapearing, and what who it's relate to.
Hope that helps
Sam
-
Thanks Sam
Well, that's a good suggestion.
I suspected a piece of my delete function which could have deleted the first record not the one meant to be delelted.
But I recognized another thing with that user which actually deletes the record or at least creates a conflit.
This is when two (or serveral) users are opening a new invoice where the I do a select query ordered by invoice number and move to last record and then add 1 to the last number and then I add a new record with that number. I found that both users will be assigned the same number which causes a problem and then I find that the specific invoice vanish.
Any help on this issue?
Thanx in advance
-
How are you generating the new invoice number?
If you're doing it "by hand" then that's a receipe for things going wrong.
I don't know the format of your invoice number but you should consider using an Identity column (either use it directly or use it as a seed for creating the invoice number with). For this you might have to insert a new row without an invoice number, then go back into the row you just created and use it's identity value to generate your number. Use @@IDENTITY (immediately after the insert) to get the one you're jsut created.
Also enclose the complete invoice creation in a transaction so that it definately finishes one before starting on the next one. This might slow things down slightly but without this you're asking for trouble.
Best of luck
Sam
-
hmmm .. advice from Sam, wise indeed.
Normally, a tool of the dark side, Identity is. But use it sometimes you should, guarantees uniqueness it does.
If create invoice number manually you must, do so only when the invoice is saved. NO user interaction there should be, between creation of invoice number and the saving of it! "To Be Determined", you will display to user as he works on it, until added to your table it is.
-
Hmmm... every invoice should have at least 2 numbers - internal ID (assigned using Identity column) and the actual number of invoice (the one that is printed, and sometimes used later to identify the payment). There should be no problem with identity column, but regarding the Invoice number, well - at least in our company all invoice numbers must follow one after another in a row, without interruptions.
Following is based on my experience - I don't know how your system is working, so it may not apply to your problem. It is quite probable, that the user application does not store data immediately into DB as each field is updated by a user, but does so only on demand or when the user is leaving the invoice form. When user opens a new form, your code finds the first free number and puts it into the FORM - without saving it to database. A while later another user starts a new invoice and gets the same number. First of these two, who tries to save the invoice, has no problems; but as soon as the other user tries to save, he/she should receive information "invoice number already in use, new number will be assigned". Then the code should automatically repeat the process of finding first free number and assigning it to the invoice. If your code does not perform such check, then it is quite possibly the source of your problems. Depending on how the entire app (and invoice table) is built, it can happen that the second invoice's save fails, or creates duplicity in invoice numbers.
HTH, Vladan
-
In fact, Yoda's suggestion not to display the invoice number, before the invoice is saved and to hide the entire process from the eye of the tiger... eh... user... is much better than to bother users with infoboxes of the kind I mentioned. Unfortunately, that's how the system I'm administering works, so it was the first thing that came into my mind 🙂
-
I am using vb6 with ado
I was used to use addnew with a recordset
but so I will use somthing like
"insert into dbo_invoice (invoice_id, invoice_date) select max(invoice_id)+1 as new_id, getdate() from dbo_invoice"
what will gurantee that using
rs.open "SELECT @@IDENTITY AS 'Identity'"
will return the Id created by the application current user and not by the some other user??
Offcourse I need for the application to know which one it created in order to open the form with the new id just created!
-
Also , Can you please explain the use of cursortypes and locking in ado and if it can explain in this case
Thanks
-
Sameh
Unless someone else knows an obscure bit if trickery you cannot safely generate your invoice numbers using "in-stream" SQL from VB the way that you have done it. You must do all of this within one operation in a stored procedure.
... and as a general rule you shouldn't be putting SQL statements in VB anyway, as if you have them as stored procedures they are pre-optimised and compiled so will run much more efficently.
Sam
-
I use a tip from Antares to generate my Invoice numbers and use a control record
e.g
UPDATE ControlRecord
SET @InvoiceID = InvoiceID = InvoiceID + 1
WHERE ControlID = 1
SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0 RETURN
INSERT
INTO Invoice
(
InvoiceID,
...)
VALUES
(
@InvoiceID,
...)
SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0 RETURN
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply