Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server
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 16-Jan-13 7:34am
Edited 16-Jan-13 7:38am
RedDk9.9K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The error in your where clause is because of this
 
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
 
="+DropDownList1.SelectedItem.Value.ToString()+"on
 
to
 
='"+DropDownList1.SelectedItem.Value.ToString()+"' on
 
Code Fixed:
 
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
 
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.
  Permalink  
v2
Comments
a2ulthakur at 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 at 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 at 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 at 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 at 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?
a2ulthakur at 16-Jan-13 13:03pm
   
my table contains value of dropdownlist and its when i am trying to run the query u stated above in sql server its note getting executed m getting error "unable to parse query text, error in join expression"
David_Wimbley at 16-Jan-13 13:06pm
   
Try what i posted.
a2ulthakur at 16-Jan-13 13:08pm
   
david what value should i pass here "SqlDataAdapter da = new SqlDataAdapter();" while using your query and thanks for being so helpful
Member 9581488 at 16-Jan-13 13:13pm
   
string str= @david's query and pass the same value to DataAdapter.
David_Wimbley at 16-Jan-13 13:14pm
   
Yep this should do it i believe.
a2ulthakur at 16-Jan-13 13:18pm
   
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 AS m inner join project p on p.pcode=m.pcode where m.pcode" = '{0}', DropDownList1.SelectedItem.Value.ToString(); its giving error in this statement can u kindly format it
David_Wimbley at 16-Jan-13 13:19pm
   
Its because you didnt use the string.format. Use EXACTLY what i posted.
 
string str = 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());
a2ulthakur at 16-Jan-13 13:22pm
   
thanks a lot @david_wimbley !!!! you are a life saver !u made my day and thanks to Member 9581488 also :)
Member 9581488 at 16-Jan-13 13:24pm
   
glad that it helped you!
Happy coding..:)
David_Wimbley at 16-Jan-13 13:25pm
   
Happy to help, glad it worked.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Don't use concatenation to create SQL statements. A parameterized query should fix that for you.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 195
1 ProgramFOX 130
2 Maciej Los 105
3 Sergey Alexandrovich Kryukov 105
4 Afzaal Ahmad Zeeshan 82
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100