PDA

View Full Version : SQL HELP


malhyp
12-09-2005, 07:47 AM
Hey to all.

I have played around with SQL and so far have not been successfull.
Can anyone suggest or post what my SQL statment should read.
I have included the following information.

Database Name: datab.mdb
Table Name: tblQuery1
Column Names: name
location
description
Recordset Name: redord1

Results Page Name: result.asp

Search Page Name: search.asp
Search Page Filed Names:
Textfield: keywordSearch
List/Menu: category
List/Menu: location

If I have left anything out please ask me.

Thanks for all your help.

Mally.

davidj
12-09-2005, 09:05 AM
If I have left anything out please ask me.

your SQL Statement !

malhyp
12-09-2005, 09:21 AM
I thaught that my post was pretty clear.

"Can anyone suggest or post what my SQL statment should read".

Mallly.

Creative Insanity
12-09-2005, 09:24 AM
ASP and SQL?
I think that is what dj means as ASP does not use sql

davidj
12-09-2005, 09:25 AM
What you have posted is meaningless

What is the problem exactly? SQL Query not working , database not working , getting wrong results , getting no results etc

you have to help me here.

davidj
12-09-2005, 09:27 AM
SQL looks like this

SELECT * FROM TABLE WHERE FIELD = 'VALUE'

malhyp
12-09-2005, 10:13 AM
Oh.

I have a search file called search.asp.
In that folder I have 3 search fields.
1 is a textfield called 'keywordSearch'
2 is a list/menu called 'category'
3 is a list/menu called 'location'

After you enter your descriptions in your search fields it transfers to the result.asp page with the result.

It is the SQL in the advanced recordset that I dont understand how to create so that this all works.

Excuse my lack of knoledge.

Mally.

davidj
12-09-2005, 10:41 AM
ASP and SQL?
I think that is what dj means as ASP does not use sql

asp uses sql as does all scripting languages when querying a db
well .. not really uses..just passes the sql to the sql engine

davidj
12-09-2005, 10:56 AM
Mally

is this your first attempt to query a database using DW

malhyp
12-09-2005, 11:19 AM
I have tried a few times before but unfortunately this time I will persist until I get it.

Trying to delete the exicsting recordset, but when you click on to the recordset and then click on the the minus symbol above it will not delete. it just sits there???.

Mally.

davidj
12-09-2005, 11:26 AM
the recordset may be corrupt (you will learn to live with this after a while)

i would start again. Go to your code view an delete all your ASP. Every bit so you only have html

then create a recordset.

malhyp
12-09-2005, 11:32 AM
Thanks for that. I will give it a try.

Mally.

malhyp
12-09-2005, 02:25 PM
I think it is working a little better now.

Do you know why this comes up?

Microsoft VBScript compilation error '800a0411'

Name redefined

/html/connTimberseek.asp, line 8

Dim MM_connTimberseek_STRING
----^

Mally.

davidj
12-09-2005, 02:41 PM
please post all the asp code you have written

malhyp
12-09-2005, 02:45 PM
Id say that this might be what you asked for...

<%
Dim rsSearchResult
Dim rsSearchResult_numRows
Set rsSearchResult = Server.CreateObject("ADODB.Recordset")
rsSearchResult.ActiveConnection = MM_connTimber_STRING
rsSearchResult.Source = "SELECT * FROM Query1 WHERE TimberSpecies LIKE '%" + Replace(rsSearchResult__MMColParam, "'", "''") + "%'"
rsSearchResult.CursorType = 0
rsSearchResult.CursorLocation = 2
rsSearchResult.LockType = 1
rsSearchResult.Open()
rsSearchResult_numRows = 0
%>

Mally

davidj
12-09-2005, 02:51 PM
please post everything from that page

from top to bottom

malhyp
12-09-2005, 02:58 PM
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%><!--#include

malhyp
12-09-2005, 03:27 PM
Hey it worked, I had the include file added twice.

Thanks David. Getting there....

Mally.

davidj
12-09-2005, 03:48 PM
what happen to my post???

with the answer

weird

malhyp
12-09-2005, 10:05 PM
huh?

Creative Insanity
12-09-2005, 10:53 PM
Hmm it wasn't me that is for sure.
You didn't hit delete instead of edit dj?

malhyp
12-09-2005, 11:01 PM
Heya, can I say that the way I have written this is correct?

SELECT *
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTable LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%'

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

Also in this book I have it says that instead of having this at the beginning,

SELECT *

I should have something like this.

SELECT [Don't use *; select what you need]'

I dont understand this line, what can I put in the place of *?

Mally.

malhyp
12-10-2005, 04:33 AM
Dose this looks better?

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

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

When I test this SQL i get the following error message.

[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters. Expected 1.

Any ideas?

Mally.

malhyp
12-10-2005, 10:01 AM
If anyone had the same problem the reason why the message came up was because of the missing ' '

WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTable LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%'

WHERE 'TimberSpecies' LIKE '%MMColParam%' AND 'CategoryTable' LIKE '%MMColParam2%' AND 'Location' LIKE '%MMColParam3%'

Thanks to all.

Mally.

malhyp
12-10-2005, 10:34 AM
ARRRRK

Can anyone suggest whey I dont get any results with this search?

SQL reads:

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("category")

Mally.

davidj
12-12-2005, 10:17 AM
you need to echo the sql to the page

i need to see the sql in action

malhyp
12-13-2005, 12:26 PM
I had to scrap the SQL I had earlier. Is this any help?

I have a SQL error that I cant resolve here.

When testing the SQL quiry in MS Access it works fine.
When testing the SQL quiry in Dreamweaver Recordset I get the following error messsage

[Microsoft][ODBC Microsoft Access Driver] Invalid use of '.', '!', or '()' in query expression 'tblCategories.2 =tblResults.2'.

SQL Reads
SELECT tblResults.idSpecies, tblResults.idCategory, tblSuppliers.idLocation
FROM tblLocation INNER JOIN (tblSuppliers INNER JOIN (tblSpecies INNER JOIN (tblCategories INNER JOIN tblResults ON tblCategories.idCategory = tblResults.idCategory) ON tblSpecies.idSpecies = tblResults.idSpecies) ON tblSuppliers.idSupplier = tblResults.idSupplier) ON tblLocation.idLocation = tblSuppliers.idLocation
ORDER BY tblResults.idSpecies, tblResults.idCategory, tblSuppliers.idLocation;

Error message when searching the database once uploaded.
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'tblResults.spotted gum'.
/html/results.asp, line 55

Code reads
52 rsSearchResult.CursorType = 0
53 rsSearchResult.CursorLocation = 2
54 rsSearchResult.LockType = 1
55 rsSearchResult.Open()
56 rsSearchResult_numRows = 0

Any ideas ?

davidj
12-13-2005, 02:13 PM
Syntax error (missing operator) in query expression 'tblResults.spotted gum'

there is a space in spotted gum