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.
9 1634 jeffstl 432
Recognized Expert Contributor
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.
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 432
Recognized Expert Contributor
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 432
Recognized Expert Contributor
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.
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.
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 432
Recognized Expert Contributor
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
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 432
Recognized Expert Contributor Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Rob |
last post by:
I am an Access developer and have done many Access databases in the
standard Front-end on the workstations, backend on the server
(over a LAN) but have never worked with Access over Citrix, though
that situation is coming up for me shortly.
Question: When you run the front-end on the Server, does Citrix clone
the front
end application database file for each user that connects or do they
all connect
to physically the same front-end...
|
by: Benny |
last post by:
Hi,
We are planning to improve the present search (Product DB search). Is there
any way on ASP script to make an auto complete text box, loaded with the
product names (like in google suggest). I hear AJAX, can that be used with
ASP scripts?
Benny
|
by: cj |
last post by:
My program checks to see if another program is already running and if so
I want it to "restore" as windows puts it that other program so it
appears on top of anything else on the screen. I know how to check to
see if it's running
If UBound(System.Diagnostics.Process.GetProcessesByName("programX")) > 1
then
then what? How do I get programX "restored"?
|
by: funfair |
last post by:
hi,every one
im facing a problem in running access it's about
25 tables
80 forms
46 query (5 append query ,1 update query ,2 delete query )
60 reports
4 modules
after 7 months of perfect working in access 2003 around 8users working
in it .i was working with these users in my sharing folder which i know
that may corrupt the whole application but from one week i transfer it
|
by: Robert Jacobs |
last post by:
Please Help!
I have read over and over again to go to Allen Browne's website to
filter subforms, but I just don't understand it, and I can't get it to
work in my situation. Any advice is very appreciated.
I have 2 forms. Mainform=Customers Subform(linked)=Service
Requests.
The two forms have a one to many relationship (many service requests
| |
by: mahesh123 |
last post by:
Hi Folks,
I want Help regarding the Auto search in the Combo Box. When I am pressing the characters it will move to the appropraite words in the Combo Box.
For Example if the Combo Box filled with the Apple, Ant, Boy,Buy
If press the A then it should go the Apple,Ant,
If press the An then it should go the Ant.
Like this to be activate the Auto Search in the Combo.
|
by: Sham |
last post by:
I am trying to perform the following query on a table that has been
indexed using Full Text Search.
The table contains multiple columns than have been indexed. (Below,
all xml columns are indexed).
dbo.maintable(ProfileID int pk)
dbo.fts_table(ProfileID int pk fk, col1 xml, col2 xml, col3 xml)
I want to perform a query that will return any rows that contain ‘x’
and ‘y’ in any columns. I.e. ‘x’ could be in col1 and ‘y’ could be in
|
by: MNNovice |
last post by:
I am working on a database on my CD collection using Access 2003. Some of the table structures are given below.
Table 1 tblMusicCategory
Field 1: MusicCategoryID - Auto Number (PK)
Field 2: MusicCategory - text
Field 3: MusicCategoryAbbv - text
Table 2 tblArtists
Field 1 ArtistID - Auto Number (PK)
|
by: Miro |
last post by:
Hi sorry for the quick question. I am on the right track but just cant seem
to find what to search for in my books / online for the answer I am looking
for.
I have created a dataset within my project.
In the dataset I created 2 tables - and set some fields on the tables.
In each table I set one of the fields to be a primary key / unique auto
incremenet.
So now I create a form, add a datagrid view to it and add to the dataset.
|
by: weirdguy |
last post by:
Hello,
Just for anyone information, there is a similar title "Search in Listbox" but it is via Combo Box. In case, anyone need it, I put a link to here.
Please let me know if I break any rules by posting a link so then, I will remove it.
Okay, here is my question. If you ever refer to the link, I am almost doing the same thing. Just that, I am doing a textbox and a combo box to search the listed data in the listbox.
The listbox...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |