More ASP Tips

by Aaron Bertrand

Easy Stored Procedure Execution
If you’ve written stored procedures, this little paragraph probably isn’t going to be shocking news for you. If you are in charge of making sure that existing stored procedures are fired at appropriate times, read on.

If you’ve written stored procedures, this little paragraph probably isn’t going to be shocking news for you. If you are in charge of making sure that existing stored procedures are fired at appropriate times, read on. This is a little tip on simplifying ASP-driven stored procedures, provided they don’t return any data. Contrary to the exhaustive coding techniques recommended by Microsoft (man, that’s a lot of code!), here is a much simpler way to handle an SP:

set conn = createobject(“adodb.connection”)
conn.open “dsn=dsnName;uid=username;pwd=password”
conn.execute(“exec spl_mySP @param=’string'”)
conn.close
set conn = nothing
%>

Wasn’t that easy? No Recordset objects, no Command objects, no messy parameter statements, etc etc. No muss, no fuss. However if your stored procedure needs to return a recordset to your ASP code, you’re not going to get away this easy. 😉

Can’t open IIS 4.0 MMC with certain user accounts?

Many people have complained about only being able to use the Internet Services Manager snap-in by logging in with the account that initially installed the Option Pack. Patrick Philippot recently shared his wisdom, explaining how he made the MMC behave under ALL accounts (please back up your registry before attempting this!):

1. Logon using the account for which you have the problem, making sure the MMC is closed.
2. Open Regedit, and go to HKCUSoftwareMicrosoftCryptographyUserKeys.
3. Delete the subkey named MS IIS DCOM Client.
4. Close the registry and reboot. You are now able to use the Internet Services Manager snap-in with your other account(s)!

Compiling DLL: ‘Permission Denied’

Writing server-side WebClasses or business objects and having problems compiling them? Create this little batch file, which will force IIS to release DLLs from memory (this works on PWS for Win98 and NT Workstation, as well as IIS on NT Server):

net stop iisadmin /y
net start w3svc

You might need to add additional lines for other dependent services, e.g. if you are running an FTP server, you’ll want to add a third line:

net start msftpsvc

Of course, this procedure is not recommended on a production machine (except when the code is done and it’s time to upload), as it would wreak havoc on your uptime ratio. I have this batch file sitting on my taskbar, and a copy of it on my desktop. It’s probably my most commonly-used shortcut!

Those darn text fields!

Using a SQL Server text field, and having difficulty getting it to display in your ASP page? Here are some things to look out for:

1. Don’t use select * notation. NAME the fields you are retrieving, and list the TEXT type fields LAST.
2. Immediately after committing your recordset, store your TEXT type fields in local variables, in the order they were retrieved with your SQL statement.
3. When returning the data to the client, refer to the local variable names instead of their fieldNames from the recordset object.

Here is a sample (assume “text1” and “text2” are TEXT type fields):

sql = “select f1,f2,text1,text2 from table”
set conn = createobject(“adodb.connection”)
conn.open “dsn=dsnName;uid=username;pwd=password”
set rs = conn.execute(sql)
do while not rs.eof
  t1 = rs(“text1”)
  t2 = rs(“text2”)
  response.write(rs(“f1”) & “
“)
  response.write(rs(“f2”) & “
“)
  response.write(t1 & “

” & t2 & “

“)
  rs.movenext
loop

rs.close
set rs = nothing
conn.close
set conn = nothing
%>

🙂