locked
How to get records from different tables RRS feed

  • Question

  • Here is my Database diagram and my scenario is that When a user enter number of person and amount, amount/person
    let suppose 4/1000=250 and now 250 is , match in Product_Price Field in RstProductDetails, and select only Restaurant where 250 is matched. Next when a user select Restaurant e.g KFC and then KFC Products details is show.
    Here is my [WebMethod]

    [WebMethod]
        public DataSet Restaurant(decimal amount, decimal persons)
        {
            //       DataSet result = Amount / personHash;
             //amount.ToString("amount");
            decimal price = amount / persons ;
            DataSet result = null;
            const string SQL_COMMAND_TEXT = "SELECT Product_Name,Product_Price FROM ABCD WHERE Product_Price<= @price";
            using (SqlConnection connection = Class1.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(SQL_COMMAND_TEXT, connection))
                {
                    command.Parameters.Add("@Rst_Name", SqlDbType.NVarChar);
                    command.Parameters.Add("@Persons", SqlDbType.NVarChar);
                    command.Parameters.Add("@price", SqlDbType.Int);
                    command.Parameters["@Rst_Name"].Value = amount;
                    command.Parameters["@persons"].Value = persons;
                    command.Parameters["@price"].Value = price;
                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                    {
                        result = new DataSet();
                        dataAdapter.Fill(result);
                    }
                }
            }

            return result;
        }




    • Edited by Ali Ashiq Sunday, February 2, 2014 6:38 PM
    Saturday, February 1, 2014 3:40 PM

Answers

All replies

  • Ali

    I do not see a column named "amount"

    SELECT <columns> FROM RSTName JOIN RSTProductDetails   ON RSTName.RST_Name=

     RSTProductDetails.RST_Name WHERE Product_Price=250   


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, February 2, 2014 6:34 AM
  • Sir i have a table Product_Price(nvarchar). When i execute the query it will give me error

    "Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the nvarchar value ' 
    145' to data type int."

    Sunday, February 2, 2014 1:38 PM
  • Can you show your query? Product_Price is a column? Try the below

    SELECT * FROM RSTProductDetails WHERE Product_Price ='250'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, February 2, 2014 2:19 PM
  • >WHERE Product_Name WHERE Product_rice<=250 = @Rst_Name"

    The above does not appear to be valid.

    You have to construct a valid SQL string.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Sunday, February 2, 2014 3:48 PM
  • sir when i execute this query
    SELECT * FROM RstProductDetails WHERE Product_Price <='300'

    it will show all records which is less than or greater than 300. I am shocked about this, and Product_Price (nvarchar), because when we scrapped websites through scrapper code it will store automatically in the database ans the price format on different websites is e.g "Rs 600" or "Rs 1,300"   

    Sunday, February 2, 2014 6:36 PM
  • >>>I am shocked about this

    You need to store the prices in appropriate data type  like DECIMAL(5,2) for example. In your case SQL Server compares  strings which leads to incorrect results.

    If you get different price formats I would suggest to take care on the client side and not storing it as a string .


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Monday, February 10, 2014 7:06 AM
    • Marked as answer by Kalman Toth Monday, February 17, 2014 12:37 PM
    Monday, February 3, 2014 6:22 AM
  • why do you store it ain string format thats the issue. Just store it as numeric/decimal and store only actual value. the currency symbols etc can be formatted based using front end formatting functions. In case you need to show multiple currency values, show currency information in seperate field and at front end use that field to determine what format you want to use to display (ie $,Rs,

    etc)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, February 3, 2014 10:46 AM
  • Sir actually we are only dealing with Rs, and our scrapper code runs and fetch price in both format i.e "2000", "2,000" and store in database automatically in Product_Price field and Dataype of Product_Price is (nvarcahr).  this all done automatically via Scrapper coed which is written in c# language.  Please solve my problem i am totally fed-up  
    Monday, February 3, 2014 8:23 PM
  • Well if you store it as a string (NVARCHAR(n)) then  you need to provide a parameter to filter the prices ALSO  as NVARCHAR otherwise you will get conversion error.

    CREATE PROCEDURE spFind_Price

    @Price NVARCHAR(50)

    AS

    SELECT * FROM RSTProductDetails WHERE Product_Price=@Price

    ---Usage

    EXEC spFind_Price '250'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 4, 2014 6:50 AM

  • <Table diffgr:id="Table8" msdata:rowOrder="7">
    <Product_Name>Orange/Tropical Juice Small</Product_Name>
    <Product_Price>120</Product_Price>
    </Table>
    <Table diffgr:id="Table9" msdata:rowOrder="8">
    <Product_Name>Orange/Tropical Juice Tall</Product_Name>
    <Product_Price>160</Product_Price>
    </Table>
    <Table diffgr:id="Table10" msdata:rowOrder="9">
    <Product_Name/>
    <Product_Price xml:space="preserve"></Product_Price>
    </Table>
    <Table diffgr:id="Table11" msdata:rowOrder="10">
    <Product_Name>Breakfast - Meals</Product_Name>
    <Product_Price>1,300</Product_Price>
    <Table diffgr:id="Table16" msdata:rowOrder="15">
    <Product_Name>3 pcs Hot Cakes + Small Cappuccino</Product_Name>

    <Product_Price>1,370</Product_Price>

    Sir when i debug mt web services it will show random items which is less than or greater than 325

    Tuesday, February 4, 2014 9:20 AM
  • Is that possible to wrap the product price with quotations like "250"?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, February 4, 2014 10:15 AM
  • then fisrt remove currency partm, convert to numeric and then do your less than greater than check.

    ie use like

    CAST(REPLACE(REPLACE(yourpricefield,',',''),'Rs.','') AS int) <= @price


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, February 4, 2014 10:17 AM
  • Sir i didn't get your point where i put this code! will you please guide me
    Tuesday, February 4, 2014 2:11 PM
  • Probably in WHERE condition but this technique may prevent from SQL Server Optimizer to use the index on that column if one exists.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, February 5, 2014 6:20 AM