I am not able to retrive data from database using datareader. I am using visual studio 2010 and access 2007. following i my code.
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 Accounts
{
public partial class Form3 : Form
{
public TextBox TextBox1
{
get
{
return textBox1;
}
}
public Form3()
{
InitializeComponent();
}
private void Form3_Load(object sender, EventArgs e)
{
this.Text = "Welome, " + textBox1.Text;
}
private void button2_Click(object sender, EventArgs e)
{
DateTime MyDateTime;
MyDateTime = DateTime.Today;
String MyString;
MyString = MyDateTime.ToString("dd-MM-yyyy");
if(textBox2.Text==""||textBox3.Text==""||textBox4.Text==""||textBox5.Text=="")
{
if (textBox2.Text == "")
{
textBox2.Text ="0";
}
else if (textBox3.Text == "")
{
textBox3.Text = " ";
}
else if (textBox4.Text == "")
{
textBox4.Text = "0";
}
else if (textBox5.Text == "")
{
textBox5.Text = " ";
}
}
OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\\accounts.accdb");
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd = con.CreateCommand();
OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = con;
cmd1 = con.CreateCommand();
OleDbCommand cmd2 = new OleDbCommand();
cmd2.Connection = con;
cmd2 = con.CreateCommand();
OleDbCommand cmd3 = new OleDbCommand();
cmd3.Connection = con;
cmd3 = con.CreateCommand();
OleDbCommand cmd4 = new OleDbCommand();
cmd4.Connection = con;
cmd4 = con.CreateCommand();
OleDbCommand cmd5 = new OleDbCommand();
cmd5.Connection = con;
cmd5 = con.CreateCommand();
OleDbCommand cmd6 = new OleDbCommand();
cmd6.Connection = con;
cmd6 = con.CreateCommand();
OleDbCommand cmd7 = new OleDbCommand();
cmd7.Connection = con;
cmd7 = con.CreateCommand();
OleDbCommand cmd8 = new OleDbCommand();
cmd8.Connection = con;
cmd8 = con.CreateCommand();
cmd.CommandText = "INSERT INTO " + textBox1.Text + " ([Date], [Income], [IncomeDescription], [Expense], [Exp description] ) VALUES('"+MyString+"','" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "','" + textBox5.Text + "')";
con.Open();
cmd.ExecuteNonQuery();
cmd1.CommandText = "select * from "+textBox1.Text+"";
OleDbDataReader dr3 = cmd1.ExecuteReader();
while (dr3.Read())
{
cmd2.CommandText = "select COUNT(*) from "+textBox1.Text+"";
int dd = (int)cmd2.ExecuteScalar();
if (dd==1)
{
cmd3.CommandText = "update " + textBox1.Text + " set Total_income=0, Total_expense=0, Balance=0";
cmd3.ExecuteNonQuery();
cmd6.CommandText = "select MAX(Total_income), MAX(Total_expense), MAX(Balance)from " + textBox1.Text + "";
OleDbDataReader dr1 = cmd6.ExecuteReader();
cmd7.CommandText = "select Income, Expense from " + textBox1.Text + "";
OleDbDataReader dr2 = cmd7.ExecuteReader();
while (dr1.Read())
{
while (dr2.Read())
{
int a = Convert.ToInt32(dr1["Total_income"]);
int b = Convert.ToInt32(dr1["Total_expense"]);
int c = Convert.ToInt32(dr1["Balance"]);
int d = Convert.ToInt32(dr1["Income"]);
int n = Convert.ToInt32(dr1["Expense"]);
int l = d + a;
int k = n + b;
int z = l - k;
cmd8.CommandText = "update " + textBox1.Text + " set Total_income=" + l + ", Total_expense=" + k + ", Balance=" + z + "";//"insert into "+textBox1.Text+" ([Total_income], [Total_expense]) values("l","k")";
cmd8.ExecuteNonQuery();
}
}
}
}
MessageBox.Show("your data has been entered...");
con.Close();
}
private void groupBox1_Enter(object sender, EventArgs e)
{
this.textBox2.Focus();
}
}
}