C# Error on selecting I guess

Question

0.00/5 (No votes)

See more:

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?

C#

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

Posted 30-Mar-15 11:14am

DPaul1994

Add a Solution

Comments

Thomas.D Williams 30-Mar-15 17:29pm

What happens when you remove the LIMIT 1 from the query?

DPaul1994 31-Mar-15 1:17am

If I remove LIMIT 1, nothing changes

Sascha Lefèvre 30-Mar-15 17:29pm

Do you want each subsequent button-click to select and display the next row?

DPaul1994 31-Mar-15 1:16am

Exactly!

Sascha Lefèvre 31-Mar-15 1:39am

Please take a look at my solution (3)

phil.o 30-Mar-15 17:43pm

"a script that should select from table all rows, order by id but only one row per select"
Your formulation is contradictory in itself. Do you want all rows to be returned by the query, or do you want only the first one?

DPaul1994 31-Mar-15 1:16am

I want all rows, one by one

3 solutions

Solution 2

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

Posted 30-Mar-15 12:57pm

cronaldo4ever

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)

BacchusBeale · Accepted Answer · 2015-03-30T11:31:00

Solution 1

Because you use LIMIT 1

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

Posted 30-Mar-15 11:31am

BacchusBeale

Comments

Sascha Lefèvre 30-Mar-15 18:39pm

Take a look at his code - it wouldn't make sense to not use LIMIT 1

Sascha Lefèvre · Accepted Answer · 2015-03-30T19:37:00

Solution 3

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:

C#

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.)

Posted 30-Mar-15 19:37pm

Sascha Lefèvre

Comments

DPaul1994 31-Mar-15 2:08am

I receive "Specified cast is not valid" and I don't understand why. `id` is an integer in table and `rows` is an int initialized with 0

Sascha Lefèvre 31-Mar-15 2:14am

Hm.. try this:
lastID = Convert.ToInt32(rdra["id"]);
(instead of lastID = (int)rdra["id"]; )

I don't know what "rows" is supposed to be? It's not visible in the code you posted.

DPaul1994 31-Mar-15 2:18am

Is just a var that I will have to use it later in program. Still got this problem...interesting

Sascha Lefèvre 31-Mar-15 2:20am

At which line does the error occur?

DPaul1994 31-Mar-15 2:27am

Aaa sorry, I have a previous error. I don't receive any error messages, is just, it selects only first row

Sascha Lefèvre 31-Mar-15 2:29am

Do you know about using the debugger and stepping through the execution of your program line by line?

DPaul1994 31-Mar-15 2:36am

Everything seems to be ok..

DPaul1994 31-Mar-15 2:34am

Of course, I check this now

Sascha Lefèvre 31-Mar-15 2:36am

Okay ;-)

Sascha Lefèvre 31-Mar-15 2:39am

Please observe what happens to lastID at the line
lastID = Convert.ToInt32(rdra["id"]);
on subsequent calls of that method. Its value should get greater each time.

DPaul1994 31-Mar-15 2:44am

Aaaa you're right, sorry. Well, I did that and I saw the problem. So, select() function is called by a button. Each time when button is pressed, select should select from table next row. And if I click again, it doesn't step into while, is jumping over while, but I don't know why..

Sascha Lefèvre 31-Mar-15 2:48am

- Make sure there are actually 2 or more records in the table ;-)
- Make sure the ID of those records is positive and different for each record

(By the way, the DISTINCT in your query is (or at least should be) unneccessary)

DPaul1994 31-Mar-15 3:30am

I have 2 records with id 1 and 2 :)

Sascha Lefèvre 31-Mar-15 3:33am

After the first query, what's the new value of lastID ?

DPaul1994 31-Mar-15 3:42am

1, it means it reads first row. but it doesn't continue with next rows

Sascha Lefèvre 31-Mar-15 3:43am

Please remove the LIMIT 1 for testing purpose and give it a run

DPaul1994 31-Mar-15 3:51am

Hmm something's wrong, it replace the value of rows with each id one by one, but on first selection, it doesn't get out of the while until it selects all rows..and that's wrong. it should select every row one by one but only when button is clicked. Don't know if it matters, but select() is also called at form Load, as initializing first value. Then, each time when button is pressed, it should called again select() function for next row

Sascha Lefèvre 31-Mar-15 3:58am

- please put the LIMIT 1 back in
- change the while(rdra.Read()) to if(rdra.Read()) (you only want to read 1 record anyway)
- remove the call to select() from form.Load (at least for testing)

You might also insert 1-2 more records into the table. And maybe display the lastID in a textbox.

DPaul1994 31-Mar-15 4:49am

Is ok, I know what's the problem. `rows` value is always 1, is not passing to the next row. This is the error. query is the problem I guess or something before that while or in while..

Sascha Lefèvre 31-Mar-15 4:56am

Umm.. as said, I don't know what "rows" is - it's not visible in your code. If you post that part I might be able to help.

DPaul1994 31-Mar-15 4:58am

Well I kinda modify the code now. select() function looks like this:
private void select()
{
using (Conexiune.getConnection())
{
string select = String.Format("SELECT * FROM questions WHERE id > {0} ORDER BY id ASC LIMIT 1",rows);
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"];
currentrow = Convert.ToInt32(rdra["id"]);
SimulatorManager.Intrebare = textBox1.Text;
label11.Text = currentrow.ToString();
}
}
catch (InvalidOperationException ex)
{
MessageBox.Show(ex.Message);
}
}
}

Is ok now?

Sascha Lefèvre 31-Mar-15 5:06am

Now I see.. you renamed "my" lastID to rows. Okay. But the idea is that it's the same variable that goes into the query-string and that is being assigned a new value when reading the id from the record. You assign it to currentrow instead. I'm pretty sure that's the problem here.

DPaul1994 31-Mar-15 5:27am

Hmm I don't really understand, how it can be the same variable? `rows` is total rows that is never modified and `currentrow` is the current id for each row.

Sascha Lefèvre 31-Mar-15 5:33am

Please try it like I proposed in my solution - one and the same variable on both lines (however you want to call it, I would call it lastID). I'm 99% sure it will work then. And I think I have an idea where your misconception is - I will explain then.

DPaul1994 31-Mar-15 5:41am

Interesting, it worked..so I have to assing the value -1?

DPaul1994 31-Mar-15 5:44am

It works with 0..I get it now. Thank you very much for your help and time!

Sascha Lefèvre 31-Mar-15 5:59am

You're welcome! :)