I am doing a HRM webs based application. I am facing a problem while inserting data into sql table from a repeater control. Could anyone plz see my code and give me an idea/help where i am doing mistake.
this is the Repeater control with 2 textboxes and i'm binding those two textboxes with some data from a table
<table style="width:250px"> <tr> <td style="width:35%;vertical-align:top">Employee ID</td> <td> <asp:TextBox ID="txtEmpID" runat="server"></asp:TextBox> </td> <td style="width:35%;vertical-align:top">CTC</td> <td> <asp:TextBox ID="txtCTC" runat="server"></asp:TextBox> </td> </tr> </table>
<asp:Repeater ID="rptrEarns" runat="server" DataSourceID="SqlDataSource1" > <ItemTemplate> <asp:TextBox runat="server" ID="txtper"Text='<%#Eval("Percentage") %>'></asp:TextBox> <asp:TextBox ID="txtValue" runat="server"Text="0"></asp:TextBox> </ItemTemplate> </asp:Repeater>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:db_connection %>"
SelectCommand="SELECT Name, [Percentage] FROM [tblSalaryHeads] WHERE [Type] ='Earnings' and Status='1' EXCEPT SELECT Name, [Percentage] FROM [tblSalaryHeads] WHERE Name ='CTC' "></asp:SqlDataSource>
and here is a button to insert from repeater
protected void btnINSERT_Click(object sender, EventArgs e)
{
try
{
foreach (RepeaterItem item in rptrEarns.Items)
{
TextBox txtValue = (TextBox)item.FindControl("txtValue");
InsertData(txtValue.Text);
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
this is Insert Method called in btnINSERT_Click Event
public void InsertData(string Value)
{
SqlConnection con = new SqlConnection(dbconn);
string sql = "IF EXISTS (SELECT EmpID from tblEmps WHERE EmpID=@EmpID) UPDATE tblEmps SET [CTC]=@CTC,[Basic]=@Basic,[HRA]=@HRA,[DA]=@DA,[Convy]=@Convy,[OA]=@OA WHERE EmpID=@EmpID ELSE Insert into tblEmps([EmpID],[CTC],[Basic],[HRA],[DA],[Convy],[OA]) values(@EmpID,@CTC,@Basic,@HRA,@DA,@Convy,@OA)";
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("EmpID", txtEmpID.Text);
cmd.Parameters.AddWithValue("CTC", txtCTC.Text);
cmd.Parameters.AddWithValue("Basic", Value);
cmd.Parameters.AddWithValue("HRA", Value);
cmd.Parameters.AddWithValue("Convy",Value);
cmd.Parameters.AddWithValue("DA", Value);
cmd.Parameters.AddWithValue("OA", Value);
cmd.ExecuteNonQuery();
con.Close();
lblresult.Text = "Records Saved Successfully..";
lblresult.ForeColor = System.Drawing.Color.Green;
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
I am able to insert the data, but only one value is inserting multiple times in each column
For Example after binding data to repeater, will have 5 rows with different values, in my case 5th row value is inserting in all columns except the textbox values from outside repeater.
Can i get the value of textbox for each iteration of RepeaterItems and then assign it to a distinct variable and pass the variable text to distinct columns to InsertData() method ??
Or How can i use List / Array to load from iterated textboxes and bind them to InsertData()
Plz help me on this..