SQL problem

Help

[Andrew Thomas]

Hi, can someone help me please,

Ive created the folowing SQL (simple to all you)in MS Access

SELECT CatalogueNo, Manufacturer, PPM
FROM monochromea4
WHERE CatalogueNo LIKE 'varCatalogueNo' AND Manufacturer LIKE 'varManufacturer' AND PPM LIKE 'varPPM'

My data is simliar to

catalogueNo = E11-EPSB3006
Manufacturer = Epson 580
PPM (pages per minute)= 10
etc etc....

The question is how do I create a search where it will find the Printer if only "EPS" is entered for the catalogueNo. Or/And only epson is entered for the manufacturer (without the 580) with printer capable of producing 10 pages per minute

I have about 60 printers all together, eg Lexamrk, Brother, HP etc etc.

thanks for your help

[wardbekker]

SELECT CatalogueNo, Manufacturer, PPM
FROM monochromea4
WHERE CatalogueNo LIKE '%EPS%' OR Manufacturer LIKE '%epson%' AND PPM = 10

[Andrew Thomas]

Thanks wardbekker, but Im letting users search from textfields using variables varCatalogueNo varPPM etc etc..

So there are many search options eg LEX for Lexmark, Bro for Brother Etc, so is there any other way around this apart from entering all the possible codes, as I do not know what the user will search for?

[joshie76]

Hi Andrew, welcome to WmW

Wardbekker's code looks like the solution you're after.

When using the LIKE filter in SQL you need to append a % symbol as a wildcard, so %EPS% means any text/varchar (etc..) field that has EPS in it somewhere which I think is the effect you're looking for.

So as you build your SQL string it should probably look something like:

"SELECT CatalogueNo, Manufacturer, PPM
FROM monochromea4
WHERE CatalogueNo LIKE '%" & varCatalogueNo & "%' AND Manufacturer LIKE '%" & varManufacturer & "%' AND PPM LIKE '%" & varPPM & "%'

Where the var variables are the users text input.

Josh

[Andrew Thomas]

Thanks for the help but im getting an error

This is my code

Recordset1.Source = "SELECT CatalogueNo, Manufacturer, PPM FROM monochromea4 WHERE CatalogueNo LIKE '%" & Recordset1__varCatalogueNo & "%' AND Manufacturer LIKE '%" & Recordset1__varManufacturer & "%' AND PPM LIKE '%" & Recordset1__varPPM & "%'";

and the error is

Microsoft JScript compilation error '800a03ec'

Expected ';'

/results.asp, line 24

Recordset1.Source = SELECT CatalogueNo, Manufacturer, PPM FROM monochromea4 WHERE CatalogueNo LIKE '%" & Recordset1__varCatalogueNo & "%' AND Manufacturer LIKE '%" & Recordset1__varManufacturer & "%' AND PPM LIKE '%" & Recordset1__varPPM & "%';

sorry, but im fairly new to SQL, im also using dreamweaver Ultradev4 if it help?

[wardbekker]

you should use a + in stead of & in javascript :

Recordset1.Source = "SELECT CatalogueNo, Manufacturer, PPM FROM monochromea4 WHERE CatalogueNo LIKE '%" + Recordset1__varCatalogueNo + "%' AND Manufacturer LIKE '%" + Recordset1__varManufacturer + "%' AND PPM LIKE '%" + Recordset1__varPPM & "%'";

[Andrew Thomas]

Thanks but ,

I now get this error:

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

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

/results.asp, line 28

any ideas

Sorry to be a pain, but once i get the basic template Im sure I'll be OK for future queries like this

[wardbekker]

Hmm, the sql code looks OK

response.write the code to the screen and post the result here, like this, so i can check the composed sql query.

response.write sqlquery
response.end

[Andrew Thomas]

My sql is within a recordset, I tried what you said, but it came up with another error, but here is the main code

<%@LANGUAGE="JAVASCRIPT"%>
<!--#include file="Connections/quote.asp" -->

<%
var Recordset1__varCatalogueNo = "%";
if(String(Request("textCatalogueNo")) != "undefined") {
Recordset1__varCatalogueNo = String(Request("textCatalogueNo"));
}
%>
<%
var Recordset1__varManufacturer = "%";
if(String(Request("textManufacturer")) != "undefined") {
Recordset1__varManufacturer = String(Request("textManufacturer"));
}
%>
<%
var Recordset1__varPPM = "%";
if(String(Request("textPPM")) != "undefined") {
Recordset1__varPPM = String(Request("textPPM"));
}
%>
<%
var Recordset1 = Server.CreateObject("ADODB.Recordset");
Recordset1.ActiveConnection = MM_quote_STRING;
Recordset1.Source = "SELECT CatalogueNo, Manufacturer, PPM FROM monochromea4 WHERE CatalogueNo LIKE '%" + Recordset1__varCatalogueNo + "%' AND Manufacturer LIKE '%" + Recordset1__varManufacturer + "%' AND PPM LIKE '%" + Recordset1__varPPM & "%'";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 3;
Recordset1.Open();
var Recordset1_numRows = 0;

%>

[Andrew Thomas]

if it helps this is the URL

[212.106.97.103...]

Thanks

andy

[joshie76]

You could try passing the SQL string to the recordset as a variable so you can output your sql prior to the build.... useful for debugging.

var sSQL = "SELECT CatalogueNo, Manufacturer, PPM FROM monochromea4 WHERE CatalogueNo LIKE '%" + Recordset1__varCatalogueNo + "%' AND Manufacturer LIKE '%" + Recordset1__varManufacturer + "%' AND PPM LIKE '%" + Recordset1__varPPM & "%'";

Response.Write sSQL

Response.End

Then when you've sussed it you can just remove the last two lines and set you recordset1.source = sSQL

Make sense?

[Andrew Thomas]

I thought it made sence, but got another error!!

Im STUCK....

(sorry for sounding an idiot)

[joshie76]

OK, back to basics.

Comment out your current SQL statement and try hardcoding one, nice and simple. Something like SELECT * FROM tablename etc... Then extend it to include a LIKE etc...

Let's see if we can get it working that way.

[txbakers]

Another suggestion, when working with Dreamweaver Ultradev.

Let Dreamweaver write the basic code, then you go in and simplify it.

For instance, DW always defines variables as follows: Recordset1__varPPM

you can manipulate it just to varPPM or even PPM . then, when you are studying your code for syntax you can actually see where the variables are.

Start simple, as described above. Then add variables one at a time until it breaks.

The "recordset1.source" is a variable you can use to print to your screen within the delimiters.

But the basic principle is correct.

[joshie76]

>> sorry for sounding an idiot

Don't worry, we've all been there!

[Andrew Thomas]

Ok so far if tried :-

Recordset1.source = "SELECT * FROM monochromea4"; ..this worked and displayed all records

so I know all the connection are correct, which is a good start..

Next i tried

Recordset1.source = "SELECT * FROM monochromea4 WHERE CatalogueNo LIKE '%EPS%' OR Manufacturer LIKE '%epson%' AND PPM = 10";

this showed all Catalogue Numbers with EPS in it, but ignored the Manufactuer and PPM as it showed some lexmark printers and some pages at 12 ppm. - but no errors..

Next

Recordset1.source = "SELECT * FROM monochromea4 WHERE CatalogueNo LIKE '%" + Recordset1__varCatalogueNo + "%';

but this time an ERROR!!

Microsoft JScript compilation error '800a03f7'

Unterminated string constant

/results.asp, line 31

Recordset1.source = "SELECT * FROM monochromea4 WHERE CatalogueNo LIKE '%" + Recordset1__varCatalogueNo + "%';

any ideas

but all your help has been appreciated, and your right starting from basics is better, and I will change my variables so they are easier to read.

[wardbekker]

always read the error message.

"Unterminated string contant"

Is the string not terminated? Are the double quotes placed ok:

.... + Recordset1__varCatalogueNo + "%';

Guess not ;-)

[Andrew Thomas]

Sorry, i missed a " at the end --- its been a long day!!

Anyway that worked, so I shall continue -- I will let you know of my progess

thanks again to all!!

[Andrew Thomas]

OK thanks to you all it seem to be working fine.

However a few tests have showed that if i enter Lexmark with 20 ppm it works fine.

But if i enter Lexmark with 60 ppm (which there are no 60 ppm in the database)

I get this error

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.

/results.asp, line 231

could you lead me down the right path on this problem

thanks
again

[wardbekker]

OK, last one...and go read a introduction to sql programming in asp ;-)

Anyway, because the recordset is empty (no results) you can't read fields.

If you put this in your code, there should be no error:

IF (!recordset.eof) {
//read results

} else {
//nothing found
}