string方法null返回 [英] string method null return

问题描述

我正在创建学生出勤信息系统。

这些方法彼此相关，如你所见。

学生表有关于每个学生的一般信息。

有些学生总是缺席，所以没有学生_ID在注册表中。

这导致da.Fill（dt）错误说：

MySqlException未被用户代码处理。

您的SQL语法出错;

查看与您的MySQL服务器版本对应的手册在第1行''附近使用的语法。

如何消除此错误？

  private   string  getUquvchilar_ID（）
 {
  var  fish = ddlName.Text.Replace（ '， ''）; 
 MySqlConnection con =  new  MySqlConnection（constr）; 
 con.Open（）; 
  string  query =  从学生中选择Students_ID其中CONCAT（Familiyasi，''，Ismi，''，Sharifi）=' + fish +   ; 
 MySqlCommand cmd =  new  MySqlCommand（query）; 
 cmd.Connection = con; 
  String  s =（cmd.ExecuteScalar（）??  String  .Empty）.ToString（ ）; 
 con.Close（）; 
  return  s; 
} 
 私有  void  BindGrid（）
 {
 MySqlConnection con =  new  MySqlConnection（constr）; 
 MySqlCommand cmd =  new  MySqlCommand（  SELECT * FROM rasmlar其中的Rasm_ID（ +  String  .Join（ ，，getImage_ID（））+  ）， CON）; 
 MySqlDataAdapter da =  new  MySqlDataAdapter（cmd）; 
 DataTable dt =  new  DataTable（）; 
 da.Fill（dt）; 
 gvImages.DataSource = dt; 
 gvImages.DataBind（）; 
} 
 私人列表< int> getImage_ID（）
 {
 List< int> i =  new  List< int>（）; 
 MySqlConnection con =  new  MySqlConnection（constr）; 
 con.Open（）; 
  string  query =  选择bosh_rasm_ID，Tugal_rasm_ID来自寄存器，其中students_ID =' + getUquvchilar_ID（）+  '; 
 MySqlCommand cmd =  new  MySqlCommand（query）; 
 cmd.Connection = con; 
 MySqlDataReader reader = cmd.ExecuteReader（）; 
  if （reader！=  null && reader.HasRows）
 {
  foreach （DbDataRecord s  in  reader）
 {
 < span class =code-keyword> if （！reader.IsDBNull（reader.GetOrdinal（  bosh_rasm_ID ）））
 i.Add（s.GetInt32（ 0 ））; 
  else  
 i.Add（ 0 ）; 
 
  if （！reader.IsDBNull（reader.GetOrdinal（  Tugal_rasm_ID）））
 i.Add（s.GetInt32（ 1 ））; 
  else  
 i.Add（ 0 ）; 
} 
} 
  return  i; 
} 

解决方案

如果 students_ID 字段是数字数据类型，下面的sql查询不正确，因为表达式周围的单引号（'）：

  string  query =  从寄存器中选择bosh_rasm_ID，Tugal_rasm_ID，其中students_ID =' + getUquvchilar_ID（）+  '; 

Richard Deeming [ ^ ]在评论中提到了问题，不要使用这样的查询，而是使用参数化查询：

  string  query =  从寄存器中选择bosh_rasm_ID，Tugal_rasm_ID，其中students_ID = @ stuId; 

其中 @stuid 是一个命名参数。使用 AddWithValue 方法将参数添加到 MySqlCommad.ParameterCollection

 cmd.Parameters.AddWithValue（  @ stuId， getUquvchilar_ID（））; 

如需了解更多信息，请参阅： 6.9.1在连接器/网络中准备语句 [ ^ ]

To添加到以前的解决方案：错误的另一个潜在来源是以下行中的IN子句：

 MySqlCommand cmd =  new  MySqlCommand（  SELECT * FROM rasmlar其中Rasm_ID in（ +  String  .Join（ ，，getImage_ID（））+  ），con）; 

如果是getImage_ID （）返回一个空列表，这个IN子句将为空，从而导致语法错误。



除此之外，我想知道为什么你首先要单独获取数据。如果我正确地调整代码，你可以组合两个select语句并节省时间和代码。换句话说，SQL语句可能类似于

  SELECT  a。* 
  FROM  rasmlar a 
  WHERE  a.Rasm_ID  IN （ SELECT   COALESCE （bosh_rasm_ID，Tugal_rasm_ID）
  FROM 注册r 
  WHERE  r.students_ID =  @ students_ID ）

I am creating Student attendance info system.
These methods are related to each other as you can see.
"students" table has general info about each student.
Some students are always absent, so no students_ID in "register" table.
And this cause for an error with da.Fill(dt) saying :
MySqlException was unhandled by user code.
"You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1".
How can I eliminate this error?

private string getUquvchilar_ID()
    {
        var fish = ddlName.Text.Replace("'", "''");
        MySqlConnection con = new MySqlConnection(constr);
        con.Open();
        string query = "Select Students_ID from students where CONCAT(Familiyasi , ' ', Ismi , ' ', Sharifi)='" + fish + "'";
        MySqlCommand cmd = new MySqlCommand(query);
        cmd.Connection = con;
        String s = (cmd.ExecuteScalar() ?? String.Empty).ToString();
        con.Close();
        return s;
    }
    private void BindGrid()
    {
        MySqlConnection con = new MySqlConnection(constr);
        MySqlCommand cmd = new MySqlCommand("SELECT * FROM rasmlar where Rasm_ID in (" + String.Join(",", getImage_ID()) + ")", con);
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        gvImages.DataSource = dt;
        gvImages.DataBind();
    }
    private List<int> getImage_ID()
    {
        List<int> i = new List<int>();
        MySqlConnection con = new MySqlConnection(constr);
        con.Open();
        string query = "Select bosh_rasm_ID, Tugal_rasm_ID from register where students_ID='" + getUquvchilar_ID() + "'"; 
        MySqlCommand cmd = new MySqlCommand(query);
        cmd.Connection = con;
        MySqlDataReader reader = cmd.ExecuteReader();
        if (reader != null && reader.HasRows)
        {
            foreach (DbDataRecord s in reader)
            {
                if (!reader.IsDBNull(reader.GetOrdinal("bosh_rasm_ID")))
                    i.Add(s.GetInt32(0));
                else
                    i.Add(0);

                if (!reader.IsDBNull(reader.GetOrdinal("Tugal_rasm_ID")))
                    i.Add(s.GetInt32(1));
                else
                    i.Add(0);
            }
        }
        return i;
    }

解决方案

If students_ID field is numeric data type, below sql query is incorrect, because of single quote around expression ('):

string query = "Select bosh_rasm_ID, Tugal_rasm_ID from register where students_ID='" + getUquvchilar_ID() + "'";

As Richard Deeming[^] had mentioned in a comment to the question, do not use such of queries, use parametrized queries instead:

string query = "Select bosh_rasm_ID, Tugal_rasm_ID from register where students_ID=@stuId";

where @stuid is a named parameter. Use AddWithValue method to add parameter to the MySqlCommad.ParameterCollection

cmd.Parameters.AddWithValue("@stuId", getUquvchilar_ID());

For further information, please see: 6.9.1 Preparing Statements in Connector/Net[^]

To add to previous solution: One more potential source for errors is the IN clause on the following line:

MySqlCommand cmd = new MySqlCommand("SELECT * FROM rasmlar where Rasm_ID in (" + String.Join(",", getImage_ID()) + ")", con);

If the getImage_ID() returns an empty list this IN clause would be empty thus causing a syntactic error.

Beyond that I wonder why you fetch the data separately in the first place. If I'm rading the code correctly you could combine the two select statements and save time and code with that. In other words the SQL statement could be something like

SELECT a.* 
FROM rasmlar a
WHERE a.Rasm_ID IN (SELECT COALESCE(bosh_rasm_ID, Tugal_rasm_ID) 
                    FROM register r
                    WHERE r.students_ID = @students_ID)

这篇关于string方法null返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！