I insert a record through an asp command object, then manually deleted it

from the Access db, then save the db.



Here's the wierd part:  then I can't insert the same record again unless I

compact the database first.  Nor can I insert a similar record (same fields

and table).



My other pages and tables don't act like this.



I haven't found other effects after step 1.  I can still read and write

records to other tables in the db through other pages without the compacting

step.



Any one have a cure for this?  Or explain the cause?



Happy Trails,



Mike

Try closing the  Access Table before running the code.

Then open the table and check your results.

Then check your multiuser db settings



----- Original Message -----

From: "Michael Goldman" <mg188@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, January 19, 2001 8:22 PM

Subject: [asp_databases] database has the flu?





> I insert a record through an asp command object, then manually deleted it

> from the Access db, then save the db.

>

> Here's the wierd part:  then I can't insert the same record again unless I

> compact the database first.  Nor can I insert a similar record (same

fields

> and table).

>

> My other pages and tables don't act like this.

>

> I haven't found other effects after step 1.  I can still read and write

> records to other tables in the db through other pages without the

compacting

> step.

>

> Any one have a cure for this?  Or explain the cause?

>

> Happy Trails,

>

> Mike

>

Hi, I tried your suggestions with no luck.  I'm working in PWS with no

network, so would multiuser settings have any affect?  But I reduced the

timeouts and intervals and set response.expires = 0 in the processing page

and tried again.  No difference.



I think I've isolated the problem a little.  I split 2 strings, then run 'em

thru a loop that trims each value, then tries to insert value pairs.  These

strings are normally built from a form on the previous webpage, but this

time I made

dummy strings on the processing page and saw the same behavior.



str1="ASDF1, ASDF2"        'first dummy string

str2="02/02/02, 03/03/03"    'second dummy string



The behavior:  I process the page once and 2 records show up fine.  I

process the page a second time, and the browser goes blank.  Now there are 3

records in the db.  The 3rd record is ASDF1, 02/02/02.  After that, repeated

page-processing shows a blank browser with no furthur change in the db.  If

I delete these 3 records, then compact the db, I can repeat this with the

same results.  Same results whether the db is open or not.  Same results if

I close the browser between processing attempts.  Same results if I reboot

first.



So I'm guessing something in the loop hangs the processing, or something in

the db stops the process.



Code for the loop:



Set objCon = Server.CreateObject("ADODB.Connection")

objCon.Open "DSN=mg53rev1"

set objcmd = server.createobject("adodb.command")

set objcmd.activeconnection = objcon

objcmd.commandtype = adcmdtext



str1="ASDF1, ASDF2"    'the first dummy string

str2="02/02/02, 03/03/03"    'the second dummy string

x=0

do while not x=2

split1=split(str1,",")

split2=split(str2,",")

strins="insert into tbl (field1,field2"

varstr1=cstr(trim(split1(x)))

varstr2=cstr(trim(split2(x)))

strval="values('"&varstr1& "','"&varstr2&"'"

objcmd.commandtext = strins&") "&strval&")"

objcmd.execute

x=x+1

loop



set objcmd=nothing

objcon.close

set objcon=nothing



What do you think?  This one has had me racking me poor little brain for

days.



Thanks a bunch,

Mike



p.s.  Would it be much better to use an action stored procedure to insert

the records?

And/or batch processing using T-SQL (I've barely started reading about

this)?



----- Original Message -----

From: "Dallas Martin" <dmartin@z...>

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, January 20, 2001 7:40 AM

Subject: [asp_databases] Re: database has the flu?





> Try closing the  Access Table before running the code.

> Then open the table and check your results.

> Then check your multiuser db settings

>

> ----- Original Message -----

> From: "Michael Goldman" <mg188@h...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Friday, January 19, 2001 8:22 PM

> Subject: [asp_databases] database has the flu?

>

>

> > I insert a record through an asp command object, then manually deleted

it

> > from the Access db, then save the db.

> >

> > Here's the wierd part:  then I can't insert the same record again unless

I

> > compact the database first.  Nor can I insert a similar record (same

> fields

> > and table).

> >

> > My other pages and tables don't act like this.

> >

> > I haven't found other effects after step 1.  I can still read and write

> > records to other tables in the db through other pages without the

> compacting

> > step.

> >

> > Any one have a cure for this?  Or explain the cause?

> >

> > Happy Trails,

> >

> > Mike

> >

>

I don't have an answer. Sorry.



----- Original Message -----

From: "Michael Goldman" <mg188@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, January 20, 2001 4:51 PM

Subject: [asp_databases] Re: database has the flu?





> Hi, I tried your suggestions with no luck.  I'm working in PWS with no

> network, so would multiuser settings have any affect?  But I reduced the

> timeouts and intervals and set response.expires = 0 in the processing page

> and tried again.  No difference.

>

> I think I've isolated the problem a little.  I split 2 strings, then run

'em

> thru a loop that trims each value, then tries to insert value pairs.

These

> strings are normally built from a form on the previous webpage, but this

> time I made

> dummy strings on the processing page and saw the same behavior.

>

> str1="ASDF1, ASDF2"        'first dummy string

> str2="02/02/02, 03/03/03"    'second dummy string

>

> The behavior:  I process the page once and 2 records show up fine.  I

> process the page a second time, and the browser goes blank.  Now there are

3

> records in the db.  The 3rd record is ASDF1, 02/02/02.  After that,

repeated

> page-processing shows a blank browser with no furthur change in the db.

If

> I delete these 3 records, then compact the db, I can repeat this with the

> same results.  Same results whether the db is open or not.  Same results

if

> I close the browser between processing attempts.  Same results if I reboot

> first.

>

> So I'm guessing something in the loop hangs the processing, or something

in

> the db stops the process.

>

> Code for the loop:

>

> Set objCon = Server.CreateObject("ADODB.Connection")

> objCon.Open "DSN=mg53rev1"

> set objcmd = server.createobject("adodb.command")

> set objcmd.activeconnection = objcon

> objcmd.commandtype = adcmdtext

>

> str1="ASDF1, ASDF2"    'the first dummy string

> str2="02/02/02, 03/03/03"    'the second dummy string

> x=0

> do while not x=2

> split1=split(str1,",")

> split2=split(str2,",")

> strins="insert into tbl (field1,field2"

> varstr1=cstr(trim(split1(x)))

> varstr2=cstr(trim(split2(x)))

> strval="values('"&varstr1& "','"&varstr2&"'"

> objcmd.commandtext = strins&") "&strval&")"

> objcmd.execute

> x=x+1

> loop

>

> set objcmd=nothing

> objcon.close

> set objcon=nothing

>

> What do you think?  This one has had me racking me poor little brain for

> days.

>

> Thanks a bunch,

> Mike

>

> p.s.  Would it be much better to use an action stored procedure to insert

> the records?

> And/or batch processing using T-SQL (I've barely started reading about

> this)?

>

> ----- Original Message -----

> From: "Dallas Martin" <dmartin@z...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Saturday, January 20, 2001 7:40 AM

> Subject: [asp_databases] Re: database has the flu?

>

>

> > Try closing the  Access Table before running the code.

> > Then open the table and check your results.

> > Then check your multiuser db settings

> >

> > ----- Original Message -----

> > From: "Michael Goldman" <mg188@h...>

> > To: "ASP Databases" <asp_databases@p...>

> > Sent: Friday, January 19, 2001 8:22 PM

> > Subject: [asp_databases] database has the flu?

> >

> >

> > > I insert a record through an asp command object, then manually deleted

> it

> > > from the Access db, then save the db.

> > >

> > > Here's the wierd part:  then I can't insert the same record again

unless

> I

> > > compact the database first.  Nor can I insert a similar record (same

> > fields

> > > and table).

> > >

> > > My other pages and tables don't act like this.

> > >

> > > I haven't found other effects after step 1.  I can still read and

write

> > > records to other tables in the db through other pages without the

> > compacting

> > > step.

> > >

> > > Any one have a cure for this?  Or explain the cause?

> > >

> > > Happy Trails,

> > >

> > > Mike

> > >

> 

Why do you split the arrays inside the loop?? Lot of overhead, I think. 

Also, you can use the execute method of the Connection object as well, so 

there is no need to create an extra Command object.



Try this:



str1="ASDF1, ASDF2"    'the first dummy string

str2="02/02/02, 03/03/03"    'the second dummy string



split1=split(str1,",")

split2=split(str2,",")



Dim iUbound, iLoopCount

iUbound = UBound(split1) ' get number of items in the array. Will return 1 

in this case, which means two items (0 and 1)



Set objCon = Server.CreateObject("ADODB.Connection")

objCon.Open "DSN=mg53rev1"



For iLoopCount = 0 to iUbound ' loop for the number of items in the array. 

Will insert split1(0)  and split1(1)

         strins = "insert into tbl (field1, field2"

         varstr1 = cstr(trim(split1(x)))

         varstr2 = cstr(trim(split2(x)))

         strval = "values('" & varstr1 & "','" & varstr2 & "'"

         strins = strins&") "& strval &")"

         Response.Write(strins & "<BR>")

         objCon.Execute(strins)

next



objCon.Close()

set objCon = Nothing



Is field1 the primary key of the table? That is, does it allow duplicates 

or not? If it doesn't that might be the reason why you can't add the same 

record twice.



HtH





Imar







At 01:51 PM 1/20/2001 -0800, you wrote:

>Hi, I tried your suggestions with no luck.  I'm working in PWS with no

>network, so would multiuser settings have any affect?  But I reduced the

>timeouts and intervals and set response.expires = 0 in the processing page

>and tried again.  No difference.

>

>I think I've isolated the problem a little.  I split 2 strings, then run 'em

>thru a loop that trims each value, then tries to insert value pairs.  These

>strings are normally built from a form on the previous webpage, but this

>time I made

>dummy strings on the processing page and saw the same behavior.

>

>str1="ASDF1, ASDF2"        'first dummy string

>str2="02/02/02, 03/03/03"    'second dummy string

>

>The behavior:  I process the page once and 2 records show up fine.  I

>process the page a second time, and the browser goes blank.  Now there are 3

>records in the db.  The 3rd record is ASDF1, 02/02/02.  After that, repeated

>page-processing shows a blank browser with no furthur change in the db.  If

>I delete these 3 records, then compact the db, I can repeat this with the

>same results.  Same results whether the db is open or not.  Same results if

>I close the browser between processing attempts.  Same results if I reboot

>first.

>

>So I'm guessing something in the loop hangs the processing, or something in

>the db stops the process.

Thanks Dallas, Imar and Shreekar for your good advice.  I'll start using the

connection object to insert.  (I did catch that little problem, and neither

field is key).



After trying your suggestions and some more

tweaks with no change, I finally rebuilt the table from scratch.  Voila!

Problem gone!  I was careful to duplicate all fields and data type info, so

I assume the old table was corrupt, if that's the right term.



This begs more questions:

What causes it?

How to prevent it?

How to detect it?  Set some sort of tests that run periodically?

How to handle it in a production environment - especially if there are

lots of records?

If I build a new table, can I then transfer the records, as opposed to

re-entering the data?  Is this "replication", and would it work reliably

from a

corrupt table?  Is there a way to do this in Access?

Does this ever happen in SQL Server?



Thanks again,



Mike

What bersion of Access are you using? Access 95 was really bad about 

corruption. Somewhat better in 97. I haven't had a problem in 2K.

You should do a repair and compact every once in a while.

thanks, Wally, I'll do that with my Access 97 db.  So it sounds like you

think Access is getting more robust each upgrade.  Any thoughts on how to

transfer data from a corrupt table?



----- Original Message -----

From: "Wally Burfine" <oopconsultant@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, January 22, 2001 12:08 PM

Subject: [asp_databases] Re: database has the flu?





> What bersion of Access are you using? Access 95 was really bad about

> corruption. Somewhat better in 97. I haven't had a problem in 2K.

> You should do a repair and compact every once in a while.

>

>

Access is always suspect.  Moreover if the db is on NON-NTFS drives, chances

you'll

encountered more disk related problems as the db expands (INSERTS), changes

(UPDATES)

and contracts (DELETES) (and don't forget the index files, which by the way

get a lot of use).

It called DISK FRAGMENTATION and eventuallys leads to very slow performance.



At least move the db to the Windows NTFS drive system. NTFS has the ability

to fix disk errors on

the fly. FAT32/VFAT does not.







----- Original Message -----

From: "Michael Goldman" <mg188@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, January 22, 2001 11:45 AM

Subject: [asp_databases] Re: database has the flu?





> Thanks Dallas, Imar and Shreekar for your good advice.  I'll start using

the

> connection object to insert.  (I did catch that little problem, and

neither

> field is key).

>

> After trying your suggestions and some more

> tweaks with no change, I finally rebuilt the table from scratch.  Voila!

> Problem gone!  I was careful to duplicate all fields and data type info,

so

> I assume the old table was corrupt, if that's the right term.

>

> This begs more questions:

> What causes it?

> How to prevent it?

> How to detect it?  Set some sort of tests that run periodically?

> How to handle it in a production environment - especially if there are

> lots of records?

> If I build a new table, can I then transfer the records, as opposed to

> re-entering the data?  Is this "replication", and would it work reliably

> from a

> corrupt table?  Is there a way to do this in Access?

> Does this ever happen in SQL Server?

>

> Thanks again,

>

> Mike

>

>

>

I think you tend to be screwed when you have a corrupt table. When 

corruption occurs Access can't read the table at all. So you have to go to 

back ups.





>From: "Michael Goldman" <mg188@h...>

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] Re: database has the flu?

>Date: Mon, 22 Jan 2001 15:32:26 -0800

>

>thanks, Wally, I'll do that with my Access 97 db.  So it sounds like you

>think Access is getting more robust each upgrade.  Any thoughts on how to

>transfer data from a corrupt table?

>

>----- Original Message -----

>From: "Wally Burfine" <oopconsultant@h...>

>To: "ASP Databases" <asp_databases@p...>

>Sent: Monday, January 22, 2001 12:08 PM

>Subject: [asp_databases] Re: database has the flu?

>

>

> > What bersion of Access are you using? Access 95 was really bad about

> > corruption. Somewhat better in 97. I haven't had a problem in 2K.

> > You should do a repair and compact every once in a while.

> >

> 

Interesting.  I defrag the disk once a month or so.  Anyway, I'm getting

ready to upgrade to win2k pro and will convert to ntfs.  I'll also add ntfs

to my list of questions for webhosts.



Thanks again for the useful info.