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
  #11 (permalink)  
Old December 9th, 2005, 10:49 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

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.
Reply With Quote
  #12 (permalink)  
Old December 9th, 2005, 11:31 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.
Reply With Quote
  #13 (permalink)  
Old December 9th, 2005, 06:15 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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")


Reply With Quote
  #14 (permalink)  
Old December 10th, 2005, 06:26 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.


Reply With Quote
  #15 (permalink)  
Old December 11th, 2005, 10:47 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

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?
Reply With Quote
  #16 (permalink)  
Old December 12th, 2005, 04:45 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.
Reply With Quote
  #17 (permalink)  
Old December 12th, 2005, 04:47 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.


Reply With Quote
  #18 (permalink)  
Old December 12th, 2005, 06:21 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.

Reply With Quote
  #19 (permalink)  
Old December 12th, 2005, 06:28 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

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.
Reply With Quote
  #20 (permalink)  
Old December 12th, 2005, 06:37 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
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.


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 07:01 AM.


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