You must Sign In to post a response.
  • Category: .NET

    Incorrect syntax in query?

    I am running following query in program but it gives followinf error
    "Incorrect syntax near 'P'."
    But this excecuted in database i.e managment sudio , why this happens?

    this is the query i'm trying to excecute,

    cmd = new SqlCommand("select top 1 P.global_trade_item_no , P.prod_name ,P.quantity_per_pack,P.manufacturer_id, S.batchno ," +
    "S.expiry_date,S.mrp,S.current_stock,P.selling_price, S.sale_price" +
    "from Product_mst P INNER JOIN stocksummary S ON (P.prod_id = S.prod_id " +
    "AND P.selling_price = S.sale_price AND P.prod_id = S.prod_id AND P.manufacturer_id = S.manufacturer_id)" +
    "WHERE P.global_trade_item_no='" + r + "'", con);

    table = new DataTable();
    adapter = new SqlDataAdapter(cmd);
    adapter.Fill(table);
  • #761334
    Hi,

    This is some what difficult to find the problem using query in command,
    1) may be whatever you declare column in the above list that is not there
    2) spelling mistake in anyplace.. lot of question.!

    if you call the Query through Command.

    Instead of that better to write Stored Procedure and call the procedure in your coding side that is the best way to do practice, In SP you can easily findout the problem.

    Hope you understood...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #761335
    Hello Priyanka,

    As per your i don't understand why are you using + in between your queries. As it doesn't make any sense in query.

    It make sense when you are passing parameter to your query like :
    SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ColumnName = t2.ColumnName WHERE Column_Name = '" + TextBox1.Text + "'

    Remove + and try again or else put breakpoint on line of cmd = new SqlCommand and check what is the actual query that you tried in SQL Server and what you are seeing here.

    Hope this will solve your problem.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #761340
    you have to use space in the string
    ex.

    S.sale_price" + should be S.sale_price " +

    cmd = new SqlCommand("select top 1 P.global_trade_item_no , P.prod_name ,P.quantity_per_pack,P.manufacturer_id, S.batchno ," +
    "S.expiry_date,S.mrp,S.current_stock,P.selling_price, S.sale_price " +
    "from Product_mst P INNER JOIN stocksummary S ON (P.prod_id = S.prod_id " +
    "AND P.selling_price = S.sale_price AND P.prod_id = S.prod_id AND P.manufacturer_id = S.manufacturer_id) " +
    "WHERE P.global_trade_item_no='" + r + "'", con);

    By Nathan
    Direction is important than speed

  • #761343
    Hi
    Priyanaka

    you have post this much query we need to table script also thats helpful to Quickly reply for your issue.

    Now i copy paste this query i have no table structure for suitable for your query . So I making new one and generated this query so take much time so when will you post Sql Query based give table structure and Sample Data also

    Try this Query working for me


    Create Table Product_mst
    (
    prod_id int,
    global_trade_item_no varchar(20),
    prod_name varchar(30),
    quantity_per_pack int,
    manufacturer_id int,
    selling_price decimal(18,2)
    )

    Create Table stocksummary
    (
    batchno int,
    expiry_date date,
    mrp decimal(18,2),
    current_stock int,
    sale_pricefrom int,
    sale_price decimal(18,2),
    prod_id int,
    manufacturer_id int
    )



    select top 1 P.global_trade_item_no , P.prod_name ,P.quantity_per_pack,P.manufacturer_id, S.batchno ,
    S.expiry_date,S.mrp,S.current_stock,P.selling_price, S.sale_price from Product_mst P INNER JOIN stocksummary S ON P.prod_id = S.prod_id
    AND P.selling_price = S.sale_price AND P.prod_id = S.prod_id AND P.manufacturer_id = S.manufacturer_id WHERE P.global_trade_item_no='1'


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #761344

    Hi

    I have attached Image Given Below


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    Delete Attachment

  • #761427
    Hai Priyanka,
    It seems that the formatting is having some problem in your query. Try the below one:

    cmd = new SqlCommand("select top 1 "+ " P.global_trade_item_no , P.prod_name ,P.quantity_per_pack,P.manufacturer_id, S.batchno ," +
    " S.expiry_date,S.mrp,S.current_stock,P.selling_price, S.sale_price " +
    " from Product_mst P INNER JOIN stocksummary S ON (P.prod_id = S.prod_id " +
    " AND P.selling_price = S.sale_price AND P.prod_id = S.prod_id AND P.manufacturer_id = S.manufacturer_id)" +
    " WHERE P.global_trade_item_no='" + r + "'", con);

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


Sign In to post your comments