Help with Processing page

[pattersonc]

Hello! I'm new to this forum and also new to using ASP and SQL and Access. I am in need of some help. I'm trying to make a database to help my football team. I've pretty much got the whole thing done to where a coach can enter new games and various plays within those games. Now I've reached the hard part.

I need to make a process page that will help me query the database given varying situations. Below is an image of the page that the query will reside on.

I've removed the rights so you can go to the actual page at http://www.clintpatterson.com/Eagle_Db2/Admin/Db_Query.asp

Below is my startings of a process page, but I don't know how to make it actually work and my syntax is all off. Do you guys have any insight into something that could help?

Thanks

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% 
 
 
' defines page from which information is coming
<form method="get" action="Db_Query.asp">
 
 
'Variable listed below here
'--------------------------------------------------------------------
 
Results_Page = "db_query_results.asp" 
 
Dim VarGame_Title = request.form("Game_Title")
Dim VarFormation = request.form("Formation")
Dim VarDown = request.form("Down")
Dim VarDistance_Symbol = request.form("Distance_Symbol")
Dim VarDistance = request.form("Distance")
Dim VarHash = request.form("Hash")
Dim VarStrength = request.form("Strength")
Dim VarGain_Loss = request.form("Gain_Loss")
Dim VarBench = request.form("Bench")
Dim VarResult = request.form("Result")
 
 
if VarGame_Title <> "" then 
    sqlst = sqlst + "Game_Title =" + VarGame_Title
endif
 
if VarFormation <> "" then 
    sqlst = sqlst + "Formation =" + VarFormation
endif
 
if VarDown <> "" then 
    sqlst = sqlst + "Down =" + VarDown
endif  
 
if VarDistance_Symbol = "none" then
    sqlst = sqlst + "Distance_Symbol =" + VarDistance_Symbol
elseif VarDistance_Symbol = ">" then
    sqlst = sqlst + ">"
elseif VarDistance_Symbol = "<" then
    sqlst = sqlst + "<" 
elseif VarDistance_Symbol = "=" then
    sqlst = sqlst + "=" then
elseif VarDistance_Symbol = ">=" then
    sqlst = sqlst + ">="
elseif VarDistance_Symbol = "<=" then
    sqlst = sqlst + "<="
endif
 
if VarDistance <> "" then
    sqlst = sqlst + "Distance =" + VarDistance
endif
 
if VarHash <> "" then
    sqlst = sqlst + "Hash =" + VarHash
endif
 
if VarStrength <> "" then
    sqlst = sqlst + "Strength =" + VarStrength
endif
 
if VarGain_Loss <> "" then
    sqlst = sqlst + "Gain_Loss =" + VarGain_Loss
endif
 
if VarBench <> "" then
    sqlst = sqlst +    "Bench =" + VarBench
endif
 
if VarResult <> "" then
    sqlst = sqlst + "Result =" + VarResult
endif
 
 
 
 
 
 
Response.Write(sqlst)
Response.Redirect(Results_Page)
 
%>

[jhardman]

pattersonc,
It looks like your approach is valid, but you are getting a lot of syntax errors and you are not sure how to clean them all up, right? first "end if" is two words. Second, don't try to declare a variable and assign it in the same line. Try this:

dim varA, varB, varC, varD
varA = request("A")
varB = request("C")
'etc.

try those and let me know how far you get.

Jared

[pattersonc]

I've made some progress with the help of a friend. He says I needed to include the connection and then do the response.write sqlst.

How does this work?

My new code is below...

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
 
 
<% 
    for x = 1 to Request.Form.count() 
        Response.Write(Request.Form.key(x) & " = ") 
        Response.Write(Request.Form.item(x) & "<br>") 
    next 
%> 
 
 
 
<% 
'Variable listed below here
'--------------------------------------------------------------------
 
Results_Page = "db_query_results.asp" ' the page where results will be outputted
sqlst = "" 'sets main sql statement blank
 
'sets up variables in ASP from the submitted values of web page
Dim VarGame_Title, VarFormation, VarDown, VarDistance_Symbol, VarDistance, VarHash, VarStrength, VarGain_Loss, VarBench, VarResult, VarPlay, VarYardline
VarGame_Title = request.form("Game_Title")
VarFormation = request.form("Formation")
VarDown = request.form("Down")
VarDistance_Symbol = request.form("Distance_Symbol")
VarDistance = request.form("Distance")
VarHash = request.form("Hash")
VarStrength = request.form("Strength")
VarGain_Loss = request.form("Gain_Loss")
VarBench = request.form("Bench")
VarResult = request.form("Result")
VarPlay = request.Form("Play")
VarYardline = request.Form("Yardline")
%>
 
<%
If  VarGame_Title <> "" Then 
    sqlst = sqlst & "Game_Title =" + VarGame_Title
End if
%> 
 
<%
If VarFormation <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & "Formation =" + VarFormation
    else
    sqlst = sqlst & " AND Formation =" + VarFormation
End if
%> 
 
<%
If VarDown <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & "Down =" + VarDown
    else
    sqlst = sqlst & " AND Down =" + VarDown
End if
%>
 
<%
If VarDistance <> "" Then 
    If VarDistance_Symbol = "Any" Then
        sqlst = sqlst
    else
        if sqlst ="" then
        sqlst = sqlst & "Distance"+ VarDistance_Symbol + VarDistance
        else
            sqlst = sqlst & " AND Distance" + VarDistance_Symbol + VarDistance
        end if
    end if
End if
%>
 
<%
If VarHash <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & "Hash =" + VarHash
    else
    sqlst = sqlst & " AND Hash =" + VarHash
End if
%>
 
<%
If VarStrength <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & "Strength =" + VarStrength
    else
    sqlst = sqlst & " AND Strength =" + VarStrength
End if
%>
 
<%
If VarGain_Loss <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & "Gain_Loss =" + VarDown
    else
    sqlst = sqlst & " AND Gain_Loss =" + VarGain_Loss
End if
%>
 
<%    
If VarBench <> "" Then 
    if    sqlst = "" Then
    sqlst = sqlst & "Bench =" + VarBench
    Else
    sqlst = sqlst & "AND Bench =" + VarBench
End if
%>    
 
<%
If VarPlay <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & "Play =" + VarDown
    else
    sqlst = sqlst & " AND Play =" + VarPlay
End if
%>
 
<%
If VarYardline <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & "Yardline =" + VarYardline
    else
    sqlst = sqlst & " AND Yardline =" + VarYardline
End if
%>
 
<%
If VarResult <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & "Result =" + VarResult
    else
    sqlst = sqlst & " AND Result =" + VarResult
End if
%>
 
<!--#include file="../Connections/e.asp" -->
 
<%
Response.Write(sqlst)
Response.Redirect(Results_Page)
%>

[jhardman]

I've made some progress with the help of a friend. He says I needed to include the connection and then do the response.write sqlst.

How does this work?

My new code is below...

Patterson,

Looks like you are making progress - putting in the response.write line is sort of standard troubleshooting procedure - it just shows what you have done so far. However since you immediately follow it up with response.redirect you will never see what the sqlst is - as soon as you redirect you will lose everything you wrote.

Jared

BTW, please put your code in [ code] [ /code] tags (there is a button marked # provided when you post.

[pattersonc]

Ok, I'm making a little more progress now. I can get the database query to pull in all of my information from the variable constraints, but I can't get it to filter the recordset by these constraints. I'm also now getting an error message of

"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Central vs LewisvilleWishbone2nd>6Right-1Right100AwayInt'.

/Eagle_Db2/Admin/process_db_query.asp, line 163 "

Any insight into how to get my string to filter these fields?

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../Connections/e.asp" -->
<link href="../global_style.css" rel="stylesheet" type="text/css" />
 
<% 
'Variable listed below here
'--------------------------------------------------------------------
%>
<%
 
'sets up variables in ASP from the submitted values of web page
Dim VarGame_Title, VarFormation, VarDown, VarDistance_Symbol, VarDistance, VarHash, VarStrength, VarGain_Loss, VarBench, VarResult, VarPlay, VarYardline, sqlst
 
VarGame_Title = request.form("Game_Title")
VarFormation = request.form("Formation")
VarDown = request.form("Down")
VarDistance_Symbol = request.form("Distance_Symbol")
VarDistance = request.form("Distance")
VarHash = request.form("Hash")
VarStrength = request.form("Strength")
VarGain_Loss = request.form("Gain_Loss")
VarBench = request.form("Bench")
VarResult = request.form("Result")
VarPlay = request.Form("Play")
VarYardline = request.Form("Yardline")
sqlst = ""
%>
 
<%
If VarGame_Title <> "" Then 
    if sqlst ="" then
    sqlst = sqlst & VarGame_Title
    End if
End if
%>
<%
If VarFormation <> "Any" Then 
    if sqlst <> "" then
    sqlst = sqlst & VarFormation
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarDown <> "Any" Then 
    if sqlst <> "" then
    sqlst = sqlst & VarDown
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarDistance_Symbol <> "any" Then 
    if sqlst <> "" then
    sqlst = sqlst & VarDistance_Symbol & VarDistance
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarHash <> "Any" Then 
    if sqlst <> "" then
    sqlst = sqlst & VarHash
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarYardline <> "Any" Then 
    if sqlst <> "" then
    sqlst = sqlst & VarYardline
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarStrength <> "Any" Then 
    if sqlst <> "" then
    sqlst = sqlst & VarStrength
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarPlay <> "" Then 
    if sqlst <> "" Then
    sqlst = sqlst & VarPlay
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarGain_Loss <> "Any" Then 
    if sqlst <> "" Then
    sqlst = sqlst & VarGain_Loss
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarBench <> "Any" Then 
    if sqlst <> "" Then
    sqlst = sqlst & VarBench
    End if
    else
    sqlst = sqlst
    End if
%>
<%
If VarResult <> "Any" Then 
    if sqlst <> "" Then
    sqlst = sqlst & VarResult
    End if
    else
    sqlst = sqlst
    End if
%>
 
 
<p></p>
<p>&nbsp;</p>
<table width="698" border="0" align="center">
  <tr>
    <td bgcolor="#FFFFFF"><div align="center"><strong>You queried the database using the following contraints:</strong><br />
        <font color="#0000FF">
        <%Response.Write(sqlst)%>
        </font></div></td>
  </tr>
</table>
<center>
<p><br />
  <br />
</p>
<p>&nbsp;</p>
 
 
<% if sqlst <> "" then
    sqlst = " WHERE " + sqlst
    session ("statement") = sqlst
    else
        sqlst = session("statement")
    end if
%>
 
<%
Dim Play_Info
Dim Play_Info_cmd
Dim Play_Info_numRows
 
Set Play_Info_cmd = Server.CreateObject ("ADODB.Command")
Play_Info_cmd.ActiveConnection = MM_e_STRING
Play_Info_cmd.CommandText = "SELECT * FROM Play_Info" + sqlst
Play_Info_cmd.Prepared = true
 
Set Play_Info = Play_Info_cmd.Execute
Play_Info_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
 
Repeat1__numRows = -1
Repeat1__index = 0
Play_Info_numRows = Play_Info_numRows + Repeat1__numRows
%>
<table border="1" cellpadding="3" cellspacing="3">
  <tr>
    <td>Play_Id</td>
    <td>Formation</td>
    <td>Down</td>
    <td>Distance</td>
    <td>Hash</td>
    <td>Yardline</td>
    <td>Strength</td>
    <td>Play</td>
    <td>Gain_Loss</td>
    <td>Bench</td>
    <td>Result</td>
    <td>Comment</td>
    <td>Game_Id</td>
    <td>Game_Title</td>
  </tr>
  <% While ((Repeat1__numRows <> 0) AND (NOT Play_Info.EOF)) %>
    <tr>
      <td><%=(Play_Info.Fields.Item("Play_Id").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Formation").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Down").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Distance").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Hash").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Yardline").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Strength").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Play").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Gain_Loss").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Bench").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Result").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Comment").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Game_Id").Value)%></td>
      <td><%=(Play_Info.Fields.Item("Game_Title").Value)%></td>
    </tr>
    <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Play_Info.MoveNext()
Wend
%>
</table>
<p>&nbsp;</p>
 
 
 
 
<%
Play_Info.Close()
Set Play_Info = Nothing
%>
 

I appreciate any help you can offer.

[jhardman]

Ok, I'm making a little more progress now. I can get the database query to pull in all of my information from the variable constraints, but I can't get it to filter the recordset by these constraints. I'm also now getting an error message of

"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Central vs LewisvilleWishbone2nd>6Right-1Right100AwayInt'.

/Eagle_Db2/Admin/process_db_query.asp, line 163 "

Any insight into how to get my string to filter these fields?

I appreciate any help you can offer.

Easy peasy (as my kindergartner would say). Your SQL query has a syntax error. You need to do a really quick troubleshoot to figure it out, but once you see it it should be obvious. Here is what to do:

• comment out the line that queries the database
• add a response.write line which prints out the query to the browser something like this:

  response.write "Query: " & sqlst & "<br>" & vbNewLine

• Look through what got printed out and check for VERY OBVIOUS mistakes, things like forgetting "AND" or spaces or commas
• If it doesn't seem obvious, post the entire query here and we can look at it.

Let me know how it goes.

Jared