Why does my ASP code break when I move from IIS 5 to II 6

[Adrian Forbes [ASP MVP]]

Which is line 21? If it is

.ActiveConnection = cn

and cn is a connection object try

set .ActiveConnection = cn

 

[Dave F]

I copied a web project that ran fine on IIS 5 to IIS 6 using VID 6.0's copy
project function.

The web site appears to work fine except for pages that access a database.
When I try to view such a page on the IIS 6 box I get the following error:


Line 1: Incorrect syntax near '='.
/hours/includes/consult_info.asp, line 21


The line referenced by the error is shown below:


Set rs = Server.CreateObject("ADODB.recordset")
With rs
.ActiveConnection = cn
.source = sSql
.Open
End With


Why does my code break in IIS 6? It works fine on IIS 5.

 

[Bob Barrows [MVP]]

I copied a web project that ran fine on IIS 5 to IIS 6 using VID
6.0's copy project function.

The web site appears to work fine except for pages that access a
database. When I try to view such a page on the IIS 6 box I get the
following error:


Line 1: Incorrect syntax near '='.
/hours/includes/consult_info.asp, line 21


The line referenced by the error is shown below:


Set rs = Server.CreateObject("ADODB.recordset")
With rs
.ActiveConnection = cn
.source = sSql
.Open
End With


Why does my code break in IIS 6? It works fine on IIS 5.

Which one is line 21?

".ActiveConnection = cn" should be "Set .ActiveConnection = cn" but that's
not a syntax error, just a performance inhibitor.

Bob Barrows

 

[Aaron [SQL Server MVP]]

I agree with Curt, do this instead of the With rs section:

Response.Write sSql
Response.End

 

[Dave F]

Thanks Guys.

The offending line is:

rs.Source=sSql

I re-wrote the code as below but am still getting the error:

'open connection (defined in db_cn.asp include)
cn.open

'Get specified record:
sSQL = "usp_Sp_GetConsultInfo"
sSQL = sSQL & " @logon = " & shortuser

Set rs = Server.CreateObject("ADODB.recordset")

rs.ActiveConnection = cn

rs.Source=sSql '****error line
rs.Open

Do while not rs.EOF.....

I should mention that this is from an "include" file with an .asp extension.

This code was a direct copy from an IIS 5 machine that is running fine in
production.

Why would installing on IIS 6 cause a problem.

 

[Bob Barrows [MVP]]

Thanks Guys.

The offending line is:

rs.Source=sSql

I re-wrote the code as below but am still getting the error:

'open connection (defined in db_cn.asp include)
cn.open

'Get specified record:
sSQL = "usp_Sp_GetConsultInfo"
sSQL = sSQL & " @logon = " & shortuser

Look at the result of:

response.write sSQL

If @logon is a character parameter, then this line should have failed in
IIS5 since you did not delimit the parameter value.


Instead of that dynamic sql, I suggest using the following to execute your
procedure:

cn.usp_Sp_GetConsultInfo shortuser, rs

Now you no longer have to worry about delimiters or SQL Injection.

Bob Barrows

 

[Dave F]

Thanks very much Bob for helping me out.

My problem was that the "shortuser" variable was derived from:
Request.ServerVariables("AUTH_USER")

This returns a null in IIS 6 (but not IIS 5) and, as you pointed out,
because my parameter was not delimited, caused the error.

Thanks again for your help.

Dave

PS.
I have never seen a stored proc executed in the manner you showed (as a
property of a connection object):

cn.usp_Sp_GetConsultInfo shortuser, rs

How long have we been able to do this?

 

[Dave F]

Request.ServerVariables("AUTH_USER") was returning a null because Allow
Anonymous Access was turned on for the IIS 6 web site. It should have
permitted only NT authentication.

This was the fundamental difference between the two web servers.