Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Posts
    31

    Unanswered: create virtual table for online search

    I was just wondering if it was possible to use the statement CREATE VIEW with an access db, using ASP. What I want to do is create a table from all the in-stock items so customers can search it, rather than them searching a table with both in-stock and out of stock items.
    What I want to do is add the following querie to my code but I'm totally new to all this so I'm not sure how to go about it or if it can even be done!
    Code:
    CREATE VIEW [Cards In Stock] AS
    SELECT *
    FROM cards
    WHERE instock=yes
    The following code is the page that I want to add the querie to:
    Code:
    Dim dropdown(3), table
    	Dim strQuery, objRS, objConn, filePath
    	Dim i
    	
    	Set objConn = Server.CreateObject("ADODB.Connection")
    	filePath = Server.MapPath("..\database.mdb")
    	Set objConn = Server.CreateObject("ADODB.Connection")
    	objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath
    	
    	strQuery = "SELECT DISTINCT Description FROM Cards;"
    	Set objRS = objConn.Execute(strQuery)
    	dropdown(2) = "<SELECT SIZE=1 NAME=colour>"
    	While NOT objRS.EOF
    		dropdown(2) = dropdown(2) & "<OPTION>" & objRS("Description") & "</OPTION>"
    		objRS.MoveNext
    	Wend
    	dropdown(2) = dropdown(2) & "</SELECT>"
    
    	strQuery = "SELECT DISTINCT ProductType FROM Cards;"
    	Set objRS = objConn.Execute(strQuery)
    	dropdown(1) = "<SELECT SIZE=1 NAME=producttype>"
    	While NOT objRS.EOF
    		dropdown(1) = dropdown(1) & "<OPTION>" & objRS("ProductType") & "</OPTION>"
    		objRS.MoveNext
    	Wend
    	dropdown(1) = dropdown(1) & "</SELECT>"
    
    	strQuery = "SELECT DISTINCT ProductGroup FROM Cards;"
    	Set objRS = objConn.Execute(strQuery)
    	dropdown(0) = "<SELECT SIZE=1 NAME=productset>"
    	dropdown(0) = dropdown(0) & "<OPTION></OPTION>"
    	While NOT objRS.EOF
    		dropdown(0) = dropdown(0) & "<OPTION>" & objRS("ProductGroup") & "</OPTION>"
    		objRS.MoveNext
    	Wend
    	dropdown(0) = dropdown(0) & "</SELECT>"
    
    	dropdown(3) = "<SELECT SIZE=1 NAME=rarity>"
    	dropdown(3) = dropdown(3) & "<OPTION></OPTION>"
    	dropdown(3) = dropdown(3) & "<OPTION>Common</OPTION>"
    	dropdown(3) = dropdown(3) & "<OPTION>Uncommon</OPTION>"
    	dropdown(3) = dropdown(3) & "<OPTION>Rare</OPTION>"
    	dropdown(3) = dropdown(3) & "</SELECT>"
    
    	strQuery = "SELECT * FROM CardSets ORDER BY Appearance;"
    	Set objRS = objConn.Execute(strQuery)
    	i = 0
    	table = ""
    	While NOT objRS.EOF
    		If i = 0 Then
    			table = table & "<TR>"
    		End If
    		table = table & "<TD><A HREF=../html/advanced_card_search.asp?productset="
    		table = table & objRS("SetName") & "><IMG WIDTH=100 HEIGHT=50 SRC="
    		table = table & objRS("Image") & "></IMG></A></TD>"
    		If i = 4 Then
    			table = table & "</TR>"
    			i = 0
    		Else
    			i = i + 1
    		End If
    		objRS.MoveNext
    	Wend
    
    	objConn.Close
    %>
    The end result that I want would go something like this
    Code:
    strQuery = "SELECT DISTINCT Description FROM Cards In Stock;"
    	Set objRS = objConn.Execute(strQuery)
    	dropdown(2) = "<SELECT SIZE=1 NAME=colour>"
    	While NOT objRS.EOF
    		dropdown(2) = dropdown(2) & "<OPTION>" & objRS("Description") & "</OPTION>"
    		objRS.MoveNext
    Instead of creating the dropdown menus from the cards table it would select from table "Cards In Stock". Any help would be appreciated, or if you know of a better way of doing this please let me know.
    You can view the search at the following address http://www.themazecomicstore.com/htm..._gathering.asp

  2. #2
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    Don't quote me, but I think CREATE VIEW is SQL specific. Which means unless the mdb file has been saved as the desktop SQL Server type (*.mdf) you'd be buggered. Wouldn't it be simpler to make "SELECT * FROM cards WHERE instock=yes" the SQL statement for the ADO recordset object or better still create a permanent query object in Access and quote that in the SQL string?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •