I am loosing data need help

Hi
I am using ASP as frontend ans MySQL as backend, mine is an intranet portal basically a calltracking system, the server is on NT 4.0 and client side is Windows XP.
I am storing atleast 1000 records a day. but i dont know why but i m loosing some data means the data is not getting stored properly.

I need help because I m unable to detect the issue, if some one wants a bit more info abt the issue I will provide but I need assistance so plz help me

any help will be appreciated
thanx in advance
Rahul

You need to be a lot more specific.

What data are you loosing?
Is it consistent or random etc.

Are you using ADO to access MySql?

There seems to be a bug in ADO drivers when accessing tables that have large text fields. Data goes missing.

You need to send some code and explain exectly what you are doing step by step

thanx for replying

I m providing you the full information

this is the structure of my main table


CREATE TABLE `casemain` (
`CaseID` varchar(15) NOT NULL default '',
`Owner` varchar(10) NOT NULL default '',
`TotTime` int(11) default '0',
`createdate` date default NULL,
`startdate` date default NULL,
`IssueClass` varchar(35) default NULL,
`OS` varchar(25) default NULL,
`LastModDate` date default NULL,
`TL` varchar(25) default NULL,
`counter` int(11) default NULL,
PRIMARY KEY (`CaseID`),
UNIQUE KEY `CaseID` (`CaseID`)
) TYPE=MyISAM

this is the structure of my detail table


CREATE TABLE `caseinfo` (
`CaseID` varchar(15) default NULL,
`ReplyType` varchar(50) default NULL,
`Owner` varchar(10) default NULL,
`NoofInter` int(11) default NULL,
`Timetoday` int(11) default NULL,
`TotalTime` int(11) default NULL,
`LastModDate` date default NULL,
`StartDate` date default NULL,
`Type` varchar(20) default NULL,
`IssueClass` varchar(25) default NULL,
`Datecustrep` date default NULL,
`OS` varchar(25) default NULL,
`dispatchq` varchar(30) default NULL,
`stime` datetime NOT NULL default '0000-00-00 00:00:00',
`etime` datetime NOT NULL default '0000-00-00 00:00:00',
`CaseStatus` varchar(35) default NULL,
`DispDate` date default NULL,
`TL` varchar(25) default NULL
) TYPE=MyISAM


I am also attaching three files named ISS.asp, ReplyNew.asp and ReplyOld.asp, I m getting
the caseid in Iss.asp and checking it that whether it is present in casemain, if not i m displaying replynew.asp
else replyOld.asp

I m facing severe issues of data loss, means the data is not getting stored in caseinfo, and sometimes it
replecate the same data many times(as many as 5000).
this issue is not very frequent but it is occuring i know that and i m unable to find this why?
and i m not in the position to loose data, so i have to solve this issue ASAP

any help will be greatly appreciated

thanx in advance
Rahul

I have had a look at the ISS.asp file so far, and noticed a couple
of syntax problems..

You'll see where I have commented the file.
See if this helps, I'm endeavouring to look at your other files, but I'm at work at the moment, so time is not available.

There doesn't seem to be anything wrong with the ReplyNew.asp at first glance.
I'll look at more later, but in the meantime, here's some suggestions for your file (all are commented in the attached file)

thanx for reply mohecan
i will try thing given by you
but plz try to analyze it a bit more
i m developing this application alone and a bit new to asp
thanx again for considering my problem
rahul

I haven't had time to look at you code.

But try changing the order of the columns in the sql query ie.
if you have "select a,b,c from tblx" try changing it to "select c,b,a from tblx"

It shouldn't make a differenece, but if it does you know something is really broken. I have had this happen to me.

hello mohecan,
Thanx

I am not locking the table while submitting the data, can it be the problem, if it can be can you tell me how to lock the table of my sql in ASP.
I have implemented the things which you have written in the file but I am still not sure that whether it will resolve the issue or not bcoz I really dont know when will this issue happens, I m attaching the files again with this mail.
Waiting and hoping for your reply.
Regards
Rahul

hello mohecan,
I m still waiting for the reply
Rahl

Please don't push and/or demand people on this forum to fix
things for you.....

This is a forum based on volunteer contributions, and we (the
community) are under no obligation to fix problems.... We are here to help where we can

Like I have said, I will try to look at, and solve, problems where I
can.... I understand that it is critical that you don't loose data, but
understand too that I have a job to do, and that demands my
attention.

With that said ......

Have you had look ngibsonau's post regarding ordering of your
columns yet?

Also, this happened to me yesterday, regarding form
submission's. Is the form you are submitting dynamically
generated? If so, are you calling the right form elements for your
values? I thought I was in my form, and I was loosing data, but
on closer inspection, I wasn't calling the right element.

I'm still trying to look at your code, but I've had a few things
come up at work that have stripped me of any free time

hello mohecan,

I really apologise for forcing you for the reply. I must understand that you will have to do other things also.

I have tried to reorder the sequence but, as i told you i dont know when this issue occurs.

if you know how to lock the mysql table when submitting the record, plz tell me. i have that idea that it might be happening becoz of concurrent transaction, i m using the myisam type of table in mysql.

once again I deeply apologise to disturb you with my idiotic comments.

Thank you

Rahul

Good idea, I've found some info that might help you, using ADO you can test the state (i.e. open,closed,executing etc) with State property.

Try this DevGuru page, it may give you some more ideas.

Maybe you can test for "if executing then wait".

You can also use batch updating procedures that might help.

See how you go.....

PS - No problem on the comments and requests, it might be just as much my interpretation of what you're saying.

Hello mohecan,

Thank you for the suggesion, i read that article and it was usefull for me to clear something.

it has given me a bit more clear picture bt why this issue is happening.

I m explaining it to you, see that if you can help me out.

1 it takes time in nano seconds for updating and iserting the record in the table.

2 there are very less possibilities for concurrent transaction to occur bcoz only some 20 people are submitting the record.

3 and third most important thing is that this issue occurs very rarely like once in a week sort of

4 so there is very much possibility of that this issue is occuring becoz of concurrent transaction.

I know that oracle provides self locking facility while the transaction, but i m not so sure abt MySQL, that's why i m hunting for a code in ASP that locks the table while the transaction.

hope you can help me out of this mess, I m also hunting for the code.

Thank you once again
Rahul

Will it be fine if I use
Conn.Mode=Conn.Mode = adModeReadWrite
and I will close the connection after i Submitt the record
Rahul