Question

Locked

how to write a search query

By zubin ·
I am using asp.net 2 with visual web developer express and mssql express database.

My question is how do i write a query

i am writing query in mysql the same query how do i write microsoft sql server

select * from emp where city like "ca*";

For eg if would like to list cities which matches first two characters as "ca"

thanks

This conversation is currently closed to new comments.

13 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

SQL is SQL

Yes. Your example will work in Mysql.

Collapse -

I dont want in mysql

by zubin In reply to SQL is SQL

i just want to use the same example as i have written in mysql, that example i need to implement using mssql not mysql. In mssql i can use the parameter query like

select name from emp where city=@city;

here i have to pass complete city name i dont want to enter exact name instead in just want to search on first characters which i pass as a parameter.

Collapse -

The correct and standard sytntax is

by Tony Hopkinson In reply to I dont want in mysql

Select [name] from [emp] where [city] like 'Man%'.

% is the wild card for 0 - n characters.

So it's just setting the parameter to teh search string concatenated with a %.

Collapse -

How are you passing the city name?

by bajanpoet In reply to I dont want in mysql

Will you pass it from a form? The user's input? If the user is inputting from something like a textbox, you can put the Textbox.text value straight into the query - MS SQL (using Access) will look like

SELECT name
FROM emp
WHERE city = Forms!
[name of search form]![name of textbox]

Collapse -

Only if the user puts in the wild cards

by Tony Hopkinson In reply to How are you passing the c ...

A nice solution but then you have to teach them % _ and [].

Collapse -

passing parameter through textbox

by zubin In reply to How are you passing the c ...

SELECT ProductID, Description, CatalogId, CategoryId, BrandID, SupplierID, buffer, reorder, balance, type,
SELECT CategoryName FROM Category
WHERE (CategoryID = Products.CategoryId)) AS categoryname,
(SELECT CompanyName FROM Suppliers
WHERE (SupplierID = Products.SupplierID)) AS companyname,
(SELECT BrandName FROM Brand
WHERE (BrandID = Products.BrandID))
AS brandname
FROM Products
WHERE (Description LIKE @description + '%') AND (CategoryId = @categoryid)
AND (BrandID = @brandid)

Currently i am using this query to return me product details by passing three parameters CategoryID comes from combo box and BrandId comes from listbox, but here Description is textbox so when ever user enters few characters in description textbox it should list me the products which matches the first few characters entered in textbox.

I am using Microsoft sql server express edition and web developer express edition, so want to write query in sql server express, the abobe query is ok but the problem which i encounter is that as soon as i run the program it says "Value cannot be null value in description". I dont want to predefine the value in description parameter in query want to get that parameter from textbox.

And i dont want user to enter the wildcard characters also, it should just take first few characters and list me all products which starts from those characters.

Collapse -

I believe this is what you are looking for

by MadestroITSolutions In reply to how to write a search que ...

The query in question would be:

select * from emp where city like 'ca%'

Notice the single quotes and percent sign.

Now, if you are passing the value to a stored procedure, then you have to create a parameter, for example @City. Then in the stored procedure you would write:

CREATE PROCEDURE proc_GetEmployeeByCity
@City varchar(30)
AS

select * from emp where city like @City + '%'


Good luck!

Collapse -

I am using same query in my example but it gives null value error

by zubin In reply to I believe this is what yo ...

SELECT ProductID, Description, CatalogId, CategoryId, BrandID, SupplierID, buffer, reorder, balance, type,
SELECT CategoryName FROM Category
WHERE (CategoryID = Products.CategoryId)) AS categoryname,
(SELECT CompanyName FROM Suppliers
WHERE (SupplierID = Products.SupplierID)) AS companyname,
(SELECT BrandName FROM Brand
WHERE (BrandID = Products.BrandID))
AS brandname
FROM Products
WHERE (Description LIKE @description + '%') AND (CategoryId = @categoryid)
AND (BrandID = @brandid)

Currently i am using this query to return me product details by passing three parameters CategoryID comes from combo box and BrandId comes from listbox, but here Description is textbox so when ever user enters few characters in description textbox it should list me the products which matches the first few characters entered in textbox.

I am using Microsoft sql server express edition and web developer express edition, so want to write query in sql server express, the abobe query is ok but the problem which i encounter is that as soon as i run the program it says "Value cannot be null value in description". I dont want to predefine the value in description parameter in query want to get that parameter from textbox.

And i dont want user to enter the wildcard characters also, it should just take first few characters and list me all products which starts from those characters.

Collapse -

ISNULL function

by MadestroITSolutions In reply to I am using same query in ...

Well, I am not sure I understand what your problem is but you seem to be receiving an error saying the @description parameter is null. I don't see why you would get such an error but if that is the case then it means you are not correctly passing the value to the parameter when you call the stored procedure, or if you are, it is coming in as a NULL. In fact you may have declared a default value of NULL for this parameter.
In any event, you can use the ISNULL function. This function checks a value and it replaces it with something else if it is NULL. In your query:

[rest of query]....
AS brandname
FROM Products
WHERE
(Description LIKE ISNULL(@description,'') + '%') AND (CategoryId = @categoryid)
AND (BrandID = @brandid)

I hope that helps!

Collapse -

I think it's saying @description is null

by Tony Hopkinson In reply to I am using same query in ...

How are you setting the parameter in the query.

you need an equivalent of
MySearchQuery.Parameters['description'].value = MySearchTextBox.Text.
before you call the query.

That will do a S & R on the SQL and resplace @description with 'ABC'.

If I'm right if you do this

WHERE (BrandID = @brandid)
AND (CategoryId = @categoryid)
AND (Description LIKE @description + '%')

it should whinge about brandid.

Waht is really saying is standard sql won't let you do

[Description] = NULL
Have to use IS to test for that.

Back to Web Development Forum
13 total posts (Page 1 of 2)   01 | 02   Next

Software Forums