Questions

how to write a search query

+
0 Votes
Locked

how to write a search query

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
  • +
    0 Votes

    Yes. Your example will work in Mysql.

    +
    0 Votes
    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.

    +
    0 Votes
    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 %.

    +
    0 Votes
    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]

    +
    0 Votes
    Tony Hopkinson

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

    +
    0 Votes
    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.

    +
    0 Votes

    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!

    +
    0 Votes
    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.

    +
    0 Votes

    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!

    +
    0 Votes
    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.

    +
    0 Votes
    sat_916

    Zubin,

    In MS SQL Server the syntax is

    SELECT * FROM EMP WHERE city LIKE 'ca%'

    +
    0 Votes
    cliffy

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

  • +
    0 Votes

    Yes. Your example will work in Mysql.

    +
    0 Votes
    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.

    +
    0 Votes
    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 %.

    +
    0 Votes
    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]

    +
    0 Votes
    Tony Hopkinson

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

    +
    0 Votes
    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.

    +
    0 Votes

    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!

    +
    0 Votes
    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.

    +
    0 Votes

    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!

    +
    0 Votes
    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.

    +
    0 Votes
    sat_916

    Zubin,

    In MS SQL Server the syntax is

    SELECT * FROM EMP WHERE city LIKE 'ca%'

    +
    0 Votes
    cliffy

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