error related to execute reader

hi siddu,
first you tell me which operation you actually want to do i.e..

1. insert data (or)
2. retrieve data..

or you want to perform both one after the other...
first you clear me with these..

here you have two options(if you want to do above operations)
1. data reader is to read values, i.e. to view the inserted data..
2. where as update is to check operation..(either insert/delete of data..)
you make clear of these things first..

hope it gets your point..

shameer ali shaik

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

hello shammer ,
thank you for responding.i am learner in asp.net
i want to insert data i mean i want to save data into data table
i will provide full code i tried to develop



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;


public partial class Applying : System.Web.UI.Page
{
String connectionString = ConfigurationManager.ConnectionStrings["SQLDbconnection"].ToString();
SqlConnection con;
DataTable dtEmp, dtLeave, dtLeaveRemaining, dtPublicHolidays;

DataSet ds;


public Applying()
{
ds = new DataSet();
con = new SqlConnection(connectionString);
}



private DataTable GetTable(string tablename)
{
DataTable temp = null;
try
{
if (con != null)
{
con.Open();
string query = string.Format("Select * from {0}", tablename);
SqlDataAdapter da = new SqlDataAdapter(query, con);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds, tablename);
temp = ds.Tables[tablename];
}
}
catch
{ }
finally
{
con.Close();
}
return temp;
}


protected void Page_Load(object sender, EventArgs e)
{

}



public void btnOk_Click(object sender, EventArgs e)
{
ddlLeaveType.SelectedIndex = 0;
tbStartdate_CalendarExtender.StartDate = DateTime.Today;
tbEndDate_CalendarExtender.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day + 1);
dtEmp = this.GetTable("Login");
dtLeave = this.GetTable("LeaveDetails");
dtPublicHolidays = this.GetTable("PublicHolidays");
dtLeaveRemaining = this.GetTable("LeaveRecord");
this.UpdateLeaveRecord();


bool EmpFound = false;
int ID = Convert.ToInt32(tbID.Text);
int leavesRemaining = 0;
SqlCommand cmd = new SqlCommand("SELECT * FROM Login WHERE LogId='"+tbID.Text+"'",con);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read() == true)
{
lblMessage.Text = "the user is valid";
EmpFound = true;
DataRow rowLeaveRemaining = dtLeaveRemaining.Rows[ID];
if (rowLeaveRemaining != null)
{
leavesRemaining = Convert.ToInt32(rowLeaveRemaining["LeavesRemaining"]);

}
}
if (!EmpFound)
{
lblMessage.Text = "invalid Employe";
}
else
{
//int numOfDaysApplied;
if (leavesRemaining >= 1)
{
DataRow row = dtLeave.NewRow();
row["ID"] = Convert.ToInt32(tbID.Text);
row["LeaveType"] = ddlLeaveType.SelectedItem.ToString();
row["StartDate"] = tbStartdate_CalendarExtender.ToString();
row["EndDate"] = tbEndDate_CalendarExtender.ToString();

dtLeave.Rows.Add(row);

DataRow rowLeaveRemaing = dtLeaveRemaining.Rows.Find(Convert.ToInt32(tbID.Text));
rowLeaveRemaing["LeavesRemaining"] = leavesRemaining ;

this.Save("LeaveRecord");
this.Save("LeaveDetails");
}
}

}



private void UpdateLeaveRecord()
{
for (int i = 0; i < dtEmp.Rows.Count; i++)
{
DataRow rowEmployee = dtEmp.Rows[i];
int ID = Convert.ToInt32(rowEmployee["LogId"]);
DataRow rowLeaveRemaining = dtLeaveRemaining.Rows.Find(ID);
if (rowLeaveRemaining == null)
{
DataRow newrow = dtLeaveRemaining.NewRow();
newrow["ID"] = ID;
newrow["LeavesRemaining"] = 20;
dtLeaveRemaining.Rows.Add(newrow);
}
this.Save("LeaveRecord");
}

}


private void Save(string tablename)
{
con.Open();

string sql = string.Format("select * from {0}", tablename);

SqlCommand cmd = this.con.CreateCommand();
cmd.CommandText = string.Format("Select * from {0}", tablename);

SqlTransaction trans = this.con.BeginTransaction();

SqlDataAdapter da = new SqlDataAdapter(sql, con);
new SqlCommandBuilder(da);

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.AcceptChangesDuringUpdate = true;

da.Update(ds.Tables[tablename]);
trans.Commit();
con.Close();
}

}

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator