Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i am making a budget page in which i am trying to get a column from two tables in my database but my query is not executed and no gridview is displayed. if i run a simple query like "select * from monthly" this is executed but when i am trying to run my required query i am getting a lot of errors. i tried my query on sql server management studio its running fine there but in asp.net my gridview is not shown and m getting this error "Incorrect syntax near the keyword 'where'." and my line adp.Fill(dt); is highlighted.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
//using System.Configuration;

public partial class Default2 : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    
    protected void Page_Load(object sender, EventArgs e)
    {
        con.ConnectionString = "server=a2ul-pc;uid=sa;pwd=rascal;database=cgrt";
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        if (Page.IsPostBack == false)
        {
            string query = "select * from project";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            DropDownList1.DataSource = ds;
            DropDownList1.DataValueField = ds.Tables[0].Columns[1].ToString();
            DropDownList1.DataTextField = ds.Tables[0].Columns[1].ToString();
            DropDownList1.DataBind();
            DropDownList1.Items.Insert(0, "--Select--");
            cmd.Dispose();
        }

        con.Close();
        
      

    }
    protected void Button1_Click(object sender, EventArgs e)
    {

       
           string str = "select  
m.pcode,m.fyyear,m.date,m.salary,m.ta,m.contigency,m.nrc,m.institcharges,m.others,p.total from monthly m inner join project p where m.pcode="+DropDownList1.SelectedItem.Value.ToString()+"on p.pcode=m.pcode";
            SqlDataAdapter da = new SqlDataAdapter(str, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
       
        
    }
}
Posted
Updated 16-Jan-13 6:38am
v2

The error in your where clause is because of this

C#
string str = "select m.pcode,m.fyyear,m.date,m.salary,m.ta,m.contigency,m.nrc,m.institcharges,m.others,p.total from monthly m inner join project p where m.pcode="+DropDownList1.SelectedItem.Value.ToString()+"on p.pcode=m.pcode";


You need to change

C#
="+DropDownList1.SelectedItem.Value.ToString()+"on


to

C#
='"+DropDownList1.SelectedItem.Value.ToString()+"' on


Code Fixed:

C#
string str = "select m.pcode,m.fyyear,m.date,m.salary,m.ta,m.contigency,m.nrc,m.institcharges,m.others,p.total from monthly m inner join project p where m.pcode='"+DropDownList1.SelectedItem.Value.ToString()+"' on p.pcode=m.pcode";


Corrected SQL

C#
string.Format(@"select m.pcode, m.fyyear, m.date, m.salary, m.ta, m.contigency, m.nrc, m.institcharges, m.others, p.total FROM monthly AS m inner join project p on p.pcode=m.pcode where m.pcode = '{0}'", DropDownList1.SelectedItem.Value.ToString());


Obviously i dont have access to your database/ability to run this query but from eyeballing it and according to your error message you specified this is likely to be your cause.
 
Share this answer
 
v2
Comments
a2ulthakur 16-Jan-13 12:47pm    
david m still getting an error and its not like my query is incorrect cause the same query was giving result in my friends pc and further more i tried your corrected code its still giving the error. with this line highlighted da.Fill(dt);
David_Wimbley 16-Jan-13 12:51pm    
What is the exception that your getting know....the exception message?

Your error message you specified before said it included the where clause and seeing how what i posted above is the only sql you posted containing a where it seemed the logical place.

Can you give your ex.Message value?
a2ulthakur 16-Jan-13 12:58pm    
this is the error code -2146232060
and i dont know how to give u ex.message value can u tell me where to locate it, by the way m still getting the same error as the above "Incorrect syntax near the keyword 'where'."
David_Wimbley 16-Jan-13 13:03pm    
Ah i think i caught it, a key to having nicely formatted sql is readability which is the reason i missed it. If you write your sql where its easy to read, itll be easy to maintain.

Your where clause was in the wrong place within your join. You had
SELECT * FROM Table AS A
JOIN Table AS B WHERE*
The where clause is in the wrong spot.

Try the following.

string.Format(@"select
m.pcode,
m.fyyear,
m.date,
m.salary,
m.ta,
m.contigency,
m.nrc,
m.institcharges,
m.others,
p.total
FROM
monthly AS m
inner join project p on p.pcode=m.pcode
where m.pcode = '{0}'", DropDownList1.SelectedItem.Value.ToString());
Member 9581488 16-Jan-13 12:52pm    
select m.pcode,m.fyyear,m.date,m.salary,m.ta,m.contigency,m.nrc,m.institcharges,m.others,p.total from monthly m inner join project p where m.pcode=ddlSelectedItemValue on p.pcode=m.pcode
in above query place your dropdownlist selected item value and run it in your sql server.
your table contains value of dropdown or text of the dropdownlist?
Don't use concatenation to create SQL statements. A parameterized query should fix that for you.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900