Currently, I have a gridview and table with a dropdownlist that is binded with the database.
Based on the user's selection and their ID, I want to validate if the dropdownlist value exist in gridview before inserting the value into the column(Category). any help that i can achieve this?
What I have tried:
WebForm.aspx
Gridview that display the data
<asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" OnPageIndexChanging="Gridview1_PageIndexChanging" OnRowCancelingEdit="Gridview1_RowCancelingEdit" OnRowCommand="Gridview1_RowCommand" OnRowDeleting="Gridview1_RowDeleting" ShowFooter="True" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" ShowHeaderWhenEmpty="True" Width="100%" CssClass="table table-responsive table-bordered" OnSelectedIndexChanged="Gridview1_SelectedIndexChanged" DataKeyNames="CID">
<Columns>
<asp:TemplateField HeaderText="Transaction ID">
<EditItemTemplate>
<asp:Label ID="lbleditid" runat="server" Text='<%# Bind("CID") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblid" runat="server" Text='<%# Bind("CID") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="hidden-xs" />
<ItemStyle CssClass="hidden-xs" />
</asp:TemplateField>
<asp:TemplateField HeaderText="CCID">
<EditItemTemplate>
<asp:Label ID="lblccid2" runat="server"></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblccid" runat="server" Text='<%# Bind("CCID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Category" HeaderText="Category" />
<asp:TemplateField HeaderText="Last Edited">
<EditItemTemplate>
<asp:TextBox ID="txtDateTime1" runat="server" Text='<%# Bind("Last_Edited") %>' ReadOnly="True"></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtDateTime" runat="server" ToolTip="DD/MM/YYYY" ReadOnly="True"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="lblDateTime" runat="server" Text='<%# Bind("Last_Edited") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" ShowHeader="True" />
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LinkButton3" runat="server" OnClick="PassData">Select</asp:LinkButton>
</ItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="true" CommandName="AddNew" Text="Add New"></asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
No record shown
</EmptyDataTemplate>
</asp:GridView>
Table that is going to insert the data to the Gridview
<table style="width: 100%;">
<tr>
<td class="auto-style1"> </td>
<td>
<asp:CustomValidator ID="CustomValidator1" runat="server" ControlToValidate="DropDownList1" ErrorMessage="CustomValidator" OnServerValidate="CustomValidator1_ServerValidate"></asp:CustomValidator>
</td>
<td> </td>
</tr>
<tr>
<td class="auto-style1">Category</td>
<td>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="Desc" DataValueField="Desc">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStr %>" SelectCommand="SELECT [Desc] FROM [Categories]"></asp:SqlDataSource>
</td>
<td> </td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label2" runat="server"></asp:Label>
</td>
<td>
<asp:Button ID="Button1" runat="server" Text="Add Category" OnClick="Button1_Click" />
</td>
<td> </td>
</tr>
</table>
WebForm.aspx.cs
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
con.Open();
string query = "SELECT CATEGORY FROM CATEGORY WHERE C_UserName = '" + Session["id"] + "' AND CATEGORY = '" + DropDownList1.SelectedItem.Value + "' ";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
cmd.Parameters.AddWithValue("@CATEGORY", DropDownList1.SelectedItem.Value);
lblResult.Text = "You have selected this category. Please select a new category";
con.Close();
}
else
{
SqlCommand cmd1 = new SqlCommand("UPDATE SET CATEGORY CCID@CCID (CATEGORY, C_USERNAME, CCID) VALUES (@CATEGORY, @C_USERNAME, @CCID)", con);
cmd1.Parameters.AddWithValue("CATEGORY", DropDownList1.SelectedItem.Value);
cmd1.Parameters.AddWithValue("C_USERNAME", Session["id"]);
cmd1.Parameters.AddWithValue("CCID", Label1.Text);
con.Open();
int i = cmd1.ExecuteNonQuery();
con.Close();
if (i != 0)
{
Label2.Text = " Your data is been saved in the database";
Label2.ForeColor = System.Drawing.Color.ForestGreen;
}
else
{
Label2.Text = "Something went wrong with selection";
Label2.ForeColor = System.Drawing.Color.Red;
}
}
}
Another Update: Now, it kept prompting a connection not closed error
