By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,089 Members | 1,488 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,089 IT Pros & Developers. It's quick & easy.

Problem passing multiple form variables for search form

P: 87
Greetings,

Perhaps someone can help me resolve what I hope is a simple dilemma...

I have one ASP page (TEST_RoadmapData_Search.asp) which contains two list/menus within a form, which, upon submission, feeds form variables (SSPA_MDMgr and Program_Name) to a second ASP page (TEST_RoadmapData.asp), which then populates a Repeat Region recordset in a table that is qualified by the form variables.

The form is designed for the user to search for records based on the values selected in the list/menus. My dilemma is that everything works great when I make a selection in both list/menus on the first page. However, if I were to make a selection in just one list/menu and submit the form, nothing is returned in the recordset, even though I know there are results based on the selection.

I suspect the problem lies in the SQL statement for the Repeat Region recordset. I'm not sure what you might need to identify the problem, so I've started by posting the SQL code and DIM variables. I can provide more code if necessary.

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim RoadmapData__MMColParam1
  3. RoadmapData__MMColParam1 = "Yes"
  4. If (Request("MM_EmptyValue") <> "") Then 
  5.   RoadmapData__MMColParam1 = Request("MM_EmptyValue")
  6. End If
  7. %>
  8. <%
  9. Dim RoadmapData__MMColParam2
  10. RoadmapData__MMColParam2 = "1"
  11. If (Request.Form("SSPA_MDMgr") <> "") Then 
  12.   RoadmapData__MMColParam2 = Request.Form("SSPA_MDMgr")
  13. End If
  14. %>
  15. <%
  16. Dim RoadmapData__MMColParam3
  17. RoadmapData__MMColParam3 = "1"
  18. If (Request.Form("Program_Name") <> "") Then 
  19.   RoadmapData__MMColParam3 = Request.Form("Program_Name")
  20. End If
  21. %>
  22. <%
  23. Dim RoadmapData
  24. Dim RoadmapData_cmd
  25. Dim RoadmapData_numRows
  26.  
  27. Set RoadmapData_cmd = Server.CreateObject ("ADODB.Command")
  28. RoadmapData_cmd.ActiveConnection = MM_EZTrackingTool_STRING
  29. RoadmapData_cmd.CommandText = "SELECT * FROM EZTracking_MASTER WHERE RoadmapView = ? AND (SSPA_Org = ?) AND (Program_Name = ?) ORDER BY ID ASC" 
  30. RoadmapData_cmd.Prepared = true
  31. RoadmapData_cmd.Parameters.Append RoadmapData_cmd.CreateParameter("param1", 200, 1, 255, RoadmapData__MMColParam1) ' adVarChar
  32. RoadmapData_cmd.Parameters.Append RoadmapData_cmd.CreateParameter("param2", 200, 1, 255, RoadmapData__MMColParam2) ' adVarChar
  33. RoadmapData_cmd.Parameters.Append RoadmapData_cmd.CreateParameter("param3", 200, 1, 255, RoadmapData__MMColParam3) ' adVarChar
  34.  
  35. Set RoadmapData = RoadmapData_cmd.Execute
  36. RoadmapData_numRows = 0
  37. %>
Ideally, what I want is for the user to make one OR two selections from the initial form and return values to the second page.

I hope this makes sense. Whatever assistance you can provide would be greatly appreciated.

Regards,

JM
Aug 1 '08 #1
Share this Question
Share on Google+
9 Replies


DrBunchman
Expert 100+
P: 979
Hi JM,

The problem is indeed the SQL Statement. When you only select one of the drop downs the blank value of the other drop down is still passed into the sql statement as a parameter. You need to put some conditions on your statement when you build it, like this:
Expand|Select|Wrap|Line Numbers
  1. Dim sSQL
  2. sSQL = "SELECT * FROM EZTracking_MASTER WHERE RoadmapView = ?"
  3. If Not RoadmapData__MMColParam2 = "" Then
  4.      sSQL = sSQL & " AND (SSPA_Org = ?) "
  5. End If
  6. If Not RoadmapData__MMColParam3 = "" Then
  7.      sSQL = sSQL & " AND (Program_Name = ?) "
  8. End If
  9. sSQL = " ORDER BY ID ASC " 
  10. RoadmapData_cmd.CommandText = sSQL
Then you need to check again whether the variables are blank before you add each of the parameters.

Does this make sense? Let me know how you get on.

Dr B
Aug 4 '08 #2

P: 87
It does make sense, but I could use your trained eye to review my SQL (particularly with checking again whether the variables are blank before adding the parameters).

Here's what I have:

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim sSQL
  3. Dim RoadmapData
  4. Dim RoadmapData_cmd
  5. Dim RoadmapData_numRows
  6.  
  7. Set RoadmapData_cmd = Server.CreateObject ("ADODB.Command")
  8. RoadmapData_cmd.ActiveConnection = MM_EZTrackingTool_STRING
  9. sSQL = "SELECT * FROM EZTracking_MASTER WHERE RoadmapView = ?"
  10.     If Not RoadmapData__MMColParam2 = "" THEN
  11.     sSQL = sSQL & " AND (SSPA_Org = ?)"
  12. End If
  13.     If Not RoadmapData__MMColParam3 = "" Then
  14.     sSQL = sSQL & " AND (Program_Name = ?)"
  15. End If
  16. sSQL = "ORDER BY ID ASC" 
  17. RoadmapData_cmd.CommandText = sSQL
  18. RoadmapData_cmd.Prepared = true
  19. RoadmapData_cmd.Parameters.Append RoadmapData_cmd.CreateParameter("param1", 200, 1, 255, RoadmapData__MMColParam1) ' adVarChar
  20.     If Not RoadmapData__MMColParam2 = "" THEN
  21.     RoadmapData_cmd.Parameters.Append RoadmapData_cmd.CreateParameter("param2", 200, 1, 255, RoadmapData__MMColParam2) ' adVarChar
  22. End If
  23.     If Not RoadmapData__MMColParam3 = "" Then
  24.     RoadmapData_cmd.Parameters.Append RoadmapData_cmd.CreateParameter("param3", 200, 1, 255, RoadmapData__MMColParam3) ' adVarChar
  25. End If
  26.  
  27. Set RoadmapData = RoadmapData_cmd.Execute
  28. RoadmapData_numRows = 0
  29. %>
Also, I think there's something wrong with this part of the SQL code...

Expand|Select|Wrap|Line Numbers
  1. Set RoadmapData = RoadmapData_cmd.Execute
...because I get the error below, which points to this line in the code:

Expand|Select|Wrap|Line Numbers
  1. Error Type:
  2. Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
  3. [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
  4. /ez/TEST_RoadmapData.asp, line 55
Thanks for taking time to help me. Your advice has been most beneficial!

Regards,

JM
Aug 4 '08 #3

DrBunchman
Expert 100+
P: 979
Hi JM,

The top bit looks absolutely fine - you'll soon find out if there are any bugs when you get it working but I think it should be okay.

What is the variable RoadmapData? Is it a recordset because if so I don't see you define it as one anywhere. Try replacing the line that errors with:
Expand|Select|Wrap|Line Numbers
  1. Set RoadmapData= Server.CreateObject(“ADODB.Recordset”)
  2. RoadmapData.Open RoadmapData_cmd
Does that work now?

Dr B
Aug 5 '08 #4

P: 87
Didn't work, Dr. B. I get the same error as before, this time on this line of code:

Expand|Select|Wrap|Line Numbers
  1. RoadmapData.Open RoadmapData_cmd
Any suggestions?

JM
Aug 5 '08 #5

DrBunchman
Expert 100+
P: 979
If you output the sql string sSQL to the screen instead of trying to open the recordset what does it look like?
Aug 5 '08 #6

DrBunchman
Expert 100+
P: 979
Another thought: try explicitly setting the command type of your command object to 1 (which defines it as command text).
Expand|Select|Wrap|Line Numbers
  1. RoadmapData_cmd.CommandType = 1
Any joy with that?
Aug 5 '08 #7

P: 87
No joy, unfortunately. I'm still getting the same error on the same line. I'm afraid we're getting into unfamiliar territory (for me, anyway), but I'll keep troubleshooting.

JM
Aug 5 '08 #8

DrBunchman
Expert 100+
P: 979
If you don't use the command object to open the recordset do you still get the error? e.g.
Expand|Select|Wrap|Line Numbers
  1. sSQL = "SELECT etc"
  2. conn = Server.CreateObject("ADODB.Connection")
  3. oRS = Server.CreateObject("ADODB.RecordSet")
  4. conn.Open CONNECTION_STRING
  5. oRS.Open sSQL, conn
If so then perhaps the connection string is not quite right? Sorry JM that I don't have an answer but hopefully we can try to isolate the problem.

Dr B
Aug 6 '08 #9

P: 87
I've been pulled off on another project for the time being, but will revisit this sometime soon. I'll repost when your feedback is needed.

Thanks again, Dr B, for your superb assistance.

JM
Aug 7 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.