Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Dreamweaver (all versions) section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #21 (permalink)  
Old December 12th, 2005, 06:41 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What I am trying to say is, I dont understand what you mean by the outcome.

  #22 (permalink)  
Old December 12th, 2005, 07:07 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OIC

This is the outcome...

SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE 'TimberSpecies' LIKE '% + Replace(rsSearchResult__MMColParam,

  #23 (permalink)  
Old December 12th, 2005, 07:25 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One thing that came to mind

Is the recordset under VARIABLES, we have the following;

MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("Location")
MMColParam3 3 Request.Form("CategoryTable")

Im not sure but where it says "Request.Form("keywordSearch")"
In the search page I have created, I have a form called "searchForm" and in this form I have 1 textField called "keywordsearch", 1 dynamic listMenu called "Location" and another dynamic listMenu called "CategoryTable". Dose this sound ok?

Mally.

  #24 (permalink)  
Old December 12th, 2005, 07:36 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Yeah, that does sound OK. Request.Form references controls on your form, like a text box.

The problem is in the way you build up the SQL statement. Look at this:

rsSearchResult.Source = "SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE [u]'TimberSpecies' LIKE '%" & " + Replace(rsSearchResult__MMColParam, "'", "''")</u> + " & "%' AND 'CategoryTable' LIKE '%"& " + Replace(rsSearchResult__MMColParam2, "'", "''") + " & "%' AND 'Location' LIKE '%"& " + Replace(rsSearchResult__MMColParam3, "'", "''") + " & "%'"

Look at the part I underlined. Instead of adding the value returned by Replace, you add the actual word Replace to the SQL statement because you have an addtional " before it. You seem to be missing a basic understanding about variable / string concatenation. Look at this example:

Dim myVariable1
Dim myVariable2
myVariable1 = " World"

' Example one - Wrong
myVariable2 = "Hello" & "myVariable1"

' Example two - Wrong
myVariable2 = "Hello" & " Replace(myVariable1, "'", "''")"

' Example three - right
myVariable2 = "Hello" & myVariable1

' Example four - right
myVariable2 = "Hello" & Replace(myVariable1, "'", "''")

The first two examples result in "Hello myVariable1" and NOT in Hello World.
The last two example use the value of the myVariable1 variable so myVariable2 ends up as Hello World.

With this knowledge, check the way you build up the SQL statement and fix it accordingly. When you do a Response.Write like I suggested earlier, you should only get SQL statements, and no more & characters or MMCol stuff.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #25 (permalink)  
Old December 13th, 2005, 04:26 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Does this look better to you?

SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE 'TimberSpecies' LIKE '% & MMColParam & %' AND 'CategoryTable' LIKE '% & MMColParam2 & %' AND 'Location' LIKE '% & MMColParam3 & %'
ORDER BY SupplierName

  #26 (permalink)  
Old December 13th, 2005, 04:54 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Looking at an earlier post I created this one but still nothing.

SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE
'TimberSpecies' LIKE '%" & MMColParam & "%' AND
'CategoryTable' LIKE '%" & MMColParam2 & "%' AND
'Location' LIKE '%" & MMColParam3 & "%'
ORDER BY SupplierName

Mally.


  #27 (permalink)  
Old December 13th, 2005, 06:49 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also another thaught, someone suggested to copy the SQL that ACCESS created. Dose this help or is it a little to advanced.

On the results page that I have been working on i only want to take the three results SupplierName, Location and ShortDescription.

The rest I will sort out in the Detail.asp Page.

SELECT tblSpecies.TimberSpecies, tblCategories.CategoryTitle, tblSuppliers.SupplierName, tblSuppliers.ContactName, tblSuppliers.StreetAddress, tblSuppliers.Suburb, tblLocation.Location, tblSuppliers.PostCode, tblCountry.Country, tblSuppliers.PhoneNumber, tblSuppliers.Facsimile, tblSuppliers.EmailAddress, tblSuppliers.WebsiteAddress, tblSuppliers.Image, tblSuppliers.ShortDescription, tblSuppliers.Description
FROM tblSpecies INNER JOIN (tblCategories INNER JOIN ((tblLocation INNER JOIN (tblCountry INNER JOIN tblSuppliers ON tblCountry.idCountry = tblSuppliers.idCountry) ON tblLocation.idLocation = tblSuppliers.idLocation) INNER JOIN tblResults ON tblSuppliers.idSupplier = tblResults.idSupplier) ON tblCategories.idCategory = tblResults.idCategory) ON tblSpecies.idSpecies = tblResults.idSpecies;

  #28 (permalink)  
Old December 13th, 2005, 08:59 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Try what I suggested earlier: Use Response.Write(myRecordset.Source) so you can diagnose the SQL.

Then paste that in an Access database and see how it behaves.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #29 (permalink)  
Old December 14th, 2005, 04:38 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hmmm.

Using the following SQL in the recordset returns the following error.
SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTable LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%'
ORDER BY SupplierName
MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("CategoryTable")
MMColParam3 3 Request.Form("Location")
"[Microsoft][ODbC Microsoft Access Driver] Too few paramaters. Expected 1."

When you try to use the search you get the following.
Microsoft JET Database Engine error '80040e14'
Syntax error in FROM clause.
/html/results.asp, line 55

I then used response.write and got this.
SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE TimberSpecies LIKE '%spotted gum%' AND CategoryTable LIKE '%All Categories%' AND Location LIKE '%All Locations%' ORDER BY SupplierName

Copied the SQL into Access. Hit the view button and got the following.
"Enter Paramater Value" I entered "test".

Hit enter and got 3 columns.
SupplierName
Location
ShortDescription

But not results, just blank??.

Help.








  #30 (permalink)  
Old December 14th, 2005, 06:07 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar, thanks for sticking with me.

Ignore the last post.

I think its working.

Will post why as soon as I understand why its working.

Thank YOU.

Mally.





Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
creating ssis packagte for sql server to sql serer Laxmikant_it ASP.NET 3.5 Professionals 0 November 26th, 2008 12:23 AM
Converting from MS SQL 2005 to Sql Epress edition saif44 SQL Language 0 February 16th, 2007 04:17 PM
Failed to copy objects from SQL server to SQL Serv monfu SQL Server 2000 4 December 4th, 2005 05:54 PM
Move SQL DB from one sql to another sql server Israr SQL Server 2000 3 January 24th, 2005 02:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.