Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > asp classic > answers

Help with Processing page

Newbie
  
Join Date: Oct 2008
Location: Charlotte, NC
Posts: 3
#1: Oct 24 '08
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
Expand|Select|Wrap|Line Numbers
  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
  2. <% 
  3.  
  4.  
  5. ' defines page from which information is coming
  6. <form method="get" action="Db_Query.asp">
  7.  
  8.  
  9. 'Variable listed below here
  10. '--------------------------------------------------------------------
  11.  
  12. Results_Page = "db_query_results.asp" 
  13.  
  14. Dim VarGame_Title = request.form("Game_Title")
  15. Dim VarFormation = request.form("Formation")
  16. Dim VarDown = request.form("Down")
  17. Dim VarDistance_Symbol = request.form("Distance_Symbol")
  18. Dim VarDistance = request.form("Distance")
  19. Dim VarHash = request.form("Hash")
  20. Dim VarStrength = request.form("Strength")
  21. Dim VarGain_Loss = request.form("Gain_Loss")
  22. Dim VarBench = request.form("Bench")
  23. Dim VarResult = request.form("Result")
  24.  
  25.  
  26. if VarGame_Title <> "" then 
  27.     sqlst = sqlst + "Game_Title =" + VarGame_Title
  28. endif
  29.  
  30. if VarFormation <> "" then 
  31.     sqlst = sqlst + "Formation =" + VarFormation
  32. endif
  33.  
  34. if VarDown <> "" then 
  35.     sqlst = sqlst + "Down =" + VarDown
  36. endif  
  37.  
  38. if VarDistance_Symbol = "none" then
  39.     sqlst = sqlst + "Distance_Symbol =" + VarDistance_Symbol
  40. elseif VarDistance_Symbol = ">" then
  41.     sqlst = sqlst + ">"
  42. elseif VarDistance_Symbol = "<" then
  43.     sqlst = sqlst + "<" 
  44. elseif VarDistance_Symbol = "=" then
  45.     sqlst = sqlst + "=" then
  46. elseif VarDistance_Symbol = ">=" then
  47.     sqlst = sqlst + ">="
  48. elseif VarDistance_Symbol = "<=" then
  49.     sqlst = sqlst + "<="
  50. endif
  51.  
  52. if VarDistance <> "" then
  53.     sqlst = sqlst + "Distance =" + VarDistance
  54. endif
  55.  
  56. if VarHash <> "" then
  57.     sqlst = sqlst + "Hash =" + VarHash
  58. endif
  59.  
  60. if VarStrength <> "" then
  61.     sqlst = sqlst + "Strength =" + VarStrength
  62. endif
  63.  
  64. if VarGain_Loss <> "" then
  65.     sqlst = sqlst + "Gain_Loss =" + VarGain_Loss
  66. endif
  67.  
  68. if VarBench <> "" then
  69.     sqlst = sqlst +    "Bench =" + VarBench
  70. endif
  71.  
  72. if VarResult <> "" then
  73.     sqlst = sqlst + "Result =" + VarResult
  74. endif
  75.  
  76.  
  77.  
  78.  
  79.  
  80.  
  81. Response.Write(sqlst)
  82. Response.Redirect(Results_Page)
  83.  
  84. %>

jhardman's Avatar
Moderator
  
Join Date: Jan 2007
Location: logan, utah
Posts: 2,690
#2: Oct 27 '08

re: Help with Processing page

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:
Expand|Select|Wrap|Line Numbers
  1. dim varA, varB, varC, varD
  2. varA = request("A")
  3. varB = request("C")
  4. 'etc.
try those and let me know how far you get.

Jared
Newbie
  
Join Date: Oct 2008
Location: Charlotte, NC
Posts: 3
#3: Oct 30 '08

re: Help with Processing page

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...
Expand|Select|Wrap|Line Numbers
  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
  2.  
  3.  
  4. <% 
  5.     for x = 1 to Request.Form.count() 
  6.         Response.Write(Request.Form.key(x) & " = ") 
  7.         Response.Write(Request.Form.item(x) & "<br>") 
  8.     next 
  9. %> 
  10.  
  11.  
  12.  
  13. <% 
  14. 'Variable listed below here
  15. '--------------------------------------------------------------------
  16.  
  17. Results_Page = "db_query_results.asp" ' the page where results will be outputted
  18. sqlst = "" 'sets main sql statement blank
  19.  
  20. 'sets up variables in ASP from the submitted values of web page
  21. Dim VarGame_Title, VarFormation, VarDown, VarDistance_Symbol, VarDistance, VarHash, VarStrength, VarGain_Loss, VarBench, VarResult, VarPlay, VarYardline
  22. VarGame_Title = request.form("Game_Title")
  23. VarFormation = request.form("Formation")
  24. VarDown = request.form("Down")
  25. VarDistance_Symbol = request.form("Distance_Symbol")
  26. VarDistance = request.form("Distance")
  27. VarHash = request.form("Hash")
  28. VarStrength = request.form("Strength")
  29. VarGain_Loss = request.form("Gain_Loss")
  30. VarBench = request.form("Bench")
  31. VarResult = request.form("Result")
  32. VarPlay = request.Form("Play")
  33. VarYardline = request.Form("Yardline")
  34. %>
  35.  
  36. <%
  37. If  VarGame_Title <> "" Then 
  38.     sqlst = sqlst & "Game_Title =" + VarGame_Title
  39. End if
  40. %> 
  41.  
  42. <%
  43. If VarFormation <> "" Then 
  44.     if sqlst ="" then
  45.     sqlst = sqlst & "Formation =" + VarFormation
  46.     else
  47.     sqlst = sqlst & " AND Formation =" + VarFormation
  48. End if
  49. %> 
  50.  
  51. <%
  52. If VarDown <> "" Then 
  53.     if sqlst ="" then
  54.     sqlst = sqlst & "Down =" + VarDown
  55.     else
  56.     sqlst = sqlst & " AND Down =" + VarDown
  57. End if
  58. %>
  59.  
  60. <%
  61. If VarDistance <> "" Then 
  62.     If VarDistance_Symbol = "Any" Then
  63.         sqlst = sqlst
  64.     else
  65.         if sqlst ="" then
  66.         sqlst = sqlst & "Distance"+ VarDistance_Symbol + VarDistance
  67.         else
  68.             sqlst = sqlst & " AND Distance" + VarDistance_Symbol + VarDistance
  69.         end if
  70.     end if
  71. End if
  72. %>
  73.  
  74. <%
  75. If VarHash <> "" Then 
  76.     if sqlst ="" then
  77.     sqlst = sqlst & "Hash =" + VarHash
  78.     else
  79.     sqlst = sqlst & " AND Hash =" + VarHash
  80. End if
  81. %>
  82.  
  83. <%
  84. If VarStrength <> "" Then 
  85.     if sqlst ="" then
  86.     sqlst = sqlst & "Strength =" + VarStrength
  87.     else
  88.     sqlst = sqlst & " AND Strength =" + VarStrength
  89. End if
  90. %>
  91.  
  92. <%
  93. If VarGain_Loss <> "" Then 
  94.     if sqlst ="" then
  95.     sqlst = sqlst & "Gain_Loss =" + VarDown
  96.     else
  97.     sqlst = sqlst & " AND Gain_Loss =" + VarGain_Loss
  98. End if
  99. %>
  100.  
  101. <%    
  102. If VarBench <> "" Then 
  103.     if    sqlst = "" Then
  104.     sqlst = sqlst & "Bench =" + VarBench
  105.     Else
  106.     sqlst = sqlst & "AND Bench =" + VarBench
  107. End if
  108. %>    
  109.  
  110. <%
  111. If VarPlay <> "" Then 
  112.     if sqlst ="" then
  113.     sqlst = sqlst & "Play =" + VarDown
  114.     else
  115.     sqlst = sqlst & " AND Play =" + VarPlay
  116. End if
  117. %>
  118.  
  119. <%
  120. If VarYardline <> "" Then 
  121.     if sqlst ="" then
  122.     sqlst = sqlst & "Yardline =" + VarYardline
  123.     else
  124.     sqlst = sqlst & " AND Yardline =" + VarYardline
  125. End if
  126. %>
  127.  
  128. <%
  129. If VarResult <> "" Then 
  130.     if sqlst ="" then
  131.     sqlst = sqlst & "Result =" + VarResult
  132.     else
  133.     sqlst = sqlst & " AND Result =" + VarResult
  134. End if
  135. %>
  136.  
  137. <!--#include file="../Connections/e.asp" -->
  138.  
  139. <%
  140. Response.Write(sqlst)
  141. Response.Redirect(Results_Page)
  142. %>
jhardman's Avatar
Moderator
  
Join Date: Jan 2007
Location: logan, utah
Posts: 2,690
#4: Oct 30 '08

re: Help with Processing page

Quote:

Originally Posted by 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...

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.
Newbie
  
Join Date: Oct 2008
Location: Charlotte, NC
Posts: 3
#5: Nov 4 '08

re: Help with Processing page

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?


Expand|Select|Wrap|Line Numbers
  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
  2. <!--#include file="../Connections/e.asp" -->
  3. <link href="../global_style.css" rel="stylesheet" type="text/css" />
  4.  
  5. <% 
  6. 'Variable listed below here
  7. '--------------------------------------------------------------------
  8. %>
  9. <%
  10.  
  11. 'sets up variables in ASP from the submitted values of web page
  12. Dim VarGame_Title, VarFormation, VarDown, VarDistance_Symbol, VarDistance, VarHash, VarStrength, VarGain_Loss, VarBench, VarResult, VarPlay, VarYardline, sqlst
  13.  
  14. VarGame_Title = request.form("Game_Title")
  15. VarFormation = request.form("Formation")
  16. VarDown = request.form("Down")
  17. VarDistance_Symbol = request.form("Distance_Symbol")
  18. VarDistance = request.form("Distance")
  19. VarHash = request.form("Hash")
  20. VarStrength = request.form("Strength")
  21. VarGain_Loss = request.form("Gain_Loss")
  22. VarBench = request.form("Bench")
  23. VarResult = request.form("Result")
  24. VarPlay = request.Form("Play")
  25. VarYardline = request.Form("Yardline")
  26. sqlst = ""
  27. %>
  28.  
  29. <%
  30. If VarGame_Title <> "" Then 
  31.     if sqlst ="" then
  32.     sqlst = sqlst & VarGame_Title
  33.     End if
  34. End if
  35. %>
  36. <%
  37. If VarFormation <> "Any" Then 
  38.     if sqlst <> "" then
  39.     sqlst = sqlst & VarFormation
  40.     End if
  41.     else
  42.     sqlst = sqlst
  43.     End if
  44. %>
  45. <%
  46. If VarDown <> "Any" Then 
  47.     if sqlst <> "" then
  48.     sqlst = sqlst & VarDown
  49.     End if
  50.     else
  51.     sqlst = sqlst
  52.     End if
  53. %>
  54. <%
  55. If VarDistance_Symbol <> "any" Then 
  56.     if sqlst <> "" then
  57.     sqlst = sqlst & VarDistance_Symbol & VarDistance
  58.     End if
  59.     else
  60.     sqlst = sqlst
  61.     End if
  62. %>
  63. <%
  64. If VarHash <> "Any" Then 
  65.     if sqlst <> "" then
  66.     sqlst = sqlst & VarHash
  67.     End if
  68.     else
  69.     sqlst = sqlst
  70.     End if
  71. %>
  72. <%
  73. If VarYardline <> "Any" Then 
  74.     if sqlst <> "" then
  75.     sqlst = sqlst & VarYardline
  76.     End if
  77.     else
  78.     sqlst = sqlst
  79.     End if
  80. %>
  81. <%
  82. If VarStrength <> "Any" Then 
  83.     if sqlst <> "" then
  84.     sqlst = sqlst & VarStrength
  85.     End if
  86.     else
  87.     sqlst = sqlst
  88.     End if
  89. %>
  90. <%
  91. If VarPlay <> "" Then 
  92.     if sqlst <> "" Then
  93.     sqlst = sqlst & VarPlay
  94.     End if
  95.     else
  96.     sqlst = sqlst
  97.     End if
  98. %>
  99. <%
  100. If VarGain_Loss <> "Any" Then 
  101.     if sqlst <> "" Then
  102.     sqlst = sqlst & VarGain_Loss
  103.     End if
  104.     else
  105.     sqlst = sqlst
  106.     End if
  107. %>
  108. <%
  109. If VarBench <> "Any" Then 
  110.     if sqlst <> "" Then
  111.     sqlst = sqlst & VarBench
  112.     End if
  113.     else
  114.     sqlst = sqlst
  115.     End if
  116. %>
  117. <%
  118. If VarResult <> "Any" Then 
  119.     if sqlst <> "" Then
  120.     sqlst = sqlst & VarResult
  121.     End if
  122.     else
  123.     sqlst = sqlst
  124.     End if
  125. %>
  126.  
  127.  
  128. <p></p>
  129. <p>&nbsp;</p>
  130. <table width="698" border="0" align="center">
  131.   <tr>
  132.     <td bgcolor="#FFFFFF"><div align="center"><strong>You queried the database using the following contraints:</strong><br />
  133.         <font color="#0000FF">
  134.         <%Response.Write(sqlst)%>
  135.         </font></div></td>
  136.   </tr>
  137. </table>
  138. <center>
  139. <p><br />
  140.   <br />
  141. </p>
  142. <p>&nbsp;</p>
  143.  
  144.  
  145. <% if sqlst <> "" then
  146.     sqlst = " WHERE " + sqlst
  147.     session ("statement") = sqlst
  148.     else
  149.         sqlst = session("statement")
  150.     end if
  151. %>
  152.  
  153. <%
  154. Dim Play_Info
  155. Dim Play_Info_cmd
  156. Dim Play_Info_numRows
  157.  
  158. Set Play_Info_cmd = Server.CreateObject ("ADODB.Command")
  159. Play_Info_cmd.ActiveConnection = MM_e_STRING
  160. Play_Info_cmd.CommandText = "SELECT * FROM Play_Info" + sqlst
  161. Play_Info_cmd.Prepared = true
  162.  
  163. Set Play_Info = Play_Info_cmd.Execute
  164. Play_Info_numRows = 0
  165. %>
  166. <%
  167. Dim Repeat1__numRows
  168. Dim Repeat1__index
  169.  
  170. Repeat1__numRows = -1
  171. Repeat1__index = 0
  172. Play_Info_numRows = Play_Info_numRows + Repeat1__numRows
  173. %>
  174. <table border="1" cellpadding="3" cellspacing="3">
  175.   <tr>
  176.     <td>Play_Id</td>
  177.     <td>Formation</td>
  178.     <td>Down</td>
  179.     <td>Distance</td>
  180.     <td>Hash</td>
  181.     <td>Yardline</td>
  182.     <td>Strength</td>
  183.     <td>Play</td>
  184.     <td>Gain_Loss</td>
  185.     <td>Bench</td>
  186.     <td>Result</td>
  187.     <td>Comment</td>
  188.     <td>Game_Id</td>
  189.     <td>Game_Title</td>
  190.   </tr>
  191.   <% While ((Repeat1__numRows <> 0) AND (NOT Play_Info.EOF)) %>
  192.     <tr>
  193.       <td><%=(Play_Info.Fields.Item("Play_Id").Value)%></td>
  194.       <td><%=(Play_Info.Fields.Item("Formation").Value)%></td>
  195.       <td><%=(Play_Info.Fields.Item("Down").Value)%></td>
  196.       <td><%=(Play_Info.Fields.Item("Distance").Value)%></td>
  197.       <td><%=(Play_Info.Fields.Item("Hash").Value)%></td>
  198.       <td><%=(Play_Info.Fields.Item("Yardline").Value)%></td>
  199.       <td><%=(Play_Info.Fields.Item("Strength").Value)%></td>
  200.       <td><%=(Play_Info.Fields.Item("Play").Value)%></td>
  201.       <td><%=(Play_Info.Fields.Item("Gain_Loss").Value)%></td>
  202.       <td><%=(Play_Info.Fields.Item("Bench").Value)%></td>
  203.       <td><%=(Play_Info.Fields.Item("Result").Value)%></td>
  204.       <td><%=(Play_Info.Fields.Item("Comment").Value)%></td>
  205.       <td><%=(Play_Info.Fields.Item("Game_Id").Value)%></td>
  206.       <td><%=(Play_Info.Fields.Item("Game_Title").Value)%></td>
  207.     </tr>
  208.     <% 
  209.   Repeat1__index=Repeat1__index+1
  210.   Repeat1__numRows=Repeat1__numRows-1
  211.   Play_Info.MoveNext()
  212. Wend
  213. %>
  214. </table>
  215. <p>&nbsp;</p>
  216.  
  217.  
  218.  
  219.  
  220. <%
  221. Play_Info.Close()
  222. Set Play_Info = Nothing
  223. %>
  224.  
I appreciate any help you can offer.
jhardman's Avatar
Moderator
  
Join Date: Jan 2007
Location: logan, utah
Posts: 2,690
#6: Nov 4 '08

re: Help with Processing page

Quote:

Originally Posted by 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?

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:
    Expand|Select|Wrap|Line Numbers
    1. 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
Reply

« previous question | next question »

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,449 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved