Incorrect syntax near the keyword 'Select'. how to resolve in this code

Question

This code is throwing an error on the line marked below, can anyone point me in the right direction to why this is throwing an error?

 SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
    SqlDataAdapter dadapter;
    DataSet dset; 
    string sql = "SELECT * from  SocoetyMaintan";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            dadapter = new SqlDataAdapter(sql, con);
            dset = new DataSet();
            dadapter.Fill(dset);
            DropDownList1.DataSource = dset.Tables[0];
            DropDownList1.DataTextField = "FullName";
            DropDownList1.DataValueField = "Id";
            DropDownList1.DataBind();
            GridViewBind();
        }
    }
      protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridViewBind();
        }
        public void GridViewBind()
        {
            dadapter = new SqlDataAdapter("SELECT * from SocoetyMaintan where Id=" + DropDownList1.SelectedValue + "", con);
            dset = new DataSet(); //Throwing Error Here
            dadapter.Fill(dset);
            GridView1.DataSource = dset.Tables[0];
            GridView1.DataBind();

Use prepared statements. What is the type of the `Id` column in your table, and what C# type are you trying to bind to it? — Tim Biegeleisen, Oct 03 '18 at 05:49
Error simply means that Select statement format is incorrect, it cannot be parsed, why can't you just figure out the Select statement generated and whether it would run as plain sql or not, that's the whole issue — Mrinal Kamboj, Oct 03 '18 at 05:52
try this => `dadapter = new SqlDataAdapter("SELECT * from SocoetyMaintan where Id='" + DropDownList1.SelectedValue + "'", con);` — er-sho, Oct 03 '18 at 05:54
yes i tried this as well it says "The DataSourceID of 'GridView1' must be the ID of a control of type IDataSource. A control with ID 'GridDataSource' could not be found" — , Oct 03 '18 at 06:15
try to set your `asp:GridView` => `DataSourceID="GridDataSource"` and `asp:SqlDataSource` => `ID="GridDataSource"` and keep my previous commented code as it is and let me know. — er-sho, Oct 03 '18 at 06:20
so can i add this as answer to yuor question? then u just mark tick beside the answer — er-sho, Oct 03 '18 at 06:36
yes you can! surely i will tick, as you know i am new here...just let me know where exactly — , Oct 03 '18 at 06:38
@Poojakulkarni, I added my answer, you just seen tickmark at left side of answer :) you just need to tick to make it green — er-sho, Oct 03 '18 at 06:44

ksdev · Answer 1 · 2018-10-03T06:23:55.603

0

Here, you forgot to convert DropDownList1.SelectedValue to string or int whatever in your database. So do it as follow. For example,

dadapter = new SqlDataAdapter("select * from SocoetyMaintan where Id='" + DropDownList1.SelectedValue.ToString() + "';", con);

edited Oct 03 '18 at 06:23

answered Oct 03 '18 at 05:52

ksdev

56
7

3

DropDownList1.SelectedValue is of type string FYI – Access Denied Oct 03 '18 at 05:56
I don't understand what you say "FYI". Plz say it again. – ksdev Oct 03 '18 at 05:57
Okay. Got it. ';' is missing in query. – ksdev Oct 03 '18 at 06:00
1

@ksdev nope `;` is not necessary for a single query and won't have any effect in here. – Ashkan Mobayen Khiabani Oct 03 '18 at 06:03
@AshkanMobayenKhiabani yeah. I accept that but its good practice to use it. It will effect while wrting multiple queries. – ksdev Oct 03 '18 at 06:06
after pasting above soln query it says " Incorrect syntax near 'dfg;'." at same line @ksdev nope – Oct 03 '18 at 06:12
@Poojakulkarni Now try again. I have changed it. If its not work then tell me what is DropDownList1? like ComboBox, etc. – ksdev Oct 03 '18 at 06:14
DropDownList1 it DropDownList like comboBox you can say – Oct 03 '18 at 06:19
@Poojakulkarni Then you can use DropDownList1.SelectedItem.ToString() method it will work. – ksdev Oct 03 '18 at 06:22
@Poojakulkarni You are missing ' at start and at end also in your original query. – ksdev Oct 03 '18 at 06:30
@Poojakulkarni You are welcome.. Its working or not? – ksdev Oct 03 '18 at 06:40

score 0 · Answer 2 · answered Oct 03 '18 at 06:03

0

It think you're missing quotes in Id=.

 $"SELECT * from SocoetyMaintan where Id='{DropDownList1.SelectedValue}';"

BTW. You should never allow direct input from user into your queries. Look up 'SQL injection'.

answered Oct 03 '18 at 06:03

tymtam

31,798
8
86
126

ManishM · Answer 3 · 2018-10-03T06:32:05.797

0

The way you are writing may give error. Try the following

Method 1:

SqlDataAdapter dadapter = new SqlDataAdapter(string.Format("SELECT * FROM SocoetyMaintan WHERE Id = '{0}'",DropDownList1.SelectedValue), con);

Method 2: (Preferred)

using (SqlDataAdapter dadapter = new SqlDataAdapter("SELECT * FROM SocoetyMaintan WHERE Id = @id", con))
{
    int filter = ID;
    dadapter.SelectCommand.Parameters.AddWithValue("@id", DropDownList1.SelectedValue);
}

edited Oct 03 '18 at 06:32

answered Oct 03 '18 at 06:15

ManishM

583
5
7

1

Have you heard about Little Bobby Tables? https://xkcd.com/327/ People should be punished for Method 1. :) – Access Denied Oct 03 '18 at 06:25
What will happen if DropDownList1.SelectedValue equals to something like that "1';Drop table SocoetyMaintan;'1'='1" – Access Denied Oct 03 '18 at 06:32

score 0 · Accepted Answer · answered Oct 03 '18 at 06:40

0

1) Just try to change your query like below

dadapter = new SqlDataAdapter("SELECT * from SocoetyMaintan where Id='" + DropDownList1.SelectedValue + "'", con);

Note: Better to use prepared statements to your query.

dadapter = new SqlDataAdapter("SELECT * from SocoetyMaintan where Id=@Id", con);
dadapter.SelectCommand.Parameters.AddWithValue("@Id", DropDownList1.SelectedValue);

2) Try to set your asp:GridView => DataSourceID="GridDataSource" and asp:SqlDataSource => ID="GridDataSource"

answered Oct 03 '18 at 06:40

er-sho

9,581
2
13
26

@Poojakulkarni, view the answer might be it help you :) – er-sho Oct 03 '18 at 06:42
Hello Sir, need one more help , in the above code I want to add iff statement for GridViewBind() where in the else case i want to say "NO DATA TO DISPLAY" so how to write such if statement condition? @ershoiab – Oct 04 '18 at 04:48
check this https://stackoverflow.com/questions/2976473/how-to-test-if-a-dataset-is-empty – er-sho Oct 04 '18 at 05:51

Incorrect syntax near the keyword 'Select'. how to resolve in this code

4 Answers4