An exception of type 'system.data.sqlclient.sqlexception' occurred in system.data.dll but was not handled in user code additional information: incorrect syntax near the keyword 'by'.

Question

1.00/5 (1 vote)

See more:

public DataSet BindStates(string country)
       {
           StudentContext db = new StudentContext();
           SqlConnection con = new SqlConnection(@"Data Source=DE-SHREE-02;Initial Catalog=Abhijeet;Integrated Security=True");
           con.Open();
           SqlCommand cmd = new SqlCommand("Select * from StateTable where CountryName=" + country + "order by Id asc",con);
           cmd.CommandType = CommandType.Text;

           SqlDataAdapter da = new SqlDataAdapter(cmd);
           DataSet ds = new DataSet();
          da.Fill(ds);
           con.Close();
           return ds;

       }

What I have tried:

the error is at "da.Fill(ds);"
and giving error as {"Incorrect syntax near the keyword 'by'."}

i want to fetch data from one DropdownList and generate data as per selection in other dropdown list in MVC

Posted 16-Feb-17 19:42pm

Member 13006667

Updated 16-Feb-17 22:24pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Garth J Lancaster · Answer 1 · 2017-02-16T20:19:00

Solution 1

well, first off, the exception actually says there's something wrong with your sql - do you have a column 'Id' for example - what happens if you remove the 'order by...' clause ?

secondly, thats a horrible way to write SQL statements - you should use parameterised queries, along the lines of

C#

using (SqlCommand command = new SqlCommand(
                "Select * from StateTable where CountryName=@countryname", con))
{
    //
    // Add new SqlParameter to the command.
    //
    command.Parameters.Add(new SqlParameter("countryname", country));
    cmd.CommandType = CommandType.Text;
            
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    con.Close();
    return ds;
}

Posted 16-Feb-17 20:19pm

Garth J Lancaster

Updated 16-Feb-17 20:20pm

v2

Comments

Karthik_Mahalingam 17-Feb-17 2:33am

5

Member 13006667 17-Feb-17 2:59am

-yes I do have Column called as 'Id' in StateTable
-while execution it is showing the value of country is accepting correctly. but its not getting the value of "Id"

Garth J Lancaster 17-Feb-17 3:58am

ok - not sure what you mean by 'but its not getting the value of "Id"' but we'll keep going

- your SQL doesnt have a space before your 'order by' clause if I see it correctly - so if you've re-written the better form of query as I suggested, and put back the 'order by', you'd have

"Select * from StateTable where CountryName=@countryname ORDER BY Id ASC"

note the space between @countryname and ORDER - without it, and using country = 'Australia', I think what your (original SQL) would do is this

"Select * from StateTable where CountryName=@countrynameorder by Id asc"

so, if you're using your original sql (bleh), put a space before the 'order' keyword -also have a look at https://msdn.microsoft.com/en-us/library/ms188385.aspx#BasicSyntax for the syntax

Member 13006667 17-Feb-17 4:08am

Thank you sir for your help.
my problem is solved after implementing your solutions.
Thank You !

Garth J Lancaster 17-Feb-17 4:14am

excellent - you can see why its harder if you 'concatenate' your SQL statement, hopefully - it makes it easier to introduce bugs

avinashkumar0509 · Answer 2 · 2017-02-16T22:24:00

string should be like this '"+stringname+"' inside your query string.

public DataSet BindStates(string country)
        {
            StudentContext db = new StudentContext();
            SqlConnection con = new SqlConnection(@"Data Source=DE-SHREE-02;Initial Catalog=Abhijeet;Integrated Security=True");
            con.Open();
            SqlCommand cmd = new SqlCommand("Select * from StateTable where CountryName='" + country + "' order by Id asc",con);
            cmd.CommandType = CommandType.Text;
            
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
           da.Fill(ds);
            con.Close();
            return ds;
            
        }