I'm new to C# and I don't think I have good grasp on how to write to a database yet. I'm not sure how to write checkboxes to a specific column in a database. If you can see an area I need to specifically study, please let me know. It's hard to know what I need to know in trying to teach myself how to do things like this. Thank you for your help.
If you need any further information, just let me know.
Goal: My goal is to write the Checkbox selections to a table in a database. Each checkbox has a column in a table, and I just want a 'Y' to be populated if something is checked and nothing populated if it's not checked. The titles of the columns are the same as the checkbox labels.
Problem: I'm not sure how to get the data from the checkboxlist, into the database.
DB Structure:
Website draft sample:
HTML:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Revocations.Default_2" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="Styles/StyleSheet1.css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div class="t_row_header">
<asp:Image ID="imgLogo" runat="server" CssClass="logo" ImageUrl="~/Images/logo.png" />
</div>
<h1>Transfer Revocations</h1>
<div class="form-fields">
<div class="form-group-left">
<label>
<asp:RequiredFieldValidator ID="rqvldSchoolYear" runat="server" ControlToValidate="txtSchoolYear" CssClass="ErrorMessage" ErrorMessage="Please enter a school year." SetFocusOnError="True">*</asp:RequiredFieldValidator>
School Year:
<asp:CompareValidator ID="cmpvldSchoolYear" runat="server" ControlToValidate="txtSchoolYear" CssClass="ErrorMessage" ErrorMessage="Please enter a school year >= 2017." Operator="GreaterThanEqual" SetFocusOnError="True" ValueToCompare="2017">*</asp:CompareValidator>
</label>
<asp:TextBox ID="txtSchoolYear" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="rqvldStudentID" runat="server" ControlToValidate="txtStudentID" CssClass="ErrorMessage" ErrorMessage="Please enter a student ID." SetFocusOnError="True">*</asp:RequiredFieldValidator>
Student ID:</label> <asp:TextBox ID="txtStudentID" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="rqvldFirstName" runat="server" ControlToValidate="txtFirstName" CssClass="ErrorMessage" ErrorMessage="Please enter a first name." SetFocusOnError="True">*</asp:RequiredFieldValidator>
First Name:</label> <asp:TextBox ID="txtFirstName" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="rqvldLastName" runat="server" ControlToValidate="txtLastName" CssClass="ErrorMessage" ErrorMessage="Please enter a last name" SetFocusOnError="True">*</asp:RequiredFieldValidator>
Last Name:</label> <asp:TextBox ID="txtLastName" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="rqvldRevokedSchool" runat="server" ControlToValidate="txtRevokedSchool" CssClass="ErrorMessage" ErrorMessage="Please enter the revoking school" SetFocusOnError="True">*</asp:RequiredFieldValidator>
Revoked School:</label> <asp:TextBox ID="txtRevokedSchool" runat="server" CssClass="txtBox"></asp:TextBox>
<label>
<asp:RequiredFieldValidator ID="rqvldRevocationDate" runat="server" ControlToValidate="txtRevocationDate" CssClass="ErrorMessage" ErrorMessage="Please enter a revocation date" SetFocusOnError="True">*</asp:RequiredFieldValidator>
Revocation Date:</label> <asp:TextBox ID="txtRevocationDate" runat="server" TextMode="Date" CssClass="txtBox"></asp:TextBox>
</div>
<!-- form-group-left -->
<div class="form-group-right">
<label>
Reason:<asp:CheckBoxList ID="chkbxlst1" runat="server">
<asp:ListItem text="Academics" Value="1"></asp:ListItem>
<asp:ListItem text="Attendance" Value="2"></asp:ListItem>
<asp:ListItem text="Behavior" Value ="3"></asp:ListItem>
<asp:ListItem text="Cooperative Relationship" Value="4"></asp:ListItem>
</asp:CheckBoxList>
<br />
</label>
<label>
<asp:RequiredFieldValidator ID="rqvldNotes" runat="server" ControlToValidate="txtNotes" CssClass="ErrorMessage" ErrorMessage="Please enter a note." SetFocusOnError="True">*</asp:RequiredFieldValidator>
Notes:</label> <asp:TextBox ID="txtNotes" runat="server" height="218px" TextMode="MultiLine" CssClass="InsideShadow"></asp:TextBox>
<br />
<asp:Button ID="btnSubmit" runat="server" Text="SUBMIT" CssClass="btn" OnClick="btnSubmit_Click" />
</div>
</div>
<asp:ValidationSummary ID="ValidationSummary1" runat="server" CssClass="ErrorMessage" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPSS_RevocationConnection %>" DeleteCommand="SELECT
[SchoolYear]
,[Student_ID]
,[Last_Name]
,[First_Name]
,[Revoked_School]
,CAST([Revocation_Date] AS DATE) AS [Revocation_Date]
,[Notes]
,[Academics]
,[Attendance]
,[Behavior]
,[Cooperative Relationship]
FROM [PPSS_Work].[Transfers].[Revocations]"
SelectCommand="SELECT
[SchoolYear]
,[Student_ID]
,[Last_Name]
,[First_Name]
,[Revoked_School]
,CAST([Revocation_Date] AS DATE) AS [Revocation_Date]
,[Notes]
,[Academics]
,[Attendance]
,[Behavior]
,[Cooperative Relationship]
FROM [PPSS_Work].[Transfers].[Revocations]"
UpdateCommand="SELECT
[SchoolYear]
,[Student_ID]
,[Last_Name]
,[First_Name]
,[Revoked_School]
,CAST([Revocation_Date] AS DATE) AS [Revocation_Date]
,[Notes]
,[Academics]
,[Attendance]
,[Behavior]
,[Cooperative Relationship]
FROM [PPSS_Work].[Transfers].[Revocations]"></asp:SqlDataSource>
</form>
</body>
</html>
C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
namespace Revocations
{
public partial class Default_2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
// Establish SQL Connection
SqlConnection sqlCon = new SqlConnection("Data Source = acctsql; Initial Catalog = PPSS_Work; Integrated Security = True");
{
// Write INSERT SQL statement
SqlCommand sqlCom = new SqlCommand("INSERT INTO [PPSS_Work].[Transfers].[t_Revocations](SchoolYear, Student_ID, Last_Name, First_Name, Revoked_School,Revocation_Date, Academics, Attendance, Behavior, Cooperative Relationship, Notes) VALUES(@SchoolYear, @Student_ID, @First_Name, @Last_Name, @Revoked_School, @Revocation_Date,@Academics, @Attendance, @Behavior, @Cooperative Relationship, @Notes)", sqlCon);
sqlCom.Parameters.AddWithValue("@SchoolYear", txtSchoolYear.Text);
sqlCom.Parameters.AddWithValue("@Student_ID", txtStudentID.Text);
sqlCom.Parameters.AddWithValue("@First_Name", txtFirstName.Text);
sqlCom.Parameters.AddWithValue("@Last_Name", txtLastName.Text);
sqlCom.Parameters.AddWithValue("@Revoked_School", txtRevokedSchool.Text);
sqlCom.Parameters.AddWithValue("@Revocation_Date", txtRevocationDate.Text);
sqlCom.Parameters.AddWithValue("@Notes", txtNotes.Text);
sqlCom.Parameters.AddWithValue("@Academics", txtNotes.Text);
sqlCom.Parameters.AddWithValue("@Attendance", txtNotes.Text);
sqlCom.Parameters.AddWithValue("@Behavior", txtNotes.Text);
sqlCom.Parameters.AddWithValue("@[Cooperative Relationship]", txtNotes.Text);
sqlCon.Open();
sqlCom.ExecuteNonQuery();
sqlCon.Close();
if (IsPostBack)
{
txtSchoolYear.Text = "";
txtStudentID.Text = "";
txtFirstName.Text = "";
txtLastName.Text = "";
txtRevokedSchool.Text = "";
txtRevocationDate.Text = "";
chkbxlst1.Text = "";
txtNotes.Text = "";
}
}
}
}
}
