I have generated Item no on GridView row databound.
I have stored the whole GridView values to database using SqlBulkCopy.
All the values stored except the item no.
Please help
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns = "false" Font-Size="13px" BorderColor="Silver" BorderStyle="None" BorderWidth="1px"
OnRowDeleting="OnRowDeleting" OnRowDataBound = "OnRowDataBound" GridLines="None" HeaderStyle-ForeColor="#FF9900" onselectedindexchanged="GridView1_SelectedIndexChanged">
<Columns>
<asp:TemplateField HeaderText="Id" ItemStyle-Width="30" HeaderStyle-CssClass="adda" ItemStyle-CssClass="ada">
<ItemTemplate>
<%# Eval("Id") %>
<input type="hidden" name="LocationId" value='<%# Eval("Id") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Preference" HeaderText="S.No" ItemStyle-Width="10px" ItemStyle-CssClass="adda" HeaderStyle-CssClass="ada" />
<asp:TemplateField HeaderText="Item No">
<ItemTemplate>
<asp:Label ID="lblRowNumber" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Pres_Item" HeaderText="Medicine Name" ItemStyle-Width="300px" ItemStyle-CssClass="add" HeaderStyle-CssClass="ad"/>
<asp:BoundField DataField="Pres_Item_Dosage" HeaderText="Dosage" ItemStyle-Width="50px" ItemStyle-CssClass="add" HeaderStyle-CssClass="ad"/>
<asp:BoundField DataField="Pres_Item_Uom" HeaderText="Uom" ItemStyle-Width="50px" ItemStyle-CssClass="add" HeaderStyle-CssClass="ad"/>
<asp:BoundField DataField="Pres_Item_Usage" HeaderText="Usage" ItemStyle-Width="300px" ItemStyle-CssClass="add" HeaderStyle-CssClass="ad"/>
<asp:BoundField DataField="Type" HeaderText="Type" ItemStyle-Width="300px" ItemStyle-CssClass="adda" HeaderStyle-CssClass="ada"/>
<asp:CommandField ButtonType="Button" ControlStyle-BorderStyle="None" ControlStyle-BackColor="#5DADE2" ControlStyle-ForeColor="White" HeaderStyle-CssClass="ad" ItemStyle-CssClass="add" ShowSelectButton="True" />
</Columns>
</asp:GridView>
int i=1;
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string item = e.Row.Cells[7].Text;
if (item == "Main" && item != " ")
{
(e.Row.FindControl("lblRowNumber") as Label).Text = i.ToString();
i++;
}
}
}
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[13] {new DataColumn("Date", typeof(string)), new DataColumn("doctcode", typeof(int)), new DataColumn("Regno",typeof(string)),
new DataColumn("preference", typeof(int)), new DataColumn("itemno", typeof(string)), new DataColumn("Item",typeof(string)), new DataColumn("Dosage",typeof(string)), new DataColumn("Uom",typeof(string)),
new DataColumn("Usage",typeof(string)), new DataColumn("Days",typeof(string)), new DataColumn("diet",typeof(string)), new DataColumn("visitno",typeof(string)), new DataColumn("type",typeof(string)) });
foreach (GridViewRow row in GridView1.Rows)
{
string date = DateTime.Parse(txtdate.Text).ToString("yyyy-MM-dd");
int dctcode = int.Parse(txtdctcode.Text);
string regno = txtid.Text;
int preference = int.Parse(row.Cells[1].Text.Replace(" ", ""));
string itemno = row.Cells[2].Text.Replace(" ", "");
string item = row.Cells[3].Text.Replace(" ", "");
string Dosage = row.Cells[4].Text.Replace(" ", "");
string Uom = row.Cells[5].Text.Replace(" ", "");
string Usage = row.Cells[6].Text.Replace(" ", "");
string days = txtduration.Text;
string diet = txtdietcode.Text;
string visitno = txtvisit.Text;
string type = row.Cells[7].Text.Replace(" ", "");
dt.Rows.Add(date, dctcode, regno, preference, itemno, item, Dosage, Uom, Usage, days, diet, visitno, type);
}
if (dt.Rows.Count > 0)
{
string consString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "Presc";
sqlBulkCopy.ColumnMappings.Add("Date", "Pres_Date");
sqlBulkCopy.ColumnMappings.Add("doctcode", "Pres_Doct_Code");
sqlBulkCopy.ColumnMappings.Add("Regno", "Pres_Reg_No");
sqlBulkCopy.ColumnMappings.Add("preference", "Preference");
sqlBulkCopy.ColumnMappings.Add("itemno", "Pres_Item_No");
sqlBulkCopy.ColumnMappings.Add("Item", "Pres_Item");
sqlBulkCopy.ColumnMappings.Add("Dosage", "Pres_Item_Dosage");
sqlBulkCopy.ColumnMappings.Add("Uom", "Pres_Item_Uom");
sqlBulkCopy.ColumnMappings.Add("Usage", "Pres_Item_Usage");
sqlBulkCopy.ColumnMappings.Add("Days", "Pres_Days");
sqlBulkCopy.ColumnMappings.Add("diet", "Pres_Diet_Code");
sqlBulkCopy.ColumnMappings.Add("visitno", "Pres_Visit_No");
sqlBulkCopy.ColumnMappings.Add("type", "Type");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
Response.Write("<script>alert('Saved successfully');</script>");
}
}
}
