replace search string with value RRS feed

  • Question

  • hello

    i am using sql ce 4.0 query like this in visual studio 2012 query builder

    SELECT        orders.ID, orders.ORDER_DATE, orders.DATE_SHIPMENT, orders.PRODUCT_ID, orders.QUANTITY, orders.CUSTOMER_ID, orders.INVOICE_FEE, 
                             orders.PROD_TYPE, orders.BRAND, orders.MODEL, orders.PRICE, orders.VAT, (orders.PRICE * orders.QUANTITY) * (orders.VAT / 100) AS vattutar, 
                             orders.PRICE * orders.QUANTITY AS Total, (orders.PRICE * orders.QUANTITY) * (orders.VAT / 100) + orders.PRICE * orders.QUANTITY AS VatTotal, 
                             RTRIM(COALESCE (customers.FIRST_NAME + '', '') + COALESCE (customers.LAST_NAME, '')) AS Description, orders.Page_No, orders.irsaliye, orders.fatura, 
                             CASE WHEN VAT = 8 THEN (PRICE * QUANTITY) * (VAT / 100) ELSE NULL END AS tax1, CASE WHEN VAT = 18 THEN (PRICE * QUANTITY) * (VAT / 100) ELSE NULL 
                             END AS tax2, CASE WHEN VAT = 0 THEN (PRICE * QUANTITY) * (VAT / 100) ELSE NULL END AS tax3, orders.stokno, customers.ID AS Expr1, customers.FIRST_NAME, 
                             customers.LAST_NAME, CASE WHEN stokno LIKE '001' THEN REPLACE(orders.QUANTITY, '1', '0') ELSE orders.quantity END AS quantity_new
    FROM            orders INNER JOIN
                             customers ON orders.CUSTOMER_ID = customers.ID
    ORDER BY orders.ID DESC

    in case option when stokno 001 it replace the quantity from 1 to 0 it is ok but if i want to use something like '%' + @stokno+ '%' it gives parameter error.

    if any suggestion, thank you.

    Sunday, August 2, 2015 5:51 AM

All replies

  • What is the error message exactly?

    Try '%' + CAST(@stokno AS VARCHAR) + '%'

    Sunday, August 2, 2015 6:14 AM
  • thank you but it did not work, when i execute the query it ask the parameters to enter and the 001 i put after that again visual studio message it says incorrect parameter.
    Sunday, August 2, 2015 7:17 AM
  • You are defining your parameter wrong, pls show us your code, where you configure your parameter

    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    Sunday, August 2, 2015 7:19 AM
  • hi,

    i am using visual studio 2012  inside the MsDataset.xsd  there is customers and orders tableadapters and i am trying the orderstableadapter-> fill.getdata-> query builder, the sql query above to execute but i get incorrect parameters error message after i put 001 parameter.why i needed for, stokno 001 is workmanship fee so i dont want to sum quantity where the stokno like workmanship fee 001.

    i can use code when the form load like 

    cmd.Parameters.Add("@stokno", SqlDbType.Int).Value = num;

    to manipulate my report viewer

    or some parameter for report viewer to show when stokno 001 replace the quantity from 1 to 0

    • Edited by ardagurman Sunday, August 2, 2015 8:18 AM
    Sunday, August 2, 2015 8:04 AM
  • If the parameter is a string, you must define it as SqlDbType.NVarchar, not .Int!

    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    Sunday, August 2, 2015 8:48 AM
  • yes it is just a sample i have copied, my stokno is nvarchar, main approach which way do i use i can not find parameter for query builder parameters for sql ce , i want to create report for sale but at quantity sum i dont want to add workmanship fee which is 1 .
    Sunday, August 2, 2015 10:03 AM