system.data.dll中出现'system.data.sqlclient.sqlexception'类型的例外，但未在用户代码中处理其他信息：关键字'by'附近的语法不正确。 [英] 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'.

问题描述

 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（从StateTable中选择*，其中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（）; 
返回ds; 
 
} 
 

我的尝试：



错误在da.Fill（ds）;

并且错误为{关键字'附近的语法不正确'。 } $



i希望从一个DropdownList获取数据并根据MVC中其他下拉列表中的选择生成数据

解决方案

好吧，首先，异常实际上说你的sql有问题 - 例如你有一个列'Id' - 如果删除'order by ...'条款会怎样？



其次，这是编写SQL语句的一种可怕方式 - 你应该使用参数化查询，按照



< pre lang =c＃> 使用（SqlCommand command = new SqlCommand（
从StateTable中选择*，其中CountryName = @c ountryname，con））
{
//
// 在命令中添加新的SqlParameter。
//
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;
}

字符串在查询字符串中应该像这样'+ stringname +'。

 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（）; 
返回ds; 
 
} 

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

解决方案

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

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;
}

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;
            
        }

这篇关于system.data.dll中出现'system.data.sqlclient.sqlexception'类型的例外，但未在用户代码中处理其他信息：关键字'by'附近的语法不正确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！