Microsoft SQL Server Topics: Introduction to Entry

	Introduction to Data Entry

Introduction

This is an introduction to creating records for a database table.

Practical Learning: Introducing Data Selection

Start Microsoft Visual Studio
Start a new empty web site name it ide
Click OK
On the main menu, click WEBSITE -> Add New Item...
Accept the Web Form option and set the name to NewGradingScale
Click Add

Design the web page as follows:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="NewGradingScale.aspx.cs" Inherits="NewGradingScale" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Monson University: Students Grading Scales - New</title>

<style type="text/css">
.LabeledCell {
            width: 150px;
}
</style>
</head>
<body>
<h1>Monson University: Students Grading Scales - New</h1>

<form id="frmNewGradingScale" runat="server">
  <div>
    <table>
        <tr>
            <td class="LabeledCell">Letter Grade:</td>
            <td>
                <asp:TextBox ID="TxtLetterGrade" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Minimum Range:</td>
            <td>
                <asp:TextBox ID="TxtMinimumRange" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Maximum Range:</td>
            <td>
                <asp:TextBox ID="TxtMaximumRange" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Minimum Percentage:</td>
            <td>
                <asp:TextBox ID="TxtMinimumPercentage" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Maximum Percentage:</td>
            <td>
                <asp:TextBox ID="TxtMaximumPercentage" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Descriptor:</td>
            <td>
                <asp:TextBox ID="TxtDescriptor" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="BtnReset" runat="server" Text="Reset" Width="124px" />
            </td>
            <td>
                <asp:Button ID="BtnSubmit" runat="server" Text="Submit" Width="124px" />
            </td>
        </tr>
    </table>
  </div>
</form>
</body>
</html>

Double-click the Reset button

Change the file as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class NewGradingScale : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void BtnReset_Click(object sender, EventArgs e)
    {
        using (SqlConnection cntUniversity = new SqlConnection("Data Source=(local);" +
                                                               "Integrated Security=Yes"))
        {
            SqlCommand cmdUniversity = new SqlCommand("CREATE DATABASE University2;", cntUniversity);
            cntUniversity.Open();
            cmdUniversity.ExecuteNonQuery();
        }

        using (SqlConnection cntUniversity =
            new SqlConnection("Data Source=(local);" +
                              "Database='University2';" +
                              "Integrated Security=Yes"))
        {
            SqlCommand cmdUniversity = new SqlCommand("CREATE SCHEMA StudentsAffairs;", cntUniversity);
            cntUniversity.Open();
            cmdUniversity.ExecuteNonQuery();
        }

        using (SqlConnection cntUniversity = new SqlConnection("Data Source=(local);" +
                                  "Database='University2';" +
                                  "Integrated Security=Yes"))
        {
            SqlCommand cmdUniversity =
                new SqlCommand("CREATE TABLE StudentsAffairs.StudentsGradeScale" +
                              "(" +
                              " 	LetterGrade nvarchar(2)," +
                              " 	MinimumRange decimal(6, 2)," +
                              " 	MaximumRange decimal(6, 2)," +
                              " 	MinimumPercentage tinyint," +
                              " 	MaximumPercentage tinyint," +
                              " 	Descriptor nvarchar(25)" +
                              ");",
                               cntUniversity);
            cntUniversity.Open();
            cmdUniversity.ExecuteNonQuery();
        }
    }
}

Execute the application
Click the Reset button on the web form (and wait a few seconds to execute) and close the browser to return to your programming environment
Click the NewGradingScale.aspx tab
On the web form, double-click the Submit button

Change the file as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class NewGradingScale : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void BtnReset_Click(object sender, EventArgs e)
    {
        TxtLetterGrade.Text = "";
        TxtMinimumRange.Text = "";
        TxtMaximumRange.Text = "";
        TxtMinimumPercentage.Text = "";
        TxtMaximumPercentage.Text = "";
        TxtDescriptor.Text = "";
      
    }
    protected void BtnSubmit_Click(object sender, EventArgs e)
    {
        double MinimumRange = 0.00;
        double MaximumRange = 0.00;
        byte MinimumPercentage = 0;
        byte MaximumPercentage = 0;

        if(string.IsNullOrEmpty(TxtMinimumRange.Text))
            MinimumRange = 0.00;
        else
            MinimumRange = double.Parse(TxtMinimumRange.Text);

        if(string.IsNullOrEmpty(TxtMaximumRange.Text))
            MaximumRange = 4.00;
        else
            MaximumRange = double.Parse(TxtMaximumRange.Text);

        if(string.IsNullOrEmpty(TxtMinimumPercentage.Text))
            MinimumPercentage = 0;
        else
            MinimumPercentage = byte.Parse(TxtMinimumPercentage.Text);

        if(string.IsNullOrEmpty(TxtMaximumPercentage.Text))
            MaximumPercentage = 0;
        else
            MaximumPercentage = byte.Parse(TxtMaximumPercentage.Text);

        using (SqlConnection cntUniversity = new SqlConnection("Data Source=(local);" +
                                  "Database='University2';" +
                                  "Integrated Security=Yes"))
        {
            SqlCommand cmdUniversity =
                new SqlCommand("INSERT INTO StudentsAffairs.StudentsGradeScale " +
                               "VALUES(N'" + TxtLetterGrade.Text + "', " + MinimumRange
                                + ", " + MaximumRange + ", " + MinimumPercentage + ", " +
                               MaximumPercentage + ", N'" + TxtDescriptor.Text + "');",
                               cntUniversity);
            cntUniversity.Open();
            cmdUniversity.ExecuteNonQuery();
        }

        BtnReset_Click(sender, e);
    }
}

Execute the application
Enter the following values:
Letter Grade: A
Minimum Range: 4.00
Minimum Percentage: 95
Maximum Percentage: 100
Descriptor: Excellent
Click Submit

Create new records as follows:

Letter Grade	Min Range	Max Range	Min %	Max %	Descriptor
A-	3.67	3.99	90	94	Excellent
B+	3.33	3.66	85	89	Good
B	3.00	3.32	80	84	Good
B-	2.67	2.99	75	79	Good
C+	2.33	2.66	70	74	Satisfactory
C	2.00	2.32	65	69	Satisfactory
C-	1.67	1.99	60	64	Satisfactory
D+	1.33	1.66	55	59	Satisfactory
D	1.00	1.32	50	54	Satisfactory
F	0.00	0.99	0	49	Unsatisfactory