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, VBasic Script -> VFoxPro with SQL question

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 ".asp" 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 is Projects, If it finds records in that table it wants a second SQL of the results of the cursor file

I can not get the code to work correctly, Can anyone point out the errors of my ways?
[tt]
<% 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

if (lnSqlRun > 0) and (lnRCount > 0) Then
etc
David W. Grewe
Dave@internationalbid.com
 
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
FILL RICHTEXTBOX WITH RECORDSET
Replies
4
Views
505
strongm
strongm
sal21
  • Locked
  • Question
send email with a free sever smpt api rest
Replies
3
Views
458
strongm
strongm
sal21
  • Locked
  • Question
Check if mobile number exists, with Api rest or other service. Possibile? 1
Replies
2
Views
488
sal21
sal21
2009luca
  • Locked
  • Question
TWO table with the same classname 1
Replies
3
Views
601
Andrzejek
Andrzejek
2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
1K
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top