Hi

I have a C# web application that connects to a SQL Server database. I
have about 15000 records where there is a resume field for each
record. I have a SQL SELECT (using LIKE) statement that the C#
creates based on users input to search for a resume that has whatever
keyword(s) the user would like to find. However, for some reason, the
searches come up blank the majority of the time, even when I am
searching using words that I know are in the database records. I have
this application and database hosted on a GoDaddy server.

It seems like the page is waiting for the response from the database,
but if the response isn't back soon enough, the page just goes ahead
and renders (but with no found records).

Is this something that I can fix by telling the C# page to wait until
a response is received back from the SQL Server database? Or could
this be a setting in SQL Server I don't have set correctly? Or
perhaps it is an issue with the host company?

Thanks for anybody's help in pointing me in the right direction.

Paul

ptvvee (jobs (AT) thomassharp (DOT) com) writes:
It's very unclear what your problem is. You say your pages come up "blank".
Does this mean that they do not display anything at all? Or do they
display like "no records found"? What you get these "blank" pages, how
long does it take? Do searches for some keywords come back with data and
sometimes come up "blank"?

How does your SELECT statements look like?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Aug 12, 6:16*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Hi Erland... thank you for the response. I know my issue is a little
unclear but that is what is so frustrating. I have tested and used
this application locally and on the web with smaller amounts of data
and it works fine. I have no way to test it on the web host though to
see what is happening (as all of the data is there). Basically
though, the SELECT statement looks something like this :

Select * from tblCandidates WHERE Resume LIKE Java (Sorry if the
syntax is a little off)

The page that renders the results of the search are supposed to take
all of the records and format them into a list. So if 5 resumes had
the word Java, it would look something like :

John Jones
Peter Smith
Greg Johnson
Eric Ravul
Betsy Hamilton

where each of these would be links, that when you click them show
their resume.

To answer a couple of your questions though, when the pages come back,
the header info is there, there are just no records. And yes, when it
does this, it takes a long time. And yes, sometimes when you put in a
search word it comes back with results where other times the exact
same search shows no results. Sometimes you have to do the same
search 2-10 or more times and then it will finally show records
(like.. when you know that a word exists in resumes... something
common like Java).

Does this have anything to do with the size of the database? And is
there a way to tell the C# page to wait until the results come back
(if this is what is happening)?

Thanks for all of your help.... I appreciate the links but they aren't
going to help someone like me who isn't a SQL Server expert where this
seems to be an advanced problem.... thanks, Paul

ptvvee (jobs (AT) thomassharp (DOT) com) writes:
Well, to be able to see if thre is any problem with the SQL, I would
need to see the exact statement. But from what you say that you get the data
sometimes, the SQL is probably OK.

More exactly 30 seconds?

It sounds that you get an command timeout. By default, ADO .Net cancels
running a query, if no result comes back from the server with the time
configured for the command timeout. Now, a command timeout raises an
exception, so show you manage to swallow that error. This is probably
something you should investigate first.

At home, take one of the guys that have Java in the résumé and do in a query
window:

BEGIN TRANSACTION
UPDATE tbl
SET firstname = 'Helge'
WHERE ...


and leave the transaction uncommitted. This should create the blank screen
in your test environment as well. If you debug this, you should see that
there is an exception, but what happens with it?

The CommandTimeout is a property, both on the Command and Connection
objects.

Since you are search for text in documents, you should probably consider
full-text searches for better speed. Preferrably, you want SQL 2008
for this.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx