SQL statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL statement

Author

Topic

mastajbl
Starting Member

42 Posts

Posted - 2007-10-17 : 09:01:33

I am having a problem with my query results. My problem is that my "WHERE [blahfield] LIKE %varSearch% " i actually need it to do a search for 2 other fields so i tried using "WHERE [blahField] or [blahField] or [blahField] LIKE %varSearch%" it tests out correctly but will not search correctly. Is there a different way of coding this? And if i do a drop down list for the user to select search criteria how do I incorporate that into the search criteria in the SQL statement?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-17 : 09:05:03

[code]Where (Field1 Like %varSearch% or Field2 Like %varSearch% Or Field3 Like %varSearch%)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mastajbl
Starting Member

42 Posts

Posted - 2007-10-17 : 09:18:35

SELECT [field0], Field1, [Field2], [Field3], [Field4], Field5
FROM TABLE1
WHERE [Field0] LIKE %varSearch% OR [Field1] LIKE %varSearch% OR [Field2] LIKE %varSearch%

I get a syntax error in query expression....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:20:55

Maybe
Where Field1+Field2+Field3 Like '%varSearch%'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:21:59

Put the single quotes back

SELECT [field0], Field1, [Field2], [Field3], [Field4], Field5
FROM TABLE1
WHERE [Field0] LIKE '%varSearch%' OR [Field1] LIKE '%varSearch%' OR [Field2] LIKE '%varSearch%'

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-17 : 09:22:52

If you are using variable in WHERE clause:

SELECT [field0], Field1, [Field2], [Field3], [Field4], Field5
FROM TABLE1
WHERE [Field0] LIKE '%' + varSearch + '%' OR [Field1] LIKE '%' + varSearch + '%' OR [Field2] LIKE '%' + varSearch + '%'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-17 : 09:24:31

SELECT *
FROM TABLE1
WHERE [Field0] LIKE '%'+@varSearch+'%' OR [Field1] LIKE '%'+@varSearch+'%' OR [Field2] LIKE '%'+@varSearch+'%'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mastajbl
Starting Member

42 Posts

Posted - 2007-10-17 : 09:27:37

Same result this is the error message.

[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'Field1 LIKE "%%%" OR [Field2] LIKE 'varSearch%' OR [Field3] LIKE '%varSearch%".

mastajbl
Starting Member

42 Posts

Posted - 2007-10-17 : 09:28:26

I will try those last 3 post that was error to peso response.

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-17 : 09:34:26

Maybe if you posted the actual code, it might help

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mastajbl
Starting Member

42 Posts

Posted - 2007-10-17 : 09:37:14

I received an error saying too few parameters. Expected 1. To your response harsh athalye

And i received Syntax error (missing operator) in query expresion to yours X002548

If it makes a difference this is going into a *.asp webpage is there any other information i can provide u with or u guys need?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:40:48

quote:
SELECT [Field0], [Field1], [Field2], [Field3], [Field4], [Field5]
FROM Table1
WHERE [Field0] LIKE '%' + varSearch + '%' OR [Field1] LIKE '%' + varSearch + '%' OR [Field2] LIKE '%' + varSearch + '%'

Where do value represented by varSearch come from?
Is it a text-box?

E 12°55'05.25"
N 56°04'39.16"

mastajbl
Starting Member

42 Posts

Posted - 2007-10-17 : 09:40:51

[code]<%
var GMNA__varSearch = "%";
if (String((Request.Form("search"))) != "undefined" &&
String((Request.Form("search"))) != "") {
GMNA__varSearch = String((Request.Form("search")));
}
%>
<%
var GMNA_cmd = Server.CreateObject ("ADODB.Command");
GMNA_cmd.ActiveConnection = MM_Standards_STRING;
GMNA_cmd.CommandText = "SELECT [APPLICATION], GMNA, [JBL PART NUMBER], [DESCRIPTION], [PART CODE], REVISION FROM GMNA WHERE [JBL PART NUMBER] LIKE ?";
GMNA_cmd.Prepared = true;
GMNA_cmd.Parameters.Append(GMNA_cmd.CreateParameter("param1", 200, 1, 255, "%" + GMNA__varSearch + "%")); // adVarChar

var GMNA = GMNA_cmd.Execute();
var GMNA_numRows = 0;
%>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
GMNA_numRows += Repeat1__numRows;
%>[/code]

SELECT [APPLICATION], GMNA, [JBL PART NUMBER], [DESCRIPTION], [PART CODE], REVISION
FROM GMNA
WHERE [JBL PART NUMBER] LIKE %varSearch%

Object is so that when search criteria is entered it searches part number, gmna, and part code. My query works fine with my SELECT, FROM, and WHERE how i posted it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:51:15

Once again you are dropping the single quotes. Why?

GMNA_cmd.Parameters.Append(GMNA_cmd.CreateParameter("param1", 200, 1, 255, "'%" + GMNA__varSearch + "%'")); // adVarChar

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 09:53:03

quote:
Originally posted by Peso

Once again you are dropping the single quotes. Why?

GMNA_cmd.Parameters.Append(GMNA_cmd.CreateParameter("param1", 200, 1, 255, "'%" + GMNA__varSearch + "%'")); // adVarChar

E 12�05.25"
N 56�39.16"

There should be no single quotes in that; he had it correct.

I think the big issue is: he appears to be using Access, not SQL Server.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:54:38

Ok?
* instead of % then?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 09:57:58

Make sure that every single column is spelled correctly and exists in the table; Access assumes that all column names that do not exist are parameters.

UPDATE: I noticed that the table is named GMNA, and you are also trying to select a column named GMNA. Is there really a column called "GMNA" in the "GMNA" table? If not, then Access thinks that GMNA is a parameter. If there is a column called GMNA in the GMNA table, then that's a pretty confusing design!

- Jeff
http://weblogs.sqlteam.com/JeffS

mastajbl
Starting Member

42 Posts

Posted - 2007-10-17 : 10:02:32

i feel like i am wasting your guys time with this. It should be simple and its been giving me a headache. Is there another forum more suitable for my needs as now i am starting to question maybe it has nothing to do with SQL at all

mastajbl
Starting Member

42 Posts

Posted - 2007-10-17 : 10:03:37

yea its a pretty confusing design. i am changing column GMNA to GMNA STANDARDS

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-17 : 10:05:14

Well if you think you're gonna find a better board, good luck

OK, let's get stupid

There's a lot of unkowns that your are not giving us

1. Are you using SQL Server and if so what version

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 10:11:15

As Brett says, if you actually provide specific information like your environment, error messages and code, it is a lot easier to help you. Did you double check all of the column names as I asked? Are you using Access? Can you post the schema of the GMNA table for us so we can take a look and help you out?

- Jeff
http://weblogs.sqlteam.com/JeffS

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources