Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ODBC -> VFP Table & SQL problem

Status
Not open for further replies.
dgrewe

dgrewe

MIS
Dec 1, 1999
1,285
US
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(&quot;ProjNum&quot;))
proj = LTrim(proj)
proj = RTrim(proj)

reqno = UCase(Request.Form(&quot;ReqNum&quot;))
reqno = LTrim(reqno)
reqno = RTrim(reqno)

lcBac = UCase(Request.Form(&quot;BacNum&quot;))
lcBac = LTrim(lcBac)
lcBac = RTrim(lcBac)

lcAgy = UCase(Request.Form(&quot;AgName&quot;))
lcAgy = LTrim(lcAgy)
lcAgy = RTrim(lcAgy)

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

If Not IsNull(proj) And (Len(proj) > 0) And (lnSqlRun = 0) Then
lnSqlRun = 1
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
rs.CursorLocation = adUseClient
SQL = &quot;SELECT * FROM projects WHERE rtrim(Project) = '&quot; & proj & &quot;'&quot;
rs.Open SQL, &quot;DSN=bidsys&quot;, 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(&quot;ADODB.Recordset&quot;)
rs.CursorLocation = adUseClient
SQL = &quot;SELECT * FROM projects WHERE rtrim(Req_no) = '&quot; & reqno & &quot;'&quot;
rs.Open SQL, &quot;DSN=bidsys&quot;, 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(&quot;ADODB.Recordset&quot;)
rs.CursorLocation = adUseClient
SQL = &quot;SELECT * FROM projects WHERE Prjname LIKE '%&quot; & title & &quot;%'&quot;
rs.Open SQL, &quot;DSN=bidsys&quot;, 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(&quot;ADODB.Recordset&quot;)
rs.CursorLocation = adUseClient
error occurs here
SQL = &quot;SELECT * FROM bidsys WHERE Buyer = '&quot; & lcAgy & &quot;'&quot;
rs.Open SQL, &quot;DSN=bidsys1&quot;, 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(&quot;ADODB.Recordset&quot;)
rs.CursorLocation = adUseClient
error occurs here
SQL = &quot;SELECT * FROM bidsys WHERE rtrim(Agency) = '&quot; & lcBac & &quot;'&quot;
rs.Open SQL, &quot;DSN=bidsys1&quot;, adOpenKeySet, adLockOptimistic

lnRCount = rs.RecordCount
End If
End If

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

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


ect David W. Grewe
Dave@internationalbid.com
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Question
SQLEXEC crashes VFP 1
Replies
9
Views
578
Claudio Costa
C
bharons
  • Locked
  • Question
SQL Server data processing with VFP 8 1
Replies
8
Views
889
bharons
bharons
dylim
  • Question
Latest VFP Version
Replies
15
Views
469
Chriss Miller
C
bharons
  • Locked
  • Question
update data table sql server using form vfp 2
Replies
13
Views
928
bharons
bharons
bharons
  • Locked
  • Question
Update Data SQL server Using Create Cursor VFP
Replies
13
Views
1K
bharons
bharons

Part and Inventory Search

Sponsor

Back
Top