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

Running a search then auto-aubmitting the results to be edited

P: 68
I am creating an ASP object that will recieve a table, a field for the table and the search text. I already have "edit objects" created for each table in my DB. what i need is a way to submit the page to the specific edit object - based on the table that the user selects. The user will submit his search which would call the search form, wich in turn would query the DB then submit the record found to the relavant edit object. The only thing the user sees is the edit form, the search and submit must be done in the background.

So here is where I'm stuck.

Expand|Select|Wrap|Line Numbers
  1. <% 
  2. Dim myTable=request.querystring("Table")
  3. Dim myField=request.querystring("Field")
  4. Dim mySchTxt=request.querystring("schTxt")
  5.  
  6. '-- Create object and open database
  7.  
  8. Set DataConnection = Server.CreateObject("ADODB.Connection")
  9. DataConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;  Data Source="& Server.MapPath("/DMS/ISDAssetsDatabase3_be.mdb"))
  10.  
  11. Set cmdDC = Server.CreateObject("ADODB.Command")
  12. cmdDC.ActiveConnection = DataConnection
  13.  
  14. '-- default SQL
  15.  
  16. SQL = "SELECT * FROM myTable"
  17.  
  18. '-- RecordToEdit SQL
  19.  
  20. If mySchTxt <> "" Then
  21.  
  22.         SQL = "SELECT myTable.myfield, myTable.* FROM myTable WHERE (((myTable.myfield)=" & myScrTxt & "));"
  23.  
  24. End If
  25.  
  26. cmdDC.CommandText = SQL
  27. Set RecordSet = Server.CreateObject("ADODB.Recordset")
  28.  
  29. if myTable= "[Maintenance History]" Then
Here i should be writing some code to auto submit the ID of the record to MH_Edit. Can someone help please.
Jun 4 '08 #1
Share this Question
Share on Google+
9 Replies


jeffstl
Expert 100+
P: 432
I do understand what you want to accomplish. All you need to do is a select case on the table name in order to direct the user to whatever page you need, and use the querystring of that redirect to pass the parameter you need.

Like this:
Expand|Select|Wrap|Line Numbers
  1. Select Case MyTable
  2.      Case "Maintenance History"
  3.           response.redirect("MH_edit.asp?id=MyRS("TableId"))
  4.      Case "Other Table"
  5.  
  6. End Select
  7.  
  8.  

However, having said that I am seeing multiple serious problems with your code. First you cannot define a column Select in a SQL string the way you have it. You have to cancatonate the string together like this:

Expand|Select|Wrap|Line Numbers
  1. sqlstr = "Select " & MyField & " FROM " & MyTable & ""
  2.  
Also you have a typo in your string where myScrTxt should be mySchTxt

Then another problem you have is that you never actually populate any recordset in this code. You set up SQL strings, ADODB recordsets, and a dataconnection but you never actually execute the sql anywhere in the code you posted.

You have to do this to execute it or something like this:

Expand|Select|Wrap|Line Numbers
  1. Set MyRS = Server.CreateObject("ADODB.Recordset")
  2. MyRS.Open MySQLString, DataConnection, 3, 3
  3. 'do your code
  4. MyRS.Close
  5.  
Now when you have the recordset populated you can obtain the data by using the MyField = MyRS("tablecolumn")

You should try to execute the page or test it and get all these bugs out before you continue with your logic problem. In fact I would recommend testing your pages frequently as you develop so that you can catch problems as you inadvertently create them.
Jun 4 '08 #2

P: 68
Thanks a lot for the help so far.
I did run into one more problem trying to impliment the code you showed me.
Here is my code

Expand|Select|Wrap|Line Numbers
  1. <% 
  2. Dim myTable
  3. myTable= request.querystring("Table")
  4. Dim myField
  5. myField= request.querystring("Field")
  6. Dim mySchTxt
  7. mySchTxt= request.querystring("schTxt")
  8. '-- Create object and open database
  9.  
  10. Set DataConnection = Server.CreateObject("ADODB.Connection")
  11. DataConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;  Data Source="& Server.MapPath("/DMS/ISDAssetsDatabase3_be.mdb"))
  12.  
  13. Set cmdDC = Server.CreateObject("ADODB.Command")
  14. cmdDC.ActiveConnection = DataConnection
  15.  
  16. '-- default SQL
  17.  
  18. SQL = "SELECT * FROM "& myTable &""
  19.  
  20. '-- RecordToEdit SQL
  21.  
  22. If mySchTxt <> "" Then
  23.  
  24.         SQL = "SELECT "& myTable &"." & myfield & ", "& myTable &".* FROM "& myTable &" WHERE ((("& myTable & "." & myfield &")="& mySchTxt &"));"
  25.  
  26. End If
  27. cmdDC.CommandText = SQL
  28. Set RecordSet = Server.CreateObject("ADODB.Recordset")
  29. RecordSet.Open cmdDC, , 3, 3
  30. Select Case myTable
  31.      Case "[Maintenance History]"
  32.           response.redirect("MH_edit.asp?ID=RecordSet("ID")")
  33.  
  34.      Case "[Hardware Assets]"
  35.            response.redirect("HWAsset_edit.asp?HardwareAssetID=RecordSet("HardwareAssetID")")
  36.  
  37.      Case "[Software Assets]"
  38.            response.redirect("SWAsset_edit.asp?Software_Asset_ID=RecordSet("Software_Asset_ID")")
  39.  
  40.      Case "SsupportLogs"
  41.            response.redirect("SSL_Edit.asp?Software_Asset_ID=RecordSet("TicketID")")
  42. End Select
  43. Set RecordSet = Nothing
  44.  
  45. Set cmdDC = Nothing
  46. DataConnection.Close
  47. Set DataConnection = Nothing 
  48. %>
Here is the error that i recieved

Expand|Select|Wrap|Line Numbers
  1. Microsoft VBScript compilation error '800a03ee' 
  2.  
  3. Expected ')' 
  4.  
  5. /DMS/TestingStuff/ALL_Search.asp, line 40 
  6.  
  7. response.redirect("MH_edit.asp?ID=RecordSet("ID")")
  8. ---------------------------------------------^
I have been trying all sorts of stuff for the pass 2 hrs without success
Jun 4 '08 #3

jeffstl
Expert 100+
P: 432
Ok. The SQL isnt quite right still. You don't really need to specify the table in your SQL unless your querying 2 tables at once. Also the only place the table needs to be is after the FROM.
(SQL syntax is SELECT field FROM table WHERE condition)

Also data types in table are important for syntax. If your data type is a number you need to use " & myScrTxt & " and if it is a string you need to use '" & myScrTxt & "'

Below I am assuming it is a string data type. I am also simplifying the sql a bit, but this is what you want still.

You don't really need all the ( ) parans. Those parans are used to contain large complex querys where this is very simple you don't really need them. All you should need is below. If you want to know though it is likely the error is from a missing ) at the end of your statement, but I didnt count them to see ;-)
Expand|Select|Wrap|Line Numbers
  1.  
  2.         SQL = "SELECT " & myfield & " FROM " & myTable & " WHERE " & myfield & " = '" & myScrTxt & "'"
  3.  
  4.  
  5.  
One other thing about your select case. I dont know how myTable is being passed but you dont really need the [ ] either. To make sure you can do a
Expand|Select|Wrap|Line Numbers
  1. response.write myTable 
  2. response.end
  3.  
This is to basically "see" whats actually in myTable to make sure your Select Case is going to work.

I think those [ ] are probably something you got from Access at one point or another but you don't really need them. But again Im not sure what your passing from your search page exactly. If you are in fact including the [ ] you will need them i guess.
Jun 4 '08 #4

jeffstl
Expert 100+
P: 432
One more thing too, if you keep having problems try doing a

Expand|Select|Wrap|Line Numbers
  1. response.write SQL
  2. response.end
  3.  
AFTER you populate your SQL string to see whats actually the final SQL. This can help you troubleshoot as well.
Jun 4 '08 #5

P: 68
I tried your suggestion but no luck the same error keeps popping up. It does look like a paran is missing but i cant tell where. here is another thought.Do i need a semicolan at the end of my sql statements? I tried that but it didn't solve this prob. just thinking that it might cause some other somewhere else.
Jun 4 '08 #6

P: 68
The SQL statement is fine as is. it is reading all the values as it should but when i go back to the normal the error pops up again. there is probadly something wrong with the Select Case statement.
Jun 4 '08 #7

jeffstl
Expert 100+
P: 432
The SQL statement is fine as is. it is reading all the values as it should but when i go back to the normal the error pops up again. there is probadly something wrong with the Select Case statement.
Oh. Yeah...there is.

You need to cancatonate those as well


response.redirect("MH_edit.asp?ID=" & RecordSet("ID") & "")

Sorry.

All of them should be like that
Jun 4 '08 #8

P: 68
got it. thanks man.
there was one proplem with my query string in this code. I should have been selecting all the records instead what i have after the select statement

Thanks again for your patients and knowledge man.

Be right back soon with more problems

Here is the corrected statement - just incase someone use this post to help them out... you never know.

Expand|Select|Wrap|Line Numbers
  1. <% 
  2. Dim myTable
  3. myTable= request.querystring("Table")
  4. Dim myField
  5. myField= request.querystring("Field")
  6. Dim mySchTxt
  7. mySchTxt= request.querystring("schTxt")
  8. '-- Create object and open database
  9.  
  10. Set DataConnection = Server.CreateObject("ADODB.Connection")
  11. DataConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;  Data Source="& Server.MapPath("/DMS/ISDAssetsDatabase3_be.mdb"))
  12.  
  13. Set cmdDC = Server.CreateObject("ADODB.Command")
  14. cmdDC.ActiveConnection = DataConnection
  15.  
  16. '-- default SQL
  17.  
  18. SQL = "SELECT * FROM "& myTable &""
  19.  
  20. '-- RecordToEdit SQL
  21.  
  22. If mySchTxt <> "" Then
  23. SQL = "SELECT * FROM " & myTable & " WHERE " & myfield & " = '" & mySchTxt & "'"
  24. End If
  25. cmdDC.CommandText = SQL
  26. Set RecordSet = Server.CreateObject("ADODB.Recordset")
  27. RecordSet.Open cmdDC, , 3, 3
  28. 'response.write SQL
  29. 'response.end
  30. Select Case myTable
  31.      Case "[Maintenance History]":
  32.            response.redirect("MH_Edit.asp?ID="& RecordSet("ID") &"")
  33.  
  34.      Case "[Hardware Assets]":
  35.            response.redirect("HWAsset_edit.asp?HardwareAssetID="& RecordSet("HardwareAssetID")&"")
  36.  
  37.      Case "[Software Assets]":
  38.            response.redirect("SWAsset_edit.asp?Software_Asset_ID="& RecordSet("Software_Asset_ID")&"")
  39.  
  40.      Case "SsupportLogs":
  41.            response.redirect("SSL_Edit.asp?Software_Asset_ID="& RecordSet("TicketID")&"")
  42. End Select
  43. Set RecordSet = Nothing
  44.  
  45. Set cmdDC = Nothing
  46. DataConnection.Close
  47. Set DataConnection = Nothing 
  48. %>
Jun 5 '08 #9

jeffstl
Expert 100+
P: 432
No problem.

Good Luck!
Jun 5 '08 #10

Post your reply

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