Randomly access data from database

Ajay Kadian

I want access data randomly and i have write the code when i click the button then data is displayed but i want when i again click the button then the next row data is displayed.

could you please tell i am stuck here past 15 days.please
And when click to a particular button which display the option match with database to a 6 column of a every row if its is correct then backcolor of button is green else red.
thank you in advance.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;

namespace WindowsFormsApplication3

{

public partial class Form1 : Form

{

public DataSet ds;

public OleDbDataAdapter adp;

OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\C#\\Database1.accdb");

public Form1()

{

InitializeComponent();

}

private void button5_Click(object sender, EventArgs e)

{

cn.Open();

OleDbDataReader odr;

OleDbCommand cmd = new OleDbCommand("select * from QUESTION where[Type]=@en", cn);

cmd.Parameters.AddWithValue("@en", textBox3.Text);

odr = cmd.ExecuteReader();

odr.Read();

if (odr.HasRows)

{

textBox2.Text = odr[0].ToString();

textBox1.Text = odr[1].ToString();

button1.Text = odr[2].ToString();

button2.Text = odr[3].ToString();

button3.Text = odr[4].ToString();

button4.Text = odr[5].ToString();

}

else

{

MessageBox.Show("HEhehehehehe");

}

cn.Close();

}

29 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
If I've understood it correctly, you'll need code something like this:

// add these fields
private bool isA = true;
private int scoreA = 0;
private int scoreB = 0;

private void button7_Click(object sender, EventArgs e)
{
isA = true; // A's question
}

private void button8_Click(object sender, EventArgs e)
{
isA = false; // B's question
}

private button1_Click(object sender, EventArgs e)
{
if (button1.Text.Trim() == answer.Trim())
{
button1.BackColor = Color.Green;

if(isA)
{
scoreA += 10;
textBox2.Text = scoreA.ToString();
}
else
{
scoreB += 10;
textBox3.Text = scoreB.ToString();
}
}
else
{
button1.BackColor = Color.Red;

if(isA)
{
scoreA -= 10;
textBox2.Text = scoreA.ToString();
}
else
{
scoreB -= 10;
textBox3.Text = scoreB.ToString();
}
}

button9.PerformClick();
}

private button2_Click(object sender, EventArgs e)
{
if (button2.Text.Trim() == answer.Trim())
{
button2.BackColor = Color.Green;

if(isA)
{
scoreA += 10;
textBox2.Text = scoreA.ToString();
}
else
{
scoreB += 10;
textBox3.Text = scoreB.ToString();
}
}
else
{
button2.BackColor = Color.Red;

if(isA)
{
scoreA -= 10;
textBox2.Text = scoreA.ToString();
}
else
{
scoreB -= 10;
textBox3.Text = scoreB.ToString();
}
}

button9.PerformClick();
}

private button3_Click(object sender, EventArgs e)
{
if (button3.Text.Trim() == answer.Trim())
{
button3.BackColor = Color.Green;

if(isA)
{
scoreA += 10;
textBox2.Text = scoreA.ToString();
}
else
{
scoreB += 10;
textBox3.Text = scoreB.ToString();
}
}
else
{
button3.BackColor = Color.Red;

if(isA)
{
scoreA -= 10;
textBox2.Text = scoreA.ToString();
}
else
{
scoreB -= 10;
textBox3.Text = scoreB.ToString();
}
}

button9.PerformClick();
}

private button4_Click(object sender, EventArgs e)
{
if (button4.Text.Trim() == answer.Trim())
{
button4.BackColor = Color.Green;

if(isA)
{
scoreA += 10;
textBox2.Text = scoreA.ToString();
}
else
{
scoreB += 10;
textBox3.Text = scoreB.ToString();
}
}
else
{
button4.BackColor = Color.Red;

if(isA)
{
scoreA -= 10;
textBox2.Text = scoreA.ToString();
}
else
{
scoreB -= 10;
textBox3.Text = scoreB.ToString();
}
}

button9.PerformClick();
}

private void button6_Click(object sender, EventArgs e)
{
rowNumber = -1;
cn.Open();
adp = new OleDbDataAdapter();;
OleDbCommand cmd = new OleDbCommand("select * from QUESTION where [Type] = ?", cn); // think Access uses ? for parameters
cmd.Parameters.AddWithValue("en", textBox3.Text);
adp.SelectCommand = cmd;
ds = new DataSet();
int rows = adp.Fill(ds);
adp.Dispose();
cmd.Dispose();
cn.Close();
if (rows == 0)
{
MessageBox.Show("HEhehehehehe");
}
else
{
rowNumber = 0;
isA = true;
scoreA = 0;
scoreB = 0;
}
}

private void button5_Click(object sender, EventArgs e)
{
if (rowNumber > -1)
{
DataRow dr = ds.Tables[0].Rows[rowNumber];
textBox2.Text = dr[0].ToString();
textBox1.Text = dr[1].ToString();
button1.Text = dr[2].ToString();
button2.Text = dr[3].ToString();
button3.Text = dr[4].ToString();
button4.Text = dr[5].ToString();
answer = dr[6].ToString();
button1.BackColor = button1.DefaultBackColor;
button2.BackColor = button2.DefaultBackColor;
button3.BackColor = button3.DefaultBackColor;
button4.BackColor = button4.DefaultBackColor;
label4.Text = "";
rowNumber++;
if (rowNumber == ds.Tables[0].Rows.Count) // no changes up to here
{
/*
It's assumed that the second form is called Form2
and has two textboxes, txtScoreA and txtScoreB,
whose Modifiers properties have been set to Public
and which hold the aggregate scores for each team
*/
Form2 f2 = Application.OpenForms["Form2"] as Form2;

if (f2 == null) // Form2 not yet open
{
f2 = new Form2();
f2.txtScoreA.Text = scoreA.ToString();
f2.txtScoreB.Text = scoreB.ToString();
f2.Show();
}
else // add to existing scores
{
int totalA = int.Parse(f2.txtScoreA.Text);
int totalB = int.Parse(f2.txtScoreB.Text);
totalA += scoreA;
totalB += scoreB;
f2.txtScoreA.Text = totalA.ToString();
f2.txtScoreB.Text = totalB.ToString();
}

rowNumber = 0;
isA = true;
scoreA = 0;
scoreB = 0;
}
}
}
Vulpes12y ago
As this thread is already very long, I'll reply to the new thread you've created.
Ajay Kadian12y ago
What's the problem here...
Ajay Kadian12y ago
Yeah, you understood correctly thats what i need.
Finally my app in final steps last phase i really appreciate your work again thank you very much.
Thanks very very much :) :) :) ...
Ajay Kadian12y ago
As you see in previous image there are two teams A and B which are playing so in quiz there is points for every each question(+10 for right & -10 for wrong).I want update the score automatically of each teams when they answer.There are 2 buttons (button7 & button8) so i want that when i clicked on button7 then current question assign to team A if there answer is correct then +10 is rewarded else -10 in textbox(there are 2 textboxes textbox2 for teamA & textbox3 for teamB) and the score automatically update on click and in final when the quiz is over then total score of each teams is displayed in next form.When the question finished instead of this messagebox it it moves to next form which display the total score.I hope you understand what i was trying to say i have not too good in english.
Thanks

{
MessageBox.Show("No more questions left - starting again at beginning");
rowNumber = 0;
}
Vulpes12y ago
Sure, ask away :)
Ajay Kadian12y ago
Its works fine..
Thanks very very much...
Can i ask another question related to this ;)
Vulpes12y ago
Well, that tells us straightaway what the problem is - textBox1.Text has a length of 10 but answer has a length of 8, despite the fact that these values come directly from the database!

So it looks like there's some white-space which we need to trim off before making the comparison:

private button1_Click(object sender, EventArgs e)
{
button1.BackColor = (button1.Text.Trim() == answer.Trim()) ? Color.Green : Color.Red;
button9.PerformClick();
}

and similarly for the other three buttons.

If that still doesn't work, then the 'surplus' characters must be nulls and we'll need to use a different overload of Trim to get rid of those.
Ajay Kadian12y ago
This still doesn't work :(.
Vulpes12y ago
Try putting this code in button1_Click:

private button1_Click(object sender, EventArgs e)
{
string msg = String.Format("button text = {0} (length = {1})\r\nanswer = {2} (length = {3}", button1.Text, button1.Text.Length, answer, answer.Length);
MessageBox.Show(msg);
button1.BackColor = (button1.Text == answer) ? Color.Green : Color.Red;
button9.PerformClick();
}

Now pick a question where button1 gives the correct answer and tell me what you see in the messagebox.
Ajay Kadian12y ago
Yeah,I have already done it for all button but its still doesn't work its shows red color.And all the answer are write its shows in database.
Vulpes12y ago
Well, I can't see anything wrong with the code for button2_Click.

You may like to check the code for the other three buttons to see if it matches the following:

private button1_Click(object sender, EventArgs e)
{
button1.BackColor = (button1.Text == answer) ? Color.Green : Color.Red;
button9.PerformClick();
}

private button3_Click(object sender, EventArgs e)
{
button3.BackColor = (button3.Text == answer) ? Color.Green : Color.Red;
button9.PerformClick();
}

private button4_Click(object sender, EventArgs e)
{
button4.BackColor = (button4.Text == answer) ? Color.Green : Color.Red;
button9.PerformClick();
}

I'd have thought it would be safe to assume that the answer in the database will correspond exactly (as regards capitalization and spacing) with one of the choices.

However, you may like to put break-points on the first line in those methods to see if the values are what we expect them to be at that point.
Ajay Kadian12y ago
:)
It didn't work :( when i click a correct answer then the color changes to red and i want if the text of button match with the text of label4 then backcolor of button changes to green else red.I am storing my answer label4 which is evoke on button9.PerformClick(); .
Vulpes12y ago
The penny has finally dropped :)

So, they are multiple choice questions and the 4 possible answers are written on the buttons themselves.

In that case, the code for button2_Click will become:

private button2_Click(object sender, EventArgs e)
{
button2.BackColor = (button2.Text == answer) ? Color.Green : Color.Red;
button9.PerformClick();
}

and the code for button1, button3 and button4's Click eventhandlers will be similar.

Incidentally, you could do this so that all 4 buttons share the same handler but, as there's not much code, I don't think I'd bother here.

The code for button5_Click will also need changing:

private void button5_Click(object sender, EventArgs e)
{
if (rowNumber > -1)
{
DataRow dr = ds.Tables[0].Rows[rowNumber];
textBox2.Text = dr[0].ToString();
textBox1.Text = dr[1].ToString();
button1.Text = dr[2].ToString();
button2.Text = dr[3].ToString();
button3.Text = dr[4].ToString();
button4.Text = dr[5].ToString();
answer = dr[6].ToString();
button1.BackColor = button1.DefaultBackColor;
button2.BackColor = button2.DefaultBackColor;
button3.BackColor = button3.DefaultBackColor;
button4.BackColor = button4.DefaultBackColor;
label4.Text = "";
rowNumber++;
if (rowNumber == ds.Tables[0].Rows.Count)
{
MessageBox.Show("No more questions left - starting again at beginning");
rowNumber = 0;
}
}
}

In button6_Click, I'd also call button5_Click to show the first question:

private void button6_Click(object sender, EventArgs e)
{
rowNumber = -1;
cn.Open();
adp = new OleDbDataAdapter();;
OleDbCommand cmd = new OleDbCommand("select * from QUESTION where [Type] = ?", cn); // think Access uses ? for parameters
cmd.Parameters.AddWithValue("en", textBox3.Text);
adp.SelectCommand = cmd;
ds = new DataSet();
int rows = adp.Fill(ds);
adp.Dispose();
cmd.Dispose();
cn.Close();
if (rows == 0)
{
MessageBox.Show("HEhehehehehe");
}
else
{
rowNumber = 0;
button5.PerformClick();
}
}
Ajay Kadian12y ago
User wouldn't enter his/her answer in any textbox he/she click on any one of the four buttons which is displaying the answer suppose if he/she clicks button3 then on click event of that button if the text of button matches with the answer then it turn into green color else red and this will also apply to another question and when we move to next question button color changes to default...As in image 4 buttons are there which are numbered as(1,2,3,4) but in run time it displays the 4 option of question.I think you understand what i was trying to.
Vulpes12y ago
I used textBox4 for the textbox in which the user was entering his/her answer.

So replace it with whatever textbox is being used for this purpose.
Ajay Kadian12y ago
In my code textbox4 for doesn't exist( if(textBox4.Text.Trim().ToLower() == answer.Trim().ToLower()))
so what else i use here.
It doesn't work because when i clicked any one from the four buttons if whether answer is write or wrong its shows red its color doesn't change.
Vulpes12y ago
Sorry, that should of course have been:

private button2_Click(object sender, EventArgs e) // now button2_Click
{
if(textBox4.Text.Trim().ToLower() == answer.Trim().ToLower())
{
button2.BackColor = Color.Green;
}
else
{
button2.BackColor = Color.Red;
}

button9.PerformClick();
}

BTW, I'm applying Trim() and ToLower() to both strings to make sure that the user won't be regarded as giving a wrong answer just because of a difference in capitalization and/or leading/trailing whitespace.

We also need to change button5_Click:

private void button5_Click(object sender, EventArgs e)
{
if (rowNumber > -1)
{
DataRow dr = ds.Tables[0].Rows[rowNumber];
textBox2.Text = dr[0].ToString();
textBox1.Text = dr[1].ToString();
button1.Text = dr[2].ToString();
button2.Text = dr[3].ToString();
button3.Text = dr[4].ToString();
button4.Text = dr[5].ToString();
answer = dr[6].ToString();
button2.BackColor = button2.DefaultBackColor;
rowNumber++;
if (rowNumber == ds.Tables[0].Rows.Count)
{
MessageBox.Show("No more questions left - starting again at beginning");
rowNumber = 0;
}
}
}
Ajay Kadian12y ago
It shows error in
if(textBox4.Text.Trim().ToLower() == answer.Text.Trim().ToLower()) (==asnwer.text).
IIf i clicked button its backcolor changes when i move to next question the backcolor remain red i want when i move to next question the color changes to default.
Ajay Kadian12y ago
It doesn't work.

i have 4 option which was displayed on 4 different button(button1,2,3,4) & the user press button2 then it checks that answer is whether right or wrong if wrong then backcolor is red else green then we click 9button which display answer in label.
Vulpes12y ago
Sure it is.

Suppose textBox4 contains the answer entered by the user and (s)he presses button10 to find out whether it's right or not:

private button10_Click(object sender, EventArgs e)
{
if(textBox4.Text.Trim().ToLower() == answer.Text.Trim().ToLower())
{
button10.BackColor = Color.Green;
}
else
{
button10.BackColor = Color.Red;
}

button9.PerformClick();
}
Ajay Kadian12y ago
Its Works Fine Thanks Buddy.
I have an idea i am displaying 4 option on 4 different buttons if user click any button then the on click event its checks with answer in database if its is correct then backcolor of button is turn to green else red and then we click on button to show answer which i we already done.Can this be possible.
Vulpes12y ago
Actually, ignore my last post, I've had a better idea :)

Add another field to store the current answer and change the other methods as follows:

private string answer;

private void button5_Click(object sender, EventArgs e)
{
if (rowNumber > -1)
{
DataRow dr = ds.Tables[0].Rows[rowNumber];
textBox2.Text = dr[0].ToString();
textBox1.Text = dr[1].ToString();
button1.Text = dr[2].ToString();
button2.Text = dr[3].ToString();
button3.Text = dr[4].ToString();
button4.Text = dr[5].ToString();
answer = dr[6].ToString();
rowNumber++;
if (rowNumber == ds.Tables[0].Rows.Count)
{
MessageBox.Show("No more questions left - starting again at beginning");
rowNumber = 0;
}
}
}

private void button9_Click(object sender, EventArgs e)
{
if (answer != null) label4.Text = answer; //EDITED
}
Vulpes12y ago
Well, the rowNumber will have been incremented in readiness for the next row to be displayed in button5_Click.

So, the answer for the currently displayed question will be based on rowNumber - 1.

Also, you don't want to increment the rowNumber when displaying the answer. So I'd change that method to:

private void button9_Click(object sender, EventArgs e)
{
DataRow dr = null;
if (rowNumber > 0)
{
dr = ds.Tables[0].Rows[rowNumber - 1];
}
else if (rowNumber == 0)
{
int count = ds.Tables[0].Rows.Count;
dr = ds.Tables[0].Rows[count - 1];
}
else
{
return;
}

label4.Text = dr[6].ToString();
}
Ajay Kadian12y ago
Hey if i want to show an answer on button click on label text.
I write a code but it didn't display answer in synchronize manner it differ from question what should i do.Please reply soon.

Thanks.

private void button9_Click(object sender, EventArgs e)
{

if (rowNumber > -1)
{
DataRow dr = ds.Tables[0].Rows[rowNumber];

label4.Text = dr[6].ToString();
rowNumber++;
if (rowNumber == ds.Tables[0].Rows.Count)
{
MessageBox.Show("No more questions left - starting again at beginning");
rowNumber = 0;
}
}
Ajay Kadian12y ago
Hi Vuples thanks man its really works.
Thanks very very very very much...
Vulpes12y ago
It looks like I've forgotten to initialize the DataSet:

private void button6_Click(object sender, EventArgs e)
{
rowNumber = -1;
cn.Open();
adp = new OleDbDataAdapter();;
OleDbCommand cmd = new OleDbCommand("select * from QUESTION where [Type] = ?", cn); // think Access uses ? for parameters
cmd.Parameters.AddWithValue("en", textBox3.Text);
adp.SelectCommand = cmd;
ds = new DataSet();
int rows = adp.Fill(ds);
adp.Dispose();
cmd.Dispose();
cn.Close();
if (rows == 0)
{
MessageBox.Show("HEhehehehehe");
}
else
{
rowNumber = 0;
}
}
Ajay Kadian12y ago
Vulpes12y ago
Well, you really need two buttons here:

One button (button6 say) to retrieve a set of questions from the database based on the current value of textbox3 and save them to a DataSet.

Another button (button5) to get the next row from the DataSet.

The code to do this would be on the following lines (untested so there may be bugs):

public partial class Form1 : Form
{
public DataSet ds;
public OleDbDataAdapter adp;
OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\C#\\Database1.accdb");

private int rowNumber;

public Form1()
{
InitializeComponent();
}

private void button6_Click(object sender, EventArgs e)
{
rowNumber = -1;
cn.Open();
adp = new OleDbDataAdapter();;
OleDbCommand cmd = new OleDbCommand("select * from QUESTION where [Type] = ?", cn); // think Access uses ? for parameters
cmd.Parameters.AddWithValue("en", textBox3.Text);
adp.SelectCommand = cmd;
int rows = adp.Fill(ds);
adp.Dispose();
cmd.Dispose();
cn.Close();
if (rows == 0)
{
MessageBox.Show("HEhehehehehe");
}
else
{
rowNumber = 0;
}
}

private void button5_Click(object sender, EventArgs e)
{
if (rowNumber > -1)
{
DataRow dr = ds.Tables[0].Rows[rowNumber];
textBox2.Text = dr[0].ToString();
textBox1.Text = dr[1].ToString();
button1.Text = dr[2].ToString();
button2.Text = dr[3].ToString();
button3.Text = dr[4].ToString();
button4.Text = odr[5].ToString();
rowNumber++;
if (rowNumber == ds.Tables[0].Rows.Count)
{
MessageBox.Show("No more questions left - starting again at beginning");
rowNumber = 0;
}
}
}
}

I assume this is some sort of Question and Answer application but, as I can't tell which textbox is showing the user's answer and which column is the actual answer (possibly the 7th), I haven't been able to deal with the green/red point.

You also implied that the questions are to be obtained in random order from the database. If the questions have some sort of auto-increment ID column, then you could add an Order By clause to your SQL query:

select * from QUESTION order by rnd(ID) where [Type] = ?

Randomly access data from database

Thanks.

private void button9_Click(object sender, EventArgs e)

{

if (rowNumber > -1)

{

DataRow dr = ds.Tables[0].Rows[rowNumber];

label4.Text = dr[6].ToString();

rowNumber++;

if (rowNumber == ds.Tables[0].Rows.Count)

{

MessageBox.Show("No more questions left - starting again at beginning");

rowNumber = 0;

}

}