Solved

update timesout my script but ....

Posted on 2001-07-16
14
286 Views
Last Modified: 2008-02-26
Ok I have a webpage which is just a large form. One of the questions takes a large amount of data (the users are basically pasting a 10k text page into the textbox). THe problem is this....they can fill out their form and paste their mondo report into question 27 and submit fine. The data is saved and everything is hunky dory. However, once they have submitted the form with the mondo text pasted in, then whenever they try to resubmit afterwards it timeouts with...

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

[Microsoft][ODBC SQL Server Driver]Timeout expired

/dencom_web/preventive_dentistry/submit_preventive_dentistry.asp, line 251

BTW: line251=   Conn.Execute(sqlstr_update_record)

Now if I take the exact query that the asp page is trying to run and just paste it into the sql server query analyzer , it runs instantly with no problems. I dont get it? IIS and the sql server are on the same box so why is this happening?  BTW , total size of the data in the form is approx 11kb.
0
Comment
Question by:mccainz2
14 Comments
 
LVL 5

Author Comment

by:mccainz2
Comment Utility
oh yes, all this is being tested on the LAN that the server is on.
0
 
LVL 7

Expert Comment

by:weesiong
Comment Utility
mccainz2,

Conn.ConnectionTimeout = 60

Regards,
Wee Siong
0
 
LVL 5

Author Comment

by:mccainz2
Comment Utility
No No No.
Remember I said that the first time you submit that data it works instantly. However if I pull up my saved data (which is just the exact same data I submitted successfully) then it hangs indefinitely. THere is something going on here that doesnt add up. Identical posts should both save at roughly the same execution time on a stable LAN with little traffic and little load on the servers. SO what makes the first post work and then any subsequent posts hang. Something is breaking and I need to know what.
BTW: I use the 'text' type for all columns that accept nonnumeric data.
0
 
LVL 5

Author Comment

by:mccainz2
Comment Utility
let me clarify

"However if I pull up my
saved data (which is just the exact same data I submitted successfully) then it hangs indefinitely"

If I pull up my saved data in the form and then try to resubmit it, then it hangs indefinitely.
0
 
LVL 3

Expert Comment

by:nigelrowe
Comment Utility
Sounds like a lock problem to me.
0
 

Expert Comment

by:theproton
Comment Utility
My guess is that on resubmit, the record that you just updated is no longer a candidate in the WHERE clause of your sqlstr_update_record stored procedure.
 
Hence it doesn't find a record to update and therefore times out.

Is your asp page requerying the database for the updated record contents prior to the resubmission??
0
 
LVL 5

Author Comment

by:mccainz2
Comment Utility
I thought about that also but I can go right into sqlserver at any time I want and run the update query that the asp page pukes on and it (query analyzer) runs instantly with a successful update.
And, I have specified no locks on the table in my code.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:mccainz2
Comment Utility
theproton...

The record is stil la candidate. I verified this by printing out the query string when the program pukes. I can then take the query string and run it in the sql server query analyzer and have it execute perfectly (1 row affected).

I am upping points now.
0
 
LVL 5

Author Comment

by:mccainz2
Comment Utility
I went into question 27 and started increasing the size incrementally. It was fine until 7000 bytes (7000 'a's).
At that point it started exhibiting the 'hang forever' behaivor on submission leading to the ODBC timeout. A text field can hold an unlimited(well practically) number of bytes in sqlserver. Is there something about the ASP interface that limits this?
0
 

Expert Comment

by:theproton
Comment Utility
How are you building your querystring for this large field of yours??
Are you using <%=Server.URLEncode(mylargefield)%>
??
0
 
LVL 5

Author Comment

by:mccainz2
Comment Utility
theproton,
no Im not using URLencode, the whole query is just a  string. Both IIS and SQLserver are on the same box. Nothing actually drops on the copper.
Also, just guessing at your direction (forgive me Im probably wrong) but the URL is fine. THe submission from the post arrives at the 'bidness end' just dandy. All the data is extracted from the post and used to form the correct query string (again validated by taking that query string and running it in query analyzer). THe thing that gets me is that the post works fine the first time but after that the identical post results in 'teats up'.
0
 
LVL 5

Accepted Solution

by:
dgorin earned 125 total points
Comment Utility
A couple tips on text data and ADO.  You should put the text field(s) at the end of your sql string so they are the last columns returned.

In the asp code, you should immediately move the data from the text column to a local variable.  Using ...rs("textfield")... in code may fail after the first call to the data field contents.
0
 

Expert Comment

by:theproton
Comment Utility
good point dgorin.  Also, you may have to look into the getchunk method of ADO
http://www.devguru.com/technologies/ado/quickref/field_getchunk.html
in order to work with this particular field.
0
 
LVL 5

Author Comment

by:mccainz2
Comment Utility
still working on it, I think some permutation of dgorins answer may work.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now