Wrox Programmer Forums
|
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
 
Old December 9th, 2005, 10:49 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

This code:

WHERE TimberSpecies LIKE '%MMColParam%'

searches for text that contains the text MMColParam, not the variable's value.

Probably easiest to see how this works is to switch to the Advanced tab and create your query there, using the Variables list to add your variables. Then look at the resulting code and tweak it if necessary.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old December 9th, 2005, 11:31 AM
Authorized User
 
Join Date: Aug 2005
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Imar, I will have to play with it tomorro. Cant keep my eyes open. Early morning here. Thanks for your help.

Mally.
 
Old December 9th, 2005, 06:15 PM
Authorized User
 
Join Date: Aug 2005
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hm, little lost here.

Ok can you explain what we mean by this line.

"WHERE TimberSpecies LIKE '%MMColParam%'searches for text that contains the text MMColParam, not the variable's value."

What is the difference between having the MMColParam & Variable?

So that means that my code should read.

SELECT *
FROM Query1
WHERE TimberSpecies LIKE '%varsearch%'

varsearch % Request("keywordSearch")


 
Old December 10th, 2005, 06:26 AM
Authorized User
 
Join Date: Aug 2005
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Actualy I up to this SQL code now. No errors, but no search results at all.

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

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

Mally.


 
Old December 11th, 2005, 10:47 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

The fact you get no results is due to what I tried to describe earlier.

This:

WHERE 'TimberSpecies' LIKE '%MMColParam%'

searches for records that contain the actual text MMColParam, not it's underlying value. It's highly unlikely you have the text MMColParam somewhere in your database.

To get the value of the variable, you'll need to concatenate it to the WHERE clause:

WHERE 'TimberSpecies' LIKE '%" & MMColParam & "%'

This adds the value of MMColParam to the where clause. So, if MMColParam contains Mally, your WHERE clause ends up like this:

WHERE 'TimberSpecies' LIKE '%Mally%'

which searches for records that have the text Mally in them.

Does this help??

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Rob D - Clubbed To Death (kurayamino mix) by The Matrix (Track 4 from the album: The Matrix - OMPST) What's This?
 
Old December 12th, 2005, 04:45 AM
Authorized User
 
Join Date: Aug 2005
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar, thank it dose. I understand now. Will post my code as soon as I have completed it.

Thanks for the explanation.

Mally.
 
Old December 12th, 2005, 04:47 AM
Authorized User
 
Join Date: Aug 2005
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also a small note. Change of subject. When I post a topic on the Wrox website, I get the following message.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'FORUM_SUBSCRIPTIONS' with unique index 'IX_FORUM_SUBSCRIPTIONS_DUPCHECK'.

/post_info.asp, line 2058

This appears everytime straight after a post.

mally.


 
Old December 12th, 2005, 06:21 AM
Authorized User
 
Join Date: Aug 2005
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Arrrrk.

Hey there, sorry ab out this. I have changed my SQL in the recordset to read the following;

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

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

I am getting a error message which reads;

Microsoft JET Database Engine error '80040e14'
Syntax error in string in query expression ''TimberSpecies' LIKE '% + Replace(rsSearchResult__MMColParam,'.
/html/results.asp, line 55

LINE 55 READS
<%
Dim rsSearchResult
Dim rsSearchResult_numRows
Set rsSearchResult = Server.CreateObject("ADODB.Recordset")
rsSearchResult.ActiveConnection = MM_connTimberseek_STRING
rsSearchResult.Source = "SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE 'TimberSpecies' LIKE '%" & " + Replace(rsSearchResult__MMColParam, "'", "''") + " & "%' AND 'CategoryTable' LIKE '%"& " + Replace(rsSearchResult__MMColParam2, "'", "''") + " & "%' AND 'Location' LIKE '%"& " + Replace(rsSearchResult__MMColParam3, "'", "''") + " & "%'"
rsSearchResult.CursorType = 0
rsSearchResult.CursorLocation = 2
rsSearchResult.LockType = 1
rsSearchResult.Open() <---LINE 55
rsSearchResult_numRows = 0
%>

Also can you suggest a good book that can teach all this SQL. Seems like I am going to have to learn how to use it.

Mally.

 
Old December 12th, 2005, 06:28 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Can you post the outcome of the following statements:

Response.Write(rsSearchResult.Source)
Response.End()

If you add those lines AFTER the Source has been set in the code you just posted, you can see the actual SQL that's passed to the database. That makes it a lot easier to see where a possible problem is.

Re: SQL books: if you want to know the nitty gritty of T-SQL: check out "Professional SQL Server 2000 Programming" (http://www.wrox.com/WileyCDA/WroxTit...764543792.html)

It mainly targets SQL Server, but you'll find that it'll help you understand general concepts that apply to other databases as well.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old December 12th, 2005, 06:37 AM
Authorized User
 
Join Date: Aug 2005
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What do we mean by this?

"Can you post the outcome of the following statements:
Response.Write(rsSearchResult.Source)
Response.End()"

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.