×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

ODBC -> VFP Table & SQL problem

ODBC -> VFP Table & SQL problem

ODBC -> VFP Table & SQL problem

(OP)
I do not know anything about VBasic Script and the programmer that did left the company and the company want me to make a change to the asp file.   

A Form has 5 fields. File looks for data in any one of the fields and does a SQL on the first field if finds info in.  Company now wants a 2 field search instead of a one field search.  

First table name id Projects, If it finds records in that table it wants a second SQL of the results.

I can not get the code to work correctly,  Can anyone point out the errors of my ways?

<% Response.Buffer = True %>
<html>
<head><title> Projects </title></head>
<body>
<%
Dim adOpenKeySet
adOpenKeySet = 1

Dim adLockOptimistic
adLockOptimistic = 3

Dim adUseClient
adUseClient = 3

Dim lnSqlRun
lnSqlRun = 0

Dim lnRcount
lnRCount = 0

proj = UCase(Request.Form("ProjNum"))
proj = LTrim(proj)
proj = RTrim(proj)

reqno = UCase(Request.Form("ReqNum"))
reqno = LTrim(reqno)
reqno = RTrim(reqno)

lcBac = UCase(Request.Form("BacNum"))
lcBac = LTrim(lcBac)
lcBac = RTrim(lcBac)

lcAgy = UCase(Request.Form("AgName"))
lcAgy = LTrim(lcAgy)
lcAgy = RTrim(lcAgy)

title = UCase(Request.Form("ProjTitle"))
title = LTrim(title)
title = RTrim(title)
title = Replace(title, "(", "")
title = Replace(title, ")", "")
title = Replace(title, "\", "")
title = Replace(title, "'", "")
title = Replace(title, "  ", " ")
title = Replace(title, "   ", " ")
title = Replace(title, "    ", " ")
title = Replace(title, " ", "%' AND Prjname LIKE '%")

If Not IsNull(proj) And (Len(proj) > 0) And (lnSqlRun = 0) Then
  lnSqlRun = 1
  Set rs = Server.CreateObject("ADODB.Recordset")
  rs.CursorLocation = adUseClient
  SQL = "SELECT * FROM projects WHERE rtrim(Project) = '" & proj & "'"
  rs.Open SQL, "DSN=bidsys", adOpenKeySet, adLockOptimistic
  lnRCount = rs.RecordCount
End If

If Not IsNull(reqno) And (Len(reqno) > 0) And (lnSqlRun = 0) Then
  lnSqlRun = 1
  Set rs = Server.CreateObject("ADODB.Recordset")
  rs.CursorLocation = adUseClient
  SQL = "SELECT * FROM projects WHERE rtrim(Req_no) = '" & reqno & "'"
  rs.Open SQL, "DSN=bidsys", adOpenKeySet, adLockOptimistic
  lnRCount = rs.RecordCount
End If

If Not IsNull(title) AND (Len(title) > 0) And (lnSqlRun = 0) Then
  lnSqlRun = 1
  Set rs = Server.CreateObject("ADODB.Recordset")
  rs.CursorLocation = adUseClient
  SQL = "SELECT * FROM projects WHERE Prjname LIKE '%" & title & "%'"
  rs.Open SQL, "DSN=bidsys", adOpenKeySet, adLockOptimistic
  lnRCount = rs.RecordCount
End If

if lnSqlRun =1 then
  If Not IsNull(lcAgy) And (Len(lcAgy) > 0) And (lnSqlRun = 1) Then
    lnSqlRun = 2
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation = adUseClient
  error occurs here
   SQL = "SELECT * FROM bidsys WHERE Buyer = '" & lcAgy & "'"
    rs.Open SQL, "DSN=bidsys1", adOpenKeySet, adLockOptimistic

   lnRCount = rs.RecordCount
  End If

  If Not IsNull(lcBac) And (Len(lcBac) > 0) And (lnSqlRun = 1) Then
    lnSqlRun = 2
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation = adUseClient
error occurs here
    SQL = "SELECT * FROM bidsys WHERE rtrim(Agency) = '" & lcBac & "'"
    rs.Open SQL, "DSN=bidsys1", adOpenKeySet, adLockOptimistic

    lnRCount = rs.RecordCount
  End If
End If

If (lnSqlRun = 0) and (lnRCount = 0) Then
  Response.Write "<H2>Please Data in the</H2>"
  Response.Write "Project number, Requisition Number, Project Title, or Bureau Agency Code Fields."
  Response.Write "<A HREF = 'projsrch.asp'> search page </A>"
End If

if (lnSqlRun > 0) and (lnRCount = 0) Then
  rs.Close
  Set rs = Nothing
  Response.Write "<H2> No Data Matched Your Input. </H2>"
  Response.Write "<A HREF = 'projsrch.asp'> Return to search page </A>"
End If


ect

David W. Grewe
Dave@internationalbid.com

RE: ODBC -> VFP Table & SQL problem

First and foremost, you'll probably receive better response if you post this in the VBScript or ASP forums.

My initial guess is you're receiving an error like - cant perform operation while the object is open, or something to the liking.

If so, the problem is because you are recycling your variables. The first executed SQL is returned as a recordset to the variable rs. You fail to close the recordset and on the second attempt of rs.Open, you bomb because the recordset remains open.

IOW, close the recordset immediately after you no longer have a use for it, ie, after you store the recordcount in your example above. FYI - ASP-guys will always harp on you to follow that as a rule of thumb.

Jon Hawkins

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close