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

[KingKen]

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.

<% 
Dim myTable=request.querystring("Table")
Dim myField=request.querystring("Field")
Dim mySchTxt=request.querystring("schTxt")

'-- Create object and open database

Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;  Data Source="& Server.MapPath("/DMS/ISDAssetsDatabase3_be.mdb"))

Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

'-- default SQL

SQL = "SELECT * FROM myTable"

'-- RecordToEdit SQL

If mySchTxt <> "" Then
	
		SQL = "SELECT myTable.myfield, myTable.* FROM myTable WHERE (((myTable.myfield)=" & myScrTxt & "));"

End If

cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")

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.

[jeffstl]

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:

Select Case MyTable
     Case "Maintenance History"
          response.redirect("MH_edit.asp?id=MyRS("TableId"))
     Case "Other Table"

End Select

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:

sqlstr = "Select " & MyField & " FROM " & MyTable & ""

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:

Set MyRS = Server.CreateObject("ADODB.Recordset")
MyRS.Open MySQLString, DataConnection, 3, 3
'do your code
MyRS.Close

Now when you have the recordset populated you can obtain the data by using the MyField = MyRS("tablecolu mn")

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.

[KingKen]

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

<% 
Dim myTable
myTable= request.querystring("Table")
Dim myField
myField= request.querystring("Field")
Dim mySchTxt
mySchTxt= request.querystring("schTxt")
'-- Create object and open database

Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;  Data Source="& Server.MapPath("/DMS/ISDAssetsDatabase3_be.mdb"))

Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

'-- default SQL

SQL = "SELECT * FROM "& myTable &""

'-- RecordToEdit SQL

If mySchTxt <> "" Then
	
		SQL = "SELECT "& myTable &"." & myfield & ", "& myTable &".* FROM "& myTable &" WHERE ((("& myTable & "." & myfield &")="& mySchTxt &"));"

End If
cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open cmdDC, , 3, 3
Select Case myTable
     Case "[Maintenance History]"
          response.redirect("MH_edit.asp?ID=RecordSet("ID")")
     
     Case "[Hardware Assets]"
           response.redirect("HWAsset_edit.asp?HardwareAssetID=RecordSet("HardwareAssetID")")
     
     Case "[Software Assets]"
           response.redirect("SWAsset_edit.asp?Software_Asset_ID=RecordSet("Software_Asset_ID")")
     
     Case "SsupportLogs"
           response.redirect("SSL_Edit.asp?Software_Asset_ID=RecordSet("TicketID")")
End Select
Set RecordSet = Nothing

Set cmdDC = Nothing
DataConnection.Close
Set DataConnection = Nothing 
%>

Here is the error that i recieved

Microsoft VBScript compilation error '800a03ee' 

Expected ')' 

/DMS/TestingStuff/ALL_Search.asp, line 40 

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

I have been trying all sorts of stuff for the pass 2 hrs without success

[jeffstl]

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 ;-)

        SQL = "SELECT " & myfield & " FROM " & myTable & " WHERE " & myfield & " = '" & myScrTxt & "'"

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

response.write myTable 
response.end

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.

[jeffstl]

One more thing too, if you keep having problems try doing a

response.write SQL
response.end

AFTER you populate your SQL string to see whats actually the final SQL. This can help you troubleshoot as well.

[KingKen]

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.

[KingKen]

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.

[jeffstl]

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.redire ct("MH_edit.asp ?ID=" & RecordSet("ID") & "")

Sorry.

All of them should be like that

[KingKen]

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.

<% 
Dim myTable
myTable= request.querystring("Table")
Dim myField
myField= request.querystring("Field")
Dim mySchTxt
mySchTxt= request.querystring("schTxt")
'-- Create object and open database

Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;  Data Source="& Server.MapPath("/DMS/ISDAssetsDatabase3_be.mdb"))

Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

'-- default SQL

SQL = "SELECT * FROM "& myTable &""

'-- RecordToEdit SQL

If mySchTxt <> "" Then
SQL = "SELECT * FROM " & myTable & " WHERE " & myfield & " = '" & mySchTxt & "'"
End If
cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open cmdDC, , 3, 3
'response.write SQL
'response.end
Select Case myTable
     Case "[Maintenance History]":
           response.redirect("MH_Edit.asp?ID="& RecordSet("ID") &"")
     
     Case "[Hardware Assets]":
           response.redirect("HWAsset_edit.asp?HardwareAssetID="& RecordSet("HardwareAssetID")&"")
     
     Case "[Software Assets]":
           response.redirect("SWAsset_edit.asp?Software_Asset_ID="& RecordSet("Software_Asset_ID")&"")
     
     Case "SsupportLogs":
           response.redirect("SSL_Edit.asp?Software_Asset_ID="& RecordSet("TicketID")&"")
End Select
Set RecordSet = Nothing

Set cmdDC = Nothing
DataConnection.Close
Set DataConnection = Nothing 
%>

[jeffstl]

No problem.

Good Luck!