I have a repeater which creates multiple rows with textboxes, I need to insert the filled multiple values into a datatable using a query. My repeater ASPX code is:
And in addition, I need to do this as bulk insert for multiple values. Please give me a code for button click where all the repeater textboxes filled values moves into database. My .cs code is:
protected void Repeater2_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "UpdateDatabase")
{
try
{
string SQLIns = "";
string newSalesInvyNum = TxtInventoryNumber.Text;
string newInvyDate = txtInventoryDate.Text;
string newSite = TxtCountSite.Text;
string newItemId = ((TextBox)e.Item.FindControl("ItemId")).Text;
string newItemName = ((TextBox)e.Item.FindControl("ItemName")).Text;
string newQty = ((TextBox)e.Item.FindControl("Qnty")).Text;
Int32 newNumQty = Convert.ToInt32(newQty);
string newCost = ((TextBox)e.Item.FindControl("Value")).Text;
Int32 Orgnl_Qty;
//--- Check for the same item already exists...
string SQL_verify = "";
//SQL_verify = "select ItemId,Qty from IC_Inventory_T where Invy_Num = '" + newSalesInvyNum + "' and ItemId = '" + newItemId + "'";
SqlDataReader ResData;
SqlConnection conn = new SqlConnection(ConString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//SQLIns = "Insert into IC_Inventory_T (Invy_Num,Invy_Date,SiteID,ItemId,ItemName,Qty,Cost) values ('" + newSalesInvyNum + "','" + newInvyDate + "','" + newSite + "','" + newItemId + "','" + newItemName + "','" + newNumQty + "','" + newCost + "')";
SqlCommand cmd = new SqlCommand("IC_INVENTORY_TINSERT", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@newSalesInvyNum", newSalesInvyNum);
cmd.Parameters.AddWithValue("@newInvyDate", newInvyDate);
cmd.Parameters.AddWithValue("@newSite", newSite);
cmd.Parameters.AddWithValue("@newItemId", newItemId);
cmd.Parameters.AddWithValue("@newItemName", newItemName);
cmd.Parameters.AddWithValue("@newNumQty", newNumQty);
cmd.Parameters.AddWithValue("@newCost", newCost);
conn = new SqlConnection(ConString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string Str1 = newQty;
string Str2 = newCost;
double Num1, Num2;
bool isNum1 = double.TryParse(Str1, out Num1);
bool isNum2 = double.TryParse(Str2, out Num2);
if ((isNum1) && (isNum2))
{
int temp = cmd.ExecuteNonQuery();
}
else
{
lblMessage.CssClass = "alert alert-warning";
lblMessage.Text = "Qty and Cost should be numeric";
}
lblMessage.CssClass = "";
lblMessage.Text = "";
conn.Close();
}
//ResData.Close();
//-- End of Check for the same item already exists...
catch (Exception ex)
{
lblMessage.CssClass = "alert alert-warning";
lblMessage.Text = "Qty and Cost should be numeric";
}
}
GetData();
dc.Rows.Add("", "", "", "", "");
Repeater2.DataSource = dc;
Repeater2.DataBind();
}
This actually does not return anything or store the values in database. Please help me with the code
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
protected void Repeater2_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "UpdateDatabase")
{
foreach (RepeaterItem item in Repeater2.Items)
{
TextBox TextBoxid = (TextBox)item.FindControl("ItemId");
TextBox TextBoxItemName = (TextBox)item.FindControl("ItemName");
TextBox TextBoxQnty = (TextBox)item.FindControl("Qnty");
TextBox TextBoxCost = (TextBox)item.FindControl("Cost");
int id = Convert.ToInt32(TextBoxid.Text.ToString());
string ItemName = TextBoxItemName.Text.ToString();
string Qnty = TextBoxQnty.Text.ToString();
string Cost = TextBoxCost.Text.ToString();
InsertRow(id, ItemName, Qnty, Cost);
}
}
}
public void InsertRow(int id, string ItemName, string Qnty, string Cost)
{
string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "INSERT INTO NEWTable(ItemId,ItemName,Qnty,Cost) VALUES (@ItemId,@ItemName,@Qnty,@Cost)";
con.Open();
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
cmd.Parameters.Add("@ItemId", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@ItemName", SqlDbType.NVarChar).Value = ItemName;
cmd.Parameters.Add("@Qnty", SqlDbType.NVarChar).Value = Qnty;
cmd.Parameters.Add("@Cost", SqlDbType.NVarChar).Value = Cost;
cmd.ExecuteNonQuery();
}
con.Close();
}
}
Output:
Best regards
Cathy
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
168 Points
497 Posts
repeater control insert functionality needed
Nov 20, 2016 10:11 AM|acmedeepak|LINK
I have a repeater which creates multiple rows with textboxes, I need to insert the filled multiple values into a datatable using a query. My repeater ASPX code is:
<asp:Repeater ID="Repeater2" runat="server" > <ItemTemplate> <tr> <td> <asp:TextBox ID="ItemId" class="form-control" runat="server" Width="150" Text='' ></asp:TextBox> </td> <td> <asp:TextBox ID="ItemName" class="form-control" runat="server" Text='' Width="300" ></asp:TextBox> </td> <td> <asp:TextBox ID="Qnty" runat="server" Text='' Width="150" class="form-control" ></asp:TextBox> </td> <td> <asp:TextBox ID="Cost" class="form-control" runat="server" Text='' Width="150" ReadOnly="True"> </asp:TextBox> </td> <td> <asp:TextBox ID="Value" runat="server" class="form-control" Text='' Width="150" ReadOnly="True" ></asp:TextBox> </td> <td > <asp:imagebutton runat="server" class="delet-icon" ID="Imagebutton2" CommandName ="DeleteRow" onclick="imgbtndelete_Click" src="images/icon-delete.png" ></asp:imagebutton> </td> </tr> </ItemTemplate> </asp:Repeater>And in addition, I need to do this as bulk insert for multiple values. Please give me a code for button click where all the repeater textboxes filled values moves into database. My .cs code is:
protected void Repeater2_ItemCommand(object source, RepeaterCommandEventArgs e) { if (e.CommandName == "UpdateDatabase") { try { string SQLIns = ""; string newSalesInvyNum = TxtInventoryNumber.Text; string newInvyDate = txtInventoryDate.Text; string newSite = TxtCountSite.Text; string newItemId = ((TextBox)e.Item.FindControl("ItemId")).Text; string newItemName = ((TextBox)e.Item.FindControl("ItemName")).Text; string newQty = ((TextBox)e.Item.FindControl("Qnty")).Text; Int32 newNumQty = Convert.ToInt32(newQty); string newCost = ((TextBox)e.Item.FindControl("Value")).Text; Int32 Orgnl_Qty; //--- Check for the same item already exists... string SQL_verify = ""; //SQL_verify = "select ItemId,Qty from IC_Inventory_T where Invy_Num = '" + newSalesInvyNum + "' and ItemId = '" + newItemId + "'"; SqlDataReader ResData; SqlConnection conn = new SqlConnection(ConString); if (conn.State == ConnectionState.Closed) { conn.Open(); } //SQLIns = "Insert into IC_Inventory_T (Invy_Num,Invy_Date,SiteID,ItemId,ItemName,Qty,Cost) values ('" + newSalesInvyNum + "','" + newInvyDate + "','" + newSite + "','" + newItemId + "','" + newItemName + "','" + newNumQty + "','" + newCost + "')"; SqlCommand cmd = new SqlCommand("IC_INVENTORY_TINSERT", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@newSalesInvyNum", newSalesInvyNum); cmd.Parameters.AddWithValue("@newInvyDate", newInvyDate); cmd.Parameters.AddWithValue("@newSite", newSite); cmd.Parameters.AddWithValue("@newItemId", newItemId); cmd.Parameters.AddWithValue("@newItemName", newItemName); cmd.Parameters.AddWithValue("@newNumQty", newNumQty); cmd.Parameters.AddWithValue("@newCost", newCost); conn = new SqlConnection(ConString); if (conn.State == ConnectionState.Closed) { conn.Open(); } string Str1 = newQty; string Str2 = newCost; double Num1, Num2; bool isNum1 = double.TryParse(Str1, out Num1); bool isNum2 = double.TryParse(Str2, out Num2); if ((isNum1) && (isNum2)) { int temp = cmd.ExecuteNonQuery(); } else { lblMessage.CssClass = "alert alert-warning"; lblMessage.Text = "Qty and Cost should be numeric"; } lblMessage.CssClass = ""; lblMessage.Text = ""; conn.Close(); } //ResData.Close(); //-- End of Check for the same item already exists... catch (Exception ex) { lblMessage.CssClass = "alert alert-warning"; lblMessage.Text = "Qty and Cost should be numeric"; } } GetData(); dc.Rows.Add("", "", "", "", ""); Repeater2.DataSource = dc; Repeater2.DataBind(); }This actually does not return anything or store the values in database. Please help me with the code
Contributor
7095 Points
1966 Posts
Re: repeater control insert functionality needed
Nov 21, 2016 04:22 AM|anuj_koundal|LINK
Hi
You have used
inside your cs code but there is no button having this command name. Create an update button with "UpdateDatabase" command name.
Refer this: Repeater Edit,Update,Delete in Asp .net
Asp .Net Tutorials | www.codingfusion.com
Bank IFSC Codes
Ajax Control Toolkit
Star
8670 Points
2882 Posts
Re: repeater control insert functionality needed
Nov 21, 2016 08:55 AM|Cathy Zou|LINK
Hi, prasannaindia,
Based on your needs, I make an example, it work well, you could refer to it:
<asp:Repeater ID="Repeater2" runat="server" DataSourceID="SqlDataSource1" OnItemCommand="Repeater2_ItemCommand"> <ItemTemplate> <table> <tr> <td> ItemId: <asp:TextBox ID="ItemId" class="form-control" runat="server" Width="150" Text=' ' ></asp:TextBox> </td> <td> ItemName: <asp:TextBox ID="ItemName" class="form-control" runat="server" Text='' Width="300" ></asp:TextBox> </td> <td> Qnty: <asp:TextBox ID="Qnty" runat="server" Text=' ' Width="150" class="form-control" ></asp:TextBox> </td> <td> Cost: <asp:TextBox ID="Cost" class="form-control" runat="server" Text=' ' Width="150" > </asp:TextBox> </td> </tr> </table> </ItemTemplate> <FooterTemplate> <asp:Button ID="Button1" runat="server" Text="Save" CommandName="UpdateDatabase"/> </FooterTemplate> </asp:Repeater>CodeBehind:
using System.Configuration; using System.Data; using System.Data.SqlClient; protected void Repeater2_ItemCommand(object source, RepeaterCommandEventArgs e) { if (e.CommandName == "UpdateDatabase") { foreach (RepeaterItem item in Repeater2.Items) { TextBox TextBoxid = (TextBox)item.FindControl("ItemId"); TextBox TextBoxItemName = (TextBox)item.FindControl("ItemName"); TextBox TextBoxQnty = (TextBox)item.FindControl("Qnty"); TextBox TextBoxCost = (TextBox)item.FindControl("Cost"); int id = Convert.ToInt32(TextBoxid.Text.ToString()); string ItemName = TextBoxItemName.Text.ToString(); string Qnty = TextBoxQnty.Text.ToString(); string Cost = TextBoxCost.Text.ToString(); InsertRow(id, ItemName, Qnty, Cost); } } } public void InsertRow(int id, string ItemName, string Qnty, string Cost) { string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { string query = "INSERT INTO NEWTable(ItemId,ItemName,Qnty,Cost) VALUES (@ItemId,@ItemName,@Qnty,@Cost)"; con.Open(); using (SqlCommand cmd = new SqlCommand(query)) { cmd.CommandType = CommandType.Text; cmd.Connection = con; cmd.Parameters.Add("@ItemId", SqlDbType.Int).Value = id; cmd.Parameters.Add("@ItemName", SqlDbType.NVarChar).Value = ItemName; cmd.Parameters.Add("@Qnty", SqlDbType.NVarChar).Value = Qnty; cmd.Parameters.Add("@Cost", SqlDbType.NVarChar).Value = Cost; cmd.ExecuteNonQuery(); } con.Close(); } }Output:
Best regards
Cathy
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.