Web Development

Ask something
Question Locked

how to write a search query

0 helpful
13 replies

by zubin

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

13 replies

by zubin

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.

by tony hopkinson

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 %.

by bajanpoet

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]

by madestroitsolutions

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!

by zubin

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.

by madestroitsolutions

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!

by tony hopkinson

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.

by zubin

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.

by sat_916

Zubin,

In MS SQL Server the syntax is

SELECT * FROM EMP WHERE city LIKE 'ca%'

by cliffy

select * from emp where city like 'ca%' should do the trick

This discussion is closed to new replies.