Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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.

Reply With Quote
  #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,

Reply With Quote
  #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.

Reply With Quote
  #24 (permalink)  
Old December 12th, 2005, 07:36 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #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

Reply With Quote
  #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.


Reply With Quote
  #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;

Reply With Quote
  #28 (permalink)  
Old December 13th, 2005, 08:59 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #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.








Reply With Quote
  #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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:40 AM.


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