Webmaster Forum / ASP / Database Access / May 2008

On May 15, 9:40 am, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:

Here goes.

dim con
dim rst
const ADopenkeyset = 1
const adLockReadOnly = 1
dim strsql
dim user
dim pwd

user = Request.Form("login")

pwd =  Request.Form("password")

strsql = "SELECT ctridx, txtuser, txtpwd, lngclient, txtFirst, txtLast
" & _
        "FROM dbo.tblPODescUpdaters WHERE (txtuser ='" &
Request.Form("login")  & "')"

set con = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.Recordset")

con.open "fastpack"
rst.open strsql, con, adOpenKeyset, adLockReadOnly
if not rst.EOF Then

if Request.Form("password") = pwd  then

dim emp
dim First
dim last

dim agentidx
session("loggedin")= "true"
first = rst("TxtFirst")
last = rst("TxtLast")

session("first") = rst("TxtFirst")
session("last") = rst("TxtLast")

session("useridx") = rst("ctrIdx")

rst.Close

%><!-- #include file="header2.inc" --><%
%> <FONT FACE="ARIAL,GENEVA,Helvetica,Sans-Serif" size=2>
<% ShowHeader("Welcome  " & first & " " & last)%>

</head>
 <p>&nbsp</p>

<p align = left>&nbsp</p>
<FORM  method=post id="form1" name="form1"
action="translatesearch.asp">
<table align = left>
 <tr>
  <td><font color = "black">Enter a P.O. Number</a></font></td>
  <td>&nbsp</td>
  <td><input type = text id="po" name="po" width = 25></td>
  <td><INPUT type="submit" value="View" id=button1   name=button1></
td>
 </tr>
</table>
</form>

<p align = left>&nbsp</p>
<FORM  id="form2" name="form2">
<table align = left>
 <tr>
  <td><font color = "black">Select a Field Destination</a></font></
td>
  <td>&nbsp</td>
  <td>
  <%' Get a list of authorized field destinations
  strsql = "SELECT TOP 100 PERCENT
dbo.tblPOUpdatersFieldDests.lngfielddestidx,
dbo.tblPOUpdatersFieldDests.lnguseridx, dbo.TblFieldDest.TxtDestName "
& _
           "FROM dbo.tblPOUpdatersFieldDests LEFT OUTER JOIN
dbo.TblFieldDest ON dbo.tblPOUpdatersFieldDests.lngfielddestidx =
dbo.TblFieldDest.CtrDestIdx " & _
           "WHERE (dbo.tblPOUpdatersFieldDests.lnguseridx = " &
session("useridx") & ") ORDER BY dbo.TblFieldDest.TxtDestName"

  rst.open strsql, con, adOpenKeyset, adLockReadOnly

The first opens fine.   I close it and attempt to open the second and
get the odbc error.    But if I use response.write(strsql) and paste
the generated SQL into a view it returns records correctly.

Again, this has worked for years, but now I'm running into similary
problems re-using recordset objects in other pages.   And I've
confirmed the issue on two servers (at two seperate clients).