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_...n/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.redire ct 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 LewisvilleWishb one2nd>6Right-1Right100AwayIn t'.

/Eagle_Db2/Admin/process_db_quer y.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 LewisvilleWishb one2nd>6Right-1Right100AwayIn t'.

/Eagle_Db2/Admin/process_db_quer y.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