选择C＃时我猜错了 [英] C# Error on selecting I guess

问题描述

我有一个脚本应该从表中选择所有行，按ID排序，但每个选择只有一行。按下按钮时进行此选择。但查询不起作用，它只选择第一行。为什么？

  private   void  选择（）
 {
 使用（Conexiune.getConnection（））
 {
  string   select  =   SELECT DISTINCT * FROM questions ORDER BY id ASC LIMIT 1; 
 SQLiteCommand cmd =  new  SQLiteCommand（ select ，Conexiune.getConnection（））; 
 cmd.CommandType = CommandType.Text; 
 SQLiteDataReader rdra = cmd.ExecuteReader（CommandBehavior.CloseConnection）; 
 尝试 
 {
  while （rdra.Read（）） 
 {
 textBox1.Text = rdra [ 问题]。ToString（ ）; 
 textBox2.Text = rdra [  answer1]。ToString（）; 
 textBox3.Text = rdra [  answer2]。ToString（）; 
 textBox4.Text = rdra [  answer3]。ToString（）; 
 r1 =（ int ）rdra [ 选项1\" ]; 
 r2 =（ int ）rdra [ 选项2\" ]; 
 r3 =（ int ）rdra [ 选项3\" ]; 
 SimulatorManager.Intrebare = textBox1.Text; 
} 
} 
  catch （InvalidOperationException ex）
 {
 MessageBox.Show（ex.Message） ; 
} 
} 
} 

解决方案

因为你使用LIMIT 1



http://www.1keydata.com/sql/sql-limit。 html [ ^ ]

你想要返回表格问题的所有行但你为什么要添加条款LIMIT 1 ??



查询是正确的！但是当我们看到你想要返回所有行的代码并且你正在获取它们（rdra.Read（））但查询将只返回一行，因为你正在使用'LIMIT 1'。





对不起，我的英语有点...... lol

正如每条评论所阐明的，目标是每个后续按钮单击选择并显示下一条记录。



所以基本上你想要的就是所谓的寻呼但只有1条记录。使用LIMIT子句，您有一个必要的部分用于分页，但是您需要另一部分以便不能每次都选择第一条记录：您需要读取记录的ID，将其存储在select-method之外的变量中，以便它的值被保留（直到你可能用另一个按钮重置它）并用WHERE子句将它包含在你的查询中：

  int  lastID = -1;  //  <  -  new（假设id为int） 
  private   void   select （）
 {
 使用（Conexiune.getConnection（））
 {
  string   select  =  String  .Format（  SELECT DISTINCT * FROM问题WHERE id> {0} ORDER BY id ASC LIMIT 1，lastID）;  //  <  -  new  
 SQLiteCommand cmd =  new  SQLiteCommand（ select ，Conexiune.getConnection（））; 
 cmd.CommandType = CommandType.Text; 
 SQLiteDataReader rdra = cmd.ExecuteReader（CommandBehavior.CloseConnection）; 
 尝试 
 {
  while （rdra.Read（）） 
 {
 textBox1.Text = rdra [ 问题]。ToString（ ）; 
 textBox2.Text = rdra [  answer1]。ToString（）; 
 textBox3.Text = rdra [  answer2]。ToString（）; 
 textBox4.Text = rdra [  answer3]。ToString（）; 
 r1 =（ int ）rdra [ 选项1\" ]; 
 r2 =（ int ）rdra [ 选项2\" ]; 
 r3 =（ int ）rdra [ 选项3\" ]; 
 lastID =（ int ）rdra [  ID];  //  <  -  new（假设id为int） 
 SimulatorManager.Intrebare = textBox1 。文本; 
} 
} 
  catch （InvalidOperationException ex）
 {
 MessageBox.Show（ex.Message） ; 
} 
} 
} 

请注意：方式我构建的查询字符串有点快速和脏，我不建议这样做（相反它应该用SQL参数完成）。请查看另一个问题的答案，其中我展示了示例性的良好实践数据库访问：如何循环sql server表来创建datagridview - sql表字段匹配csv字段 [ ^ ]（针对SQL -Server，对于SQLite，您必须更改SQL-Parameter在查询字符串中的表示方式，但是我不知道自己，如果您不知道，请google它。）

I have a script that should select from table all rows, order by id but only one row per select. This selection is made when a button is pressed. But query is not working, it selects only first row. Why?

private void select()
        {
            using (Conexiune.getConnection())
            {
                string select = "SELECT DISTINCT * FROM questions ORDER BY id ASC LIMIT 1";
                SQLiteCommand cmd = new SQLiteCommand(select, Conexiune.getConnection());
                cmd.CommandType = CommandType.Text;
                SQLiteDataReader rdra = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                try
                {
                        while (rdra.Read())
                        {
                            textBox1.Text = rdra["question"].ToString();
                            textBox2.Text = rdra["answer1"].ToString();
                            textBox3.Text = rdra["answer2"].ToString();
                            textBox4.Text = rdra["answer3"].ToString();
                            r1 = (int)rdra["option1"];
                            r2 = (int)rdra["option2"];
                            r3 = (int)rdra["option3"];
                            SimulatorManager.Intrebare = textBox1.Text;
                        }
                }
                catch (InvalidOperationException ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

解决方案

Because you use LIMIT 1

http://www.1keydata.com/sql/sql-limit.html[^]

Well you want to return all the rows of the table questions but why you're adding the clause LIMIT 1 ??

The query is correct !,but as we see the code you want to return all the rows and you're fetching them (rdra.Read() ) but the query will only return ONE ROW because you're using 'LIMIT 1'.


sorry my english is little bit ... lol

As clarified per comments, the goal is to have each subsequent button-click select and display the next record.

So basically what you want is so called "Paging" but just with 1 record. With your LIMIT-clause you have one neccessary part for paging but you need another part in order to not select the first record each time: You need to read the ID of the record, store it in a variable outside of your select-method so that its value is being retained (until you potentially reset it with another button) and include it into your query with a WHERE-clause:

int lastID = -1; // <- new (assuming id is an int)
private void select()
{
    using (Conexiune.getConnection())
    {
        string select = String.Format("SELECT DISTINCT * FROM questions WHERE id > {0} ORDER BY id ASC LIMIT 1", lastID); // <- new
        SQLiteCommand cmd = new SQLiteCommand(select, Conexiune.getConnection());
        cmd.CommandType = CommandType.Text;
        SQLiteDataReader rdra = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        try
        {
            while (rdra.Read())
            {
                textBox1.Text = rdra["question"].ToString();
                textBox2.Text = rdra["answer1"].ToString();
                textBox3.Text = rdra["answer2"].ToString();
                textBox4.Text = rdra["answer3"].ToString();
                r1 = (int)rdra["option1"];
                r2 = (int)rdra["option2"];
                r3 = (int)rdra["option3"];
                lastID = (int)rdra["id"]; // <- new (assuming id is an int)
                SimulatorManager.Intrebare = textBox1.Text;
            }
        }
        catch (InvalidOperationException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

Please note: The way I built the query-string is a bit quick&dirty and I don't recommend doing it like this (instead it should be done with an SQL-Parameter). Please take a look at an answer to another question where I show an exemplary good-practice database-access: how to loop sql server table to create a datagridview - sql table field matches csv field[^] (It's targeted at SQL-Server, for SQLite you would have to change how an SQL-Parameter is represented in a query-string but ad-hoc I don't know that myself, please google it if you don't know either.)

这篇关于选择C＃时我猜错了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！