Dear Expert,
Please find below coding as I have a problem in data update as there is no error found but when i update the table it update the table in right table and then it return some thing wrong while returning the records.
Kindly help me in this matter.
Best Regards,
Hasan
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
namespace ptcl
{
public partial class dash : System.Web.UI.Page
{
private string region;
protected void Page_Load(object sender, EventArgs e)
{
string name = Session["user"].ToString();
region = Session["region"].ToString();
Label1.Text = name;
Label2.Text = region;
connection();
Label3.Text = strqry;
}
private string strqry;
private string connstr = "Data Source=HASANNASIR-PC;Initial Catalog=Ptcl;Integrated Security=True";
private string searchparam;
private void connection()
{
searchparam = txt.Text;
// Now write the datbase query.
// i have removed the sql data source because we are going to write our own code.
string strcon = connstr;
SqlConnection conn = new SqlConnection(strcon);
conn.Open();
// modify your logic here for search
strqry = "select TelephoneNumber,BillingPeriod,NAME,Address,BalanceTotal from " + region + " where TelephoneNumber='" + searchparam + "'"; //"select * from "+region+" WHERE TelephoneNumber = "+searchparam;
SqlCommand cmd = new SqlCommand(strqry, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
gv.DataSource = ds.Tables[0];
gv.DataBind();
//DataTable dtable = getdatatable();
//gv.DataSource = dtable;
//gv.DataBind();
}
private DataTable getdatatable()
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand sCommand = new SqlCommand("select * from " + region + " where TelephoneNumber = '" + searchparam + "'", connection);
//sCommand.Parameters.AddWithValue("@region", region);
//sCommand.Parameters.AddWithValue("@searchparam", searchparam);
SqlDataAdapter sAdapter = new SqlDataAdapter(sCommand);
//SqlCommandBuilder sBuilder = new SqlCommandBuilder(sAdapter);
DataSet sDs = new DataSet();
sAdapter.Fill(sDs, "tbl");
DataTable sTable = sDs.Tables["tbl"];
connection.Close();
return sTable;
}
catch (Exception err)
{
Response.Write(err.Message);
return null;
}
}
protected void gv_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
DataTable dtable = getdatatable();
tel_lab.Text = dtable.Rows[0]["TelephoneNumber"].ToString();
bill_lab.Text = dtable.Rows[0]["BillingPeriod"].ToString();
name_lab.Text = dtable.Rows[0]["Name"].ToString();
Panel2.Visible = true;
}
private static bool jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec;
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
Panel1.Visible = true;
if (DropDownList1.SelectedValue == "jan")
{
DataTable dtable = getdatatable();
jan = true;
TextBox1.Text = dtable.Rows[0]["RecPTCLJan"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTJan"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTJan"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
//jan = false;
}
else if (DropDownList1.SelectedValue == "feb")
{
DataTable dtable = getdatatable();
feb = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCFeb"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTFeb"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTFeb"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
//feb = false;
}
else if (DropDownList1.SelectedValue == "march")
{
DataTable dtable = getdatatable();
mar = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCMar"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTMar"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTMar"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
//mar = false;
}
else if (DropDownList1.SelectedValue == "april")
{
DataTable dtable = getdatatable();
apr = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCApr"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTApr"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTApr"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
else if (DropDownList1.SelectedValue == "may")
{
DataTable dtable = getdatatable();
may = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCMay"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTMay"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTMay"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
else if (DropDownList1.SelectedValue == "june")
{
DataTable dtable = getdatatable();
jun = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCJun"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTJun"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTJun"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
else if (DropDownList1.SelectedValue == "july")
{
DataTable dtable = getdatatable();
jul = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCJul"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTJul"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTJul"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
else if (DropDownList1.SelectedValue == "august")
{
DataTable dtable = getdatatable();
aug = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCAug"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTAug"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTAug"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
else if (DropDownList1.SelectedValue == "sept")
{
DataTable dtable = getdatatable();
sep = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCSep"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTSep"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTSep"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
else if (DropDownList1.SelectedValue == "oct")
{
DataTable dtable = getdatatable();
oct = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCOct"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTOct"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTOct"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
else if (DropDownList1.SelectedValue == "nov")
{
DataTable dtable = getdatatable();
nov = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCNov"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTNov"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTNov"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
else
{
DataTable dtable = getdatatable();
dec = true;
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox1.Text = dtable.Rows[0]["RecPTCDec"].ToString();
TextBox2.Text = dtable.Rows[0]["RecGSTDec"].ToString();
TextBox3.Text = dtable.Rows[0]["RecWHTDec"].ToString();
TextBox4.Text = (Convert.ToInt32(TextBox1.Text) + Convert.ToInt32(TextBox2.Text) + Convert.ToInt32(TextBox3.Text)).ToString();
}
}
protected void update_btn_Click(object sender, EventArgs e)
{
if (jan)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCLJan] = " + TextBox1.Text + " ,[RecGSTJan] = " + TextBox2.Text + " ,[RecWhtJan] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
jan = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (feb)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCFeb] = " + TextBox1.Text + " ,[RecGSTFeb] = " + TextBox2.Text + " ,[RecWhtFeb] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
feb = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (mar)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCMar] = " + TextBox1.Text + " ,[RecGSTMar] = " + TextBox2.Text + " ,[RecWhtMar] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
mar = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (apr)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCApr] = " + TextBox1.Text + " ,[RecGSTApr] = " + TextBox2.Text + " ,[RecWhtApr] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
apr = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (may)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCMay] = " + TextBox1.Text + " ,[RecGSTMay] = " + TextBox2.Text + " ,[RecWhtMay] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
may = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (jun)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCJun] = " + TextBox1.Text + " ,[RecGSTJun] = " + TextBox2.Text + " ,[RecWhtJun] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
jun = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (jul)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCJul] = " + TextBox1.Text + " ,[RecGSTJul] = " + TextBox2.Text + " ,[RecWhtJul] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
jul = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (aug)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCAug] = " + TextBox1.Text + " ,[RecGSTAug] = " + TextBox2.Text + " ,[RecWhtAug] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
aug = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (sep)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCSep] = " + TextBox1.Text + " ,[RecGSTSep] = " + TextBox2.Text + " ,[RecWhtSep] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
sep = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (oct)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCOct] = " + TextBox1.Text + " ,[RecGSTOct] = " + TextBox2.Text + " ,[RecWhtOct] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
oct = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else if (nov)
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCNov] = " + TextBox1.Text + " ,[RecGSTNov] = " + TextBox2.Text + " ,[RecWhtNov] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
nov = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
else
{
try
{
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
SqlCommand cmd = new SqlCommand("update " + region + " set [RecPTCDec] = " + TextBox1.Text + " ,[RecGSTDec] = " + TextBox2.Text + " ,[RecWhtDec] =" + TextBox3.Text + " where TelephoneNumber = '" + searchparam + "'", connection);
cmd.ExecuteNonQuery();
dec = false;
}
catch (SqlException ex)
{
//Display Error message
Response.Write(ex.Message);
}
}
}
}
