ur focus has always been on assisting the new ASP programmer in mastering the techniques that the pros use all the time. Thus, to help you wade through the list of tips and tricks on our site, we present you with a “Top 10 List” of what we consider to be our best. (You can browse the entire list of tips and tricks here).

Remember that this list is current only as of today. As we add new tips and articles, the “best” will undoubtedly change. But, for now…drum roll, please…here they are:

Logging File Download

This is a very simple and elegant method to monitor how many people download a resource from your Web site. Former ASP Pro Ken Cox provides a brief solution to this often-asked question.

Question:
We need to keep track of users’ file download activity on our site. When a user clicks on a hyperlink to download a file, it will run an ASP page on the server to log something into SQL Server and then download the file into the client. How can an ASP page download a file back to the client after the logging process?

Answer:
The trick is to use a hyperlink to launch a second “background” script that logs the filename and initiates the download. If the second script doesn’t produce any HTML output, the first page stays on the user’s browser screen.

Here’s how the hyperlink looks in the first file:

Keying Into Smart Database Searches

Writing your own little search routine has never been easier. But consider the fact that your users would like to do partial text searches on data within your database and it gets worse. You can’t retrieve records that match the text “ASP Pro” just by doing a search on the text “pro” and using an equal to sign. This tip explains how to use the LIKE SQL clause to do partial text searches.

Question:
When setting up a recordset, is it possible to get a record when the field contains more than the word that I’m looking for and where the words are in no particular order?

For example:

StrName = ‘Direct’	RSShops = Server.CreateObject(“ADODB.Recordset”)	strSql = “SELECT * FROM Shops WHERE Name =         ‘” & StrName & “‘

Can I get the recordset to contain any shop/company with ‘direct’ in it’s name?

Answer:
Instead of using the Equal to (=) operator, use the LIKE operator in your SQL Statement.

To hunt for Shops with names that have the word ‘direct’ in them, your SQL statement would look like this:

SELECT * FROM Shops WHERE Name LIKE ‘%direct%’

You can thus modify your code accordingly.

Keeping Up to Data with Recordsets

As you start using recordsets within your ASP page, you will frequently run into this problem. You want to access the data within your recordset, but you need to make sure it has data before you access it. Remember that, if the recordset has no data, you will get an ugly, run time error message. You can use the code in this tip to solve your problem.

Question:
I have several years of experience with VB but am brand new to ASP (VB Script). Right now, I’m trying to open an access database, count the number of records and display the information on the Web page. I know that?my database contains a table (people) called ‘sean.mdb’?which has three records. However, when I run the script it says that there are -1 records in the table.

Can you tell me what I’m doing wrong in my code:

<% Set objConn = Server.CreateObject("ADODB.Connection")Set objRst = Server.CreateObject("ADODB.Recordset")objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.Mappath("seannewelldbsean.mdb"))strSQL = "SELECT * FROM people"objRst.Open strSQL, objConn Response.write( "
” & strSQL & “
” )Response.write(“
There are ” & objRst.RecordCount & ” People in the database
“)If objRst.RecordCount > 0 Then	objRst.MoveFirst	Do While Not objRst.EOF		Response.write( “Name = ” & objRst.fields(0) )		objRst.MoveNext	Loopelse	Response.write( “It’s EMPTY!” )End IfobjRst.CloseSet objRst = NothingobjConn.CloseSet objConn = Nothing%>

Answer:
The RecordCount property returns -1 in older versions of MDAC. Try to upgrade the MDAC files on your server to the latest ones, available at www.microsoft.com/data.

If you cannot do that because your Web server is hosted by your ISP and you do not have control over it, change your code.

Instead of using:

   If objRst.RecordCount > 0 Then …

to check if there are records in your Recordset, use the following:

   If objRst.BOF and objRst.EOF Then      ‘ Recordset is Empty   Else      Do While not objRst.EOF         ‘Process the recordset         objRst.MoveNext      Loop   End If

Update 6/30/00: Daryl Egarr from New Zealand made this observation:

The page implies that the code in question is okay but that “The RecordCount property returns -1 in older versions of MDAC.” While this may be true, the author should not make that assumption based on the code in question, as there is nothing in the code that suggests an older version MDAC.

The author missed the whole point, which is that not all properties and/or methods are supported by all cursor types (regardless of database type). The real reason the code failed is that when using the default cursor location(which the code does):

Recordset.CursorLocation = adUseServer

the RecordCount property is only available if the Recordset uses CursorType 1 or 3 (adOpenKeyset or adOpenStatic). The code doesn’t specify a CursorType, so therefore 0 (adOpenForwardOnly?the fastest cursortype) is used, and any RecordCount call will return -1.

The Solution is to simply change the line …

objRst.Open strSQL, objConn 

to …

objRst.Open strSQL, objConn ,1

Dodging the Dinosaur of DSN

Still connecting to databases using an ODBC system or file DSNs? Get with it, man! Don’t be a dinosaur–instead, use the much faster OLEDB Provider technique to connect to your database without using a DSN. No more pleading with your ISP (or your DBA/Webmaster) to create a System DSN for you. And no more configuration changes when you move Web files.

Question:
I see many examples of using a data source name (DSN) to connect to a database. I would like to access a database without using a DSN. Can I do this with ASP? Could you show some sample connection code? I would like to pass the Driver, Server Name, UID, PWD, and Database in a connection string and not depend on a DSN on a machine.

Answer:
If you are using SQL Server 7, use this code as your connection string:

strConnString = “DSN=”;DRIVER={SQL SERVER};” & _ “UID=myuid;PWD=mypwd;” & _ “DATABASE=MyDb;SERVER=MyServer;” 

The most important parameter is the DRIVER= portion. If you want to bypass ODBC and use SQL Server using OLEDB (this is supposed to be faster), use this syntax:

strConnString =”Provider=SQLOLEDB.1;Password=mypassword;” & _ “Persist Security Info=True;User ID=myuid;” & _ “Initial Catalog=mydbname;” & _ “Data Source=myserver;Connect Timeout=15” 

Big Tip:
If you require a connection string but are unfamiliar with the syntax required by the OLE DB provider, use the either the Data Environment designer or the ADO Data Control in Visual Basic to create one, and copy it for use with the ADO Connection object. In the Immediate window, type: ? dataenvironment1.connection1.ConnectionString to get the actual string.

Note: The syntax for Microsoft Access is different.

For more information on using the non-DSN connections with Access, check out the tip, Syntax for DSN-Less Connection for MS Access