Techniques of Selecting Records
Techniques of Selecting Records
Fundamentals of Columns Selections
SELECTing a Column
Remember that the primary formula to make (a) selection(s) on a table is:
SELECT what-field(s) FROM what-object;
To select a column, you can replace the what-field(s) placeholder of our formula with the name of the desired column. For example, to get a list of last names of students of a table, you would execute the following statement:
SELECT LastName FROM Students;
GO
If the table was created in a particular schema, make sure to qualify the name of the table with its schema. Here is an example:
SELECT LastName FROM Registration.Students;
GO
You can also qualify a column by preceding it with the name of the table followed by the period operator. The above statement is equivalent to:
SELECT Students.LastName FROM Registration.Students;
When you execute the statement, only the column that contains the last names would be selected.
Practical Learning: Introducing Records Selections
<!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>@ViewBag.Title - Traffic Tickets Management</title> @Styles.Render("~/Content/css") @Scripts.Render("~/bundles/modernizr") </head> <body> <div class="navbar navbar-inverse navbar-fixed-top"> <div class="container"> <div class="navbar-header"> <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse"> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> @Html.ActionLink("Traffic Tickets Management", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" }) </div> <div class="navbar-collapse collapse"> <ul class="nav navbar-nav"> <li>@Html.ActionLink("Emergency/911", "Index", "Home")</li> <li>@Html.ActionLink("Government", "Index", "Home")</li> <li>@Html.ActionLink("Public Parks", "Index", "Home")</li> <li>@Html.ActionLink("Employment/Careers", "Index", "Home")</li> <li>@Html.ActionLink("About", "About", "Home")</li> <li>@Html.ActionLink("Contact", "Contact", "Home")</li> </ul> </div> </div> </div> <div class="container body-content"> @RenderBody() <hr /> <footer> <p style="text-align: center">© @DateTime.Now.Year - Traffic Tickets Management</p> </footer> </div> @Scripts.Render("~/bundles/jquery") @Scripts.Render("~/bundles/bootstrap") @RenderSection("scripts", required: false) </body> </html>
@{
ViewBag.Title = "Welcome";
}
<div class="row">
<div class="col-md-4">
<h2>Constituents</h2>
<p>Here, you will find out about available services and opportunities. These include
employment assistance and financial services.</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301865">Learn more »</a></p>
</div>
<div class="col-md-4">
<h2>County Board</h2>
<p>Get to know the county executives. Read their biographies. Don't hesitate to reach
out to them. And make suggestions!</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301866">Learn more »</a></p>
</div>
<div class="col-md-4">
<h2>Police/Safety</h2>
<p>Our constituents safety is our top priority. Inform us about your concerns in energy
(water, electricity, transportation, communication, etc).</p>
<p><a class="btn btn-default" href="https://go.microsoft.com/fwlink/?LinkId=301867">Learn more »</a></p>
</div>
</div>
<div class="jumbotron">
<h1>Solomon County</h1>
<p class="lead">Our county is here to serve you, assist you, and lead you. We have gathered
various types of community, social, personal, and financial resources.</p>
</div>
USE master; GO CREATE DATABASE TrafficTicketSystem1; GO USE TrafficTicketSystem1; GO
<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=301880
-->
<configuration>
. . .
<connectionStrings>
<add name="csTrafficTicketsManagement"
connectionString="Data Source=(local);Database='TrafficTicketSystem1';Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
. . .<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=301880
-->
<configuration>
. . .
<connectionStrings>
<add name="csTrafficTicketsManagement"
connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\TrafficTicketSystem1.mdf;Initial Catalog=TrafficTicketSystem1;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
. . .
CREATE SCHEMA CitationDivision;
GO
CREATE TABLE CitationDivision.ViolationsCategoryies
(
ViolationCategory NVARCHAR(50)
);
GO
CREATE TABLE CitationDivision.CamerasTickets
(
TicketNumber INT IDENTITY(100001, 1),
CameraNumber NCHAR(10) NOT NULL,
VehicleTagNumber NVARCHAR(20) NOT NULL,
ViolationDate DATE NOT NULL,
ViolationTime TIME NOT NULL,
ViolationCategory NVARCHAR(50),
ViolationLocation NVARCHAR(50) NOT NULL,
MediaTransferDate DATETIME,
PhotoAvailable BIT,
VideoAvailable BIT,
PaymentDueDate DATE,
AmountDue SMALLINT
);
GO
INSERT INTO CitationDivision.ViolationsCategoryies VALUES(N'Speed'), (N'Red Light'), (N'Stop Sign'), (N'Reckless Driving'), (N'Illegal U-Turn'), (N'Illegal Right-Turn'), (N'Holding Ellectro-Device While Derving'), (N'Leaving Scene After Accident');
GO
INSERT INTO CitationDivision.CamerasTickets(CameraNumber, VehicleTagNumber, ViolationDate, ViolationTime, ViolationCategory, ViolationLocation, MediaTransferDate, PhotoAvailable, VideoAvailable, PaymentDueDate, AmountDue)
VALUES(N'QGD-309586', N'JHK WRT', N'08-31-2018', N'14:47:15', N'Speed', N'Holland Str', N'10-04-2018 10:05:25', 1, 0, N'11-06-2018', 85),
(N'BKR-880248', N'8DT9403', N'06-01-2017', N'01:16:44 AM', N'Red Light', N'Woodson Rd and Cissera Ave', N'06/22/17 09:42:55 AM', 0, 1, N'08-05-2017', 75),
(N'LGU-602049', N'BPT-3095', N'05/27/2018', N'06:18:05 AM', N'Stop Sign', N'Sommerset Rd and Clarenton Blvd', N'13-June-2018 20:12:44', 1, 1, N'08/02/2018', 60),
(N'ANR-928374', N'M-280468', N'07-15-2018', N'13:47:27', N'Red Light', N'Samson Ave and Jameson Str', N'16-Jul-2018 11:32:38 AM', 1, 1, N'09-16-2018', 125),
(N'QGD-309586', N'850684', N'21-September-2018', N'02:18:13 AM', N'Speed', N'Holland Str', N'10-04-2018 10:08:12', 1, 1, N'08-Nov-18', 75),
(N'PTH-927947', N'M-280468', N'07-15-2018', N'13:52:29', N'Speed', N'Samson Ave and Eastern Str', N'05-August-18 14:17:09', 1, 0, N'09-16-2018', 75),
(N'BKR-880248', N'GHL-9283', N'06-01-2017', N'00:48:17 AM', N'Illegal Right-Turn', N'Woodson Rd and Cissera Ave', N'06/22/17 10:17:42 AM', 0, 1, N'08-05-2017', 60),
(N'BKR-880248', N'928497', N'06-01-2017', N'02:24:39 AM', N'Illegal Right-Turn', N'Woodson Rd and Cissera Ave', N'06/22/17 10:20:07', 1, 1, N'08-05-2017', 60),
(N'LDG-174048', N'KKL614', N'04/14/2018', N'16:27:01', N'Speed', N'Rte 728 Sumnerville', N'04-17-2018 18:15:22', 1, 0, N'04-28-2018', 75),
(N'QGD-309586', N'KGD 2095', N'21-SEP-2018', N'05:35:19 AM', N'Speed', N'Holland Str', N'10-04-2018 10:22:25', 1, 0, N'11-06-2018', 85),
(N'WJN-294075', N'M-280468', N'10-31-2018', N'10:14:25', N'Reckless Driving', N'Manassa Blvd', N'11-Nov-2018 22:46:07', 0, 1, N'09-16-2018', 40),
(N'PTH-927947', N'KSD4LKE', N'07-15-2018', N'13:58:06', N'Speed', N'Samson Ave and Eastern Str', N'05-August-18 04:24:25 PM', 0, 1, N'09-16-2018', 75);
GOusing System.Web.Mvc;
namespace TrafficTicketsManagement1.Controllers
{
public class TicketsProcessingController : Controller
{
// GET: TicketsProcessing
public ActionResult Index()
{
return View();
}
// GET: TicketsProcessing/TicketsSummary
public ActionResult TicketsSummary()
{
return View();
}
}
}Selecting Some Columns
As opposed to selecting all fields, you can select one particular column or a few columns. To do this, you can replace the what-field(s) factor in our formula with the names of the desired columns, separating them with commas column. The formula to follow is:
SELECT column_1, column_2, . . ., column_n FROM what-object;
For example, to get a list that includes the first name, the last name, the gender, the email address, and the home phone of records from a table called Students, you would create the SQL statement as follows:
SELECT FirstName, LastName, Gender, City, State FROM Students;
Once again, you can qualify each column by preceding it with the name of the table followed by the period operator. Here is an example:
SELECT Students.FirstName, Students.LastName, Students.DateofBirth, Students.Gender FROM Students;
You don't have to qualify all columns, you can qualify some and not qualify some others. Here is an example:
SELECT Students.FirstName, LastName, Students.DateOfBirth, Gender FROM Students;
You can use an alias name for a table by preceding each column with a letter or a word and a period operator, and then entering the name of the table followed by that letter or word. Here is an example:
SELECT std.FirstName, std.LastName, std.DateOfBirth, std.Gender FROM Students std;
Selecting Into a Table
Consider the following Employees table:
CREATE TABLE Employees
(
EmployeeNumber int unique not null,
FirstName nvarchar(20),
LastName nvarchar(20) not null,
Department nvarchar(50) null,
EmploymentStatus nvarchar(30),
HourlySalary money
);
GO
INSERT INTO Employees
VALUES(84628, N'Anthony', N'Holms', N'Corporate', N'Full Time', 24.72),
(40574, N'William', N'Wilson', N'Information Technology', N'Full Time', 21.18),
(27462, N'Peter', N'Swanson', N'Corporate', N'Full Time', 22.84),
(52835, N'Edward', N'Johansen', N'Information Technology', N'Consultant', 15.50),
(93075, N'Sharon', N'Edson', N'Accounting', N'Full Time', 28.74),
(82718, N'Yanuh', N'Hadhah', N'Corporate', N'Full Time', 14.86),
(29174, N'Hamin', N'Souleyman', N'Public Relations', N'Consultant', 18.76);
GO
You can use all or some records from an existing table to create a new table that would contain those existing records. To do this, use the following formula:
SELECT columns INTO new-table-name FROM existing-table
To use all columns and all records, after the SELECT operator, type * INTO, a name for the table you want to create, followed by FROM, and the name of the original table that contains the records. Here is an example:
SELECT * INTO CompanyRecipients FROM Employees;
GO
Instead of using all columns, you can specify only some columns after the SELECT keyword. Here is an example:
SELECT EmployeeNumber, LastName, FirstName, EmploymentStatus
INTO Salaried FROM Employees;
GO
Distinct Field Selection
If you specify a column to select from a table, every record of that column would be selected. This can cause the same value to repeat over and over. Sometimes you want to show each value only once. To get such a result, you can use the DISTINCT keyword before the name of the column in the SELECT statement.
To perform a distinct selection, in most cases, you would get a better result if you select only one column. Still, you can use as many columns as you want.
Using an Alias Name for a Column
If you create a SELECT statement, when you select a column, you must specify its name. If you are working in the Table window, the name of a column would display as the column header in the Results section. The string that displays in the column header is called a caption. As an alternative, if you want, you can display the caption of your choice. This is done by creating an alias for a column.
To create the alias of a column, in your SELECT statement, on the right side of the column name, put an empty space followed by the desired name of the column header. If the desired column header is in one word, you can simply type it. Here is an example:
SELECT CameraNumber Device
FROM CitationDivision.CamerasTickets;
GO
If you want the column header to appear with more than one word, provide the words as a string in single-quotes or between the square brackets: [ and ] . Here are examples:
SELECT TicketNumber 'Ticket #', CameraNumber Device, ViolationCategory [Type of Violation], PaymentDueDate [Payment Due Date] FROM CitationDivision.CamerasTickets; GO
As an alternative, instead of the empty space between the column name of its alias, use the AS keyword. Here are examples:
SELECT TicketNumber 'Ticket #', CameraNumber Device, VehicleTagNumber AS [Tag Nbr], ViolationCategory [Type of Violation], PaymentDueDate [Payment Due Date], AmountDue AS 'Pmt Amt' FROM CitationDivision.CamerasTickets; GO
Of course, you can qualify each column. Here are examples of preceding the names of columns by their parent table:
SELECT Students.FirstName AS [First Name], Students.LastName AS [Last Name], Students.DateOfBirth AS [Date of Birth], Students.City AS [City], Students.State FROM Students;
A Combination or Expression of Columns
When formulating a SELECT statement, you can combine some column names in an expression. You can use any of the available operators.
To create an expression in a SELECT statement, type the expression where a column would be used. Here is an example:
SELECT FirstName + N' ' + LastName
FROM Students;
This type of expression would appear without a caption in the results section of the Table window. To specify the caption of the column, use the alias features we reviewed in the previous section. Here is an example:
The addition can also be used on numeric values. All other arithmetic operators can be used. For example, you can multiply a weekly hours value to an hourly salary to get a weekly salary. Here is an example:
SELECT WeeklyHours * HourlySalary FROM Payroll
Of course, you can add as many expressions as you want in the SELECT operation of a table. You can also create an alias for an expression to give it the desired name. To do this, on the right side of the expression, add an alias as we saw already. Here are examples:
SELECT FirstName + N' ' + LastName AS [Full Name],
Address + N', ' + City + N' ' + State + N' ' + ZIPCode AS [Home Address],
EmrgName + N' ' + EmrgPhone AS [Emergency Contact]
FROM Students
The Assignment Operator
If you just create a regular expression using arithmetic operators, the new column would not have a name. The SQL allows you to specify a different name for any column during data selection or a name for an expression. This is done using the assignment operator "=".
To change the name of a column during data selection, in your SQL statement, on the right side of SELECT, type the desired name, followed by the assignment operator, followed by the actual name of the column. If the name you want to use is in one word, simply use it. If the name is in more than one word, include it either in single-quotes or between [ and ]. Here are examples:
SELECT TicketNumber 'Ticket #', CameraNumber Device, VehicleTagNumber AS [Tag Nbr], Period = ViolationPeriod, ViolationCategory [Type of Violation], 'Camera Position' = ViolationLocation, PaymentDueDate [Payment Due Date], AmountDue AS 'Pmt Amt' FROM CitationDivision.CamerasTickets; GO
WHEN a Column's Value Meets a Criterion
Consider the following table:
CREATE DATABASE DepartmentStore;
GO
USE DepartmentStore;
GO
CREATE TABLE Employees
(
FirstName nvarchar(20),
MI nchar(2),
LastName nvarchar(20),
HourlySalary smallmoney,
[Status] tinyint
);
GO
INSERT INTO Employees
VALUES(N'Christopher', N'L', N'Larsen', 14.50, 3),
(N'Henry', N'C', N'Jonathan', 12.85, 1),
(N'Lance', N'A', N'Seagal', 16.95, 1);
GO
INSERT INTO Employees(FirstName, LastName, Status)
VALUES(N'Roberta', N'Ortez', 1);
GO
INSERT INTO Employees(FirstName, LastName, HourlySalary)
VALUES(N'Paul', N'Swanson', 10.90);
GO
INSERT INTO Employees VALUES(N'Kristofer', N'L', N'Michaels', 12.85, 2);
GO
INSERT INTO Employees VALUES(N'Ivan', N'N', N'Sanders', 15.00, 2),
(N'David', N'P', N'Monahan', 13.05, 1);
GO
When writing your SELECT statement, the value of a column may not bew clear to the user. Consider the Employment column in the following query:
<!DOCTYPE html>
<html>
<head>
<title>Department Store - Employees</title>
</head>
<body>
<div align="center">
<h1>Department Store - Employees</h1>
@{
using(System.Data.SqlClient.SqlConnection scDepartmentStores =
new System.Data.SqlClient.SqlConnection("Integrated Security='True';Initial Catalog='DepartmentStore';Data Source=(Local)"))
{
string strEmployees = "SELECT FirstName, MI, LastName, HourlySalary, [Status] " +
"FROM Employees;";
System.Data.SqlClient.SqlCommand cmdEmployees = new System.Data.SqlClient.SqlCommand(strEmployees, scDepartmentStores);
scDepartmentStores.Open();
cmdEmployees.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaEmployees = new System.Data.SqlClient.SqlDataAdapter(cmdEmployees);
System.Data.DataSet dsEmployees = new System.Data.DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
<table border="6">
<tr style="font-weight: 600; background-color: navy; color: lightcyan">
<td>First Name</td>
<td style="text-align: center">MI</td>
<td>Last Name</td>
<td style="text-align: right">Hourly Salary</td>
<td style="text-align: center">Employment Status</td>
</tr>
@for (int i = 0; i < dsEmployees.Tables[0].Rows.Count; i++)
{
System.Data.DataRow drProperty = dsEmployees.Tables[0].Rows[i];
if (i % 2 == 0)
{
<tr style="background-color: aliceblue; color: navy;">
<td>@drProperty[0]</td>
<td style="text-align: center">@drProperty[1]</td>
<td>@drProperty[2]</td>
<td style="text-align: right">@drProperty[3]</td>
<td style="text-align: center;">@drProperty[4]</td>
</tr>
}
else
{
<tr style="background-color: dodgerblue; color: yellow;">
<td>@drProperty[0]</td>
<td style="text-align: center">@drProperty[1]</td>
<td>@drProperty[2]</td>
<td style="text-align: right">@drProperty[3]</td>
<td style="text-align: center;">@drProperty[4]</td>
</tr>
}
}
</table>
}
}
</div>
</body>
</html>
This would produce:
If the value of a column is not very clear, to let you provide some type of explanation to the user, Transact-SQL provides a conditional statement that uses a combination of CASE and WHEN keywords. It roughly resembles the switch statement of the C# language. The formula to follow is:
CASE proposition
WHEN expression_1 THEN result_1
WHEN expression_2 THEN result_2
. . .
WHEN expression_n THEN result_n
END
You can first apply an expression to the CASE keywork to consider a value. The expression can also be the name of a column of a table. The CASE statement ends with an END keyword. The section between the CASE line and the END keyword is the body of the CASE statement.
In the body of the CASE statement, create a number of statement that start with a WHEN expression followed by a THEN expression. Each WHEN expression considers a possible value of the CASE expression. If the value is found, deal with it in a THEN expression. In the same way, create as many WHEN expression_n THEN result_n expression as you need.
Here is an example of creating and using a CASE statement:
<!DOCTYPE html>
<html>
<head>
<title>Department Store - Employees</title>
</head>
<body>
<div align="center">
<h1>Department Store - Employees</h1>
@{
using(System.Data.SqlClient.SqlConnection scDepartmentStores =
new System.Data.SqlClient.SqlConnection("Integrated Security='True';Initial Catalog='DepartmentStore';Data Source=(Local)"))
{
string strEmployees = "SELECT FirstName, MI, LastName, " +
" HourlySalary, " +
"[Status] = " +
"CASE Status " +
" WHEN 1 THEN N'Full-Time' " +
" WHEN 2 THEN N'Part-Time' " +
"END " +
"FROM Employees;";
System.Data.SqlClient.SqlCommand cmdEmployees = new System.Data.SqlClient.SqlCommand(strEmployees, scDepartmentStores);
scDepartmentStores.Open();
cmdEmployees.ExecuteNonQuery();
System.Data.SqlClient.SqlDataAdapter sdaEmployees = new System.Data.SqlClient.SqlDataAdapter(cmdEmployees);
System.Data.DataSet dsEmployees = new System.Data.DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
<table border="6">
<tr style="font-weight: 600; background-color: navy; color: lightcyan">
<td>First Name</td>
<td style="text-align: center">MI</td>
<td>Last Name</td>
<td style="text-align: right">Hourly Salary</td>
<td style="text-align: center">Employment Status</td>
</tr>
@for (int i = 0; i < dsEmployees.Tables[0].Rows.Count; i++)
{
System.Data.DataRow drProperty = dsEmployees.Tables[0].Rows[i];
if (i % 2 == 0)
{
<tr style="background-color: aliceblue; color: navy;">
<td>@drProperty[0]</td>
<td style="text-align: center">@drProperty[1]</td>
<td>@drProperty[2]</td>
<td style="text-align: right">@drProperty[3]</td>
<td style="text-align: center;">@drProperty[4]</td>
</tr>
}
else
{
<tr style="background-color: dodgerblue; color: yellow;">
<td>@drProperty[0]</td>
<td style="text-align: center">@drProperty[1]</td>
<td>@drProperty[2]</td>
<td style="text-align: right">@drProperty[3]</td>
<td style="text-align: center;">@drProperty[4]</td>
</tr>
}
}
</table>
}
}
</div>
</body>
</html>
This would produce:
If none of the WHEN expression_n expressions matches a CASE value, you can create a last statement using the ELSE keyword. The formula to follow would become:
CASE proposition
WHEN expression_1 THEN result_1
WHEN expression_2 THEN result_2
. . .
WHEN expression_n THEN result_n
ELSE else_result
END
Here is an example of using it:
SELECT FirstName, MI, LastName,
HourlySalary,
[Status] =
CASE Status
WHEN 1 THEN N'Full-Time'
WHEN 2 THEN N'Part-Time'
ELSE N'Contractor/Seasonal'
END
FROM Employees;
This would produce:
Data Selection and Web Controls
Data Selection and Labels
In the previous lesson, we saw how to display values using a SQL data reader. Text-based controls are the prime candidate for showing the value of a column of a table. A label can be used to display static text that the user cannot change.
To display a value of a SELECT statement in a label, call the Html.Labe;() method. Pass the first argument from a value of an indexer of a data reader. Here are examples:
@using (Html.BeginForm())
{
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection("Data Source=(local); Database='WaterDistribution'; Integrated Security=True))
{
string strTrafficSystem = "SELECT TicketNumber, CameraNumber, ViolationCategory, VehicleTagNumber, " +
" ServiceVehicle, DateCitationMailed, ViolationPeriod, ViolationLocation, " +
" PaymentDueDate, AmountDue " +
"FROM CitationDivision.CamerasTickets;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
<table border="6">
<tr>
<td style="text-align: center; font-weight: 600">Ticket #</td>
<td><b>Camera #</b></td>
<td><b>Violation</b></td>
<td style="text-align: center; font-weight: 600">Tag Number</td>
<td style="text-align: center; font-weight: 600">Service Vehicle?</td>
<td style="text-align: center; font-weight: 600"><b>Date Citation Mailed</td>
<td><b>Violation Date/Time</b></td>
<td><b>Location</b></td>
<td style="text-align: center; font-weight: 600">Pmt Due Date</td>
<td style="text-align: right; font-weight: 600">Amount Due</td>
</tr>
@while (sdrTrafficSystem.Read())
{
<tr>
<td>@Html.Label(@sdrTrafficSystem[0].ToString())</td>
<td>@Html.Label(@sdrTrafficSystem[1].ToString())</td>
<td>@Html.Label(@sdrTrafficSystem[2].ToString())</td>
<td style="text-align: center">@Html.Label(@sdrTrafficSystem[3].ToString())</td>
<td style="text-align: center">@Html.Label(@sdrTrafficSystem[4].ToString())</td>
<td style="text-align: center">@Html.Label(DateTime.Parse(@sdrTrafficSystem[5].ToString()).ToShortDateString())</td>
<td>@Html.Label(@sdrTrafficSystem[6].ToString())</td>
<td>@Html.Label(@sdrTrafficSystem[7].ToString())</td>
<td style="text-align: center">@Html.Label(DateTime.Parse(@sdrTrafficSystem[8].ToString()).ToShortDateString())</td>
<td style="text-align: right">@Html.Label(@sdrTrafficSystem[9].ToString())</td>
</tr>
}
</table>
}
}
Text Boxes
After getting a value from a SELECT statement, to display that value in a text box, call the Html.TextBox() method and pass the second argument as an indexed value of a data reader.
Check Boxes
A check box is used to display a true or false value. If you are using a data reader to get the value of a column that has Boolean values, parse that value before applying it to the check box.
Practical Learning: Displaying Records in Check Boxes
@{
ViewBag.Title = "Tickets Summary";
}
<h2 class="text-center">Tickets Summary</h2>
@using (Html.BeginForm())
{
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
{
string strTrafficSystem = "SELECT TicketNumber, CameraNumber, VehicleTagNumber, ViolationDate, ViolationTime, ViolationCategory, " +
" ViolationLocation, MediaTransferDate, PhotoAvailable, VideoAvailable, " +
" PaymentDueDate, AmountDue " +
"FROM CitationDivision.CamerasTickets;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
bool photoIsAvailable = false;
bool videoIsAvailable = false;
<table border="6">
<tr style="background-color: #4f0505; color: antiquewhite;">
<td style="text-align: center; font-weight: 600">Ticket #</td>
<td><b>Camera #</b></td>
<td style="text-align: center; font-weight: 600">Tag Number</td>
<td><b>Violation Date</b></td>
<td style="text-align: center; font-weight: 600">Violation Time</td>
<td style="text-align: center; font-weight: 600">Type of Violation</td>
<td><b>Location</b></td>
<td><b>Media Transfer Date/Time</b></td>
<td><b>Photo Available</b></td>
<td><b>Video Available</b></td>
<td style="text-align: center; font-weight: 600">Pmt Due Date</td>
<td style="text-align: center; font-weight: 600">Amount Due</td>
</tr>
@while (sdrTrafficSystem.Read())
{
<tr style="background-color: ivory; color: black;">
<td>@sdrTrafficSystem[0].ToString()</td>
<td>@sdrTrafficSystem[1].ToString()</td>
<td>@sdrTrafficSystem[2].ToString()</td>
<td style="text-align: center">@DateTime.Parse(sdrTrafficSystem[3].ToString()).ToShortDateString()</td>
<td style="text-align: center">@sdrTrafficSystem[4].ToString()</td>
<td>@sdrTrafficSystem[5].ToString()</td>
<td style="text-align: center">@sdrTrafficSystem[6].ToString()</td>
<td style="text-align: center">@sdrTrafficSystem[7].ToString()</td>
@if (@sdrTrafficSystem[8].ToString().Equals("True"))
{
photoIsAvailable = true;
}
else
{
photoIsAvailable = false;
}
<td style="text-align: center">@Html.CheckBox("PhotoAvailable", @photoIsAvailable)</td>
@if (@sdrTrafficSystem[9].ToString().Equals("True"))
{
videoIsAvailable = true;
}
else
{
videoIsAvailable = false;
}
<td style="text-align: center">@Html.CheckBox("VideoAvailable", videoIsAvailable)</td>
<td style="text-align: center">@DateTime.Parse(@sdrTrafficSystem[10].ToString()).ToShortDateString()</td>
<td style="text-align: center">@sdrTrafficSystem[11].ToString()</td>
</tr>
}
</table>
}
}
Combo Boxes and List Boxes
A combo box, also called a drop-down-list, is a list-based control that can show one, some, or all of the values of a column of a table. To put those values in the control, you can use a data reader to get each value and add it to the combo box.
If the column has repeating values and you use the same technique, the combo box also would have repeating values. In most cases, this would not be professional. To make sure the combo box displays unique values, you have various alternatives. You can use a loop to check whether the combo box has a certain value already and decide whether to add or ignore the value. An alternative is to apply the DISTINCT keyword to the SELECT statement.
Practical Learning: Using a Combo Box for Data Selection
using System; using System.Web.Mvc; using System.Configuration; using System.Data.SqlClient; namespace CountyPolice2.Controllers { public class TicketsProcessingController : Controller { // GET: TicketsProcessing public ActionResult Index() { return View(); } // GET: TicketsProcessing/TicketsSummary public ActionResult TicketsSummary() { return View(); } // GET: TicketsProcessing/IssueTicket public ActionResult IssueTicket() { return View(); } // GET: TicketsProcessing/SaveTicket public ActionResult SaveTicket(string CameraNumber, string VehicleTagNumber, string ViolationDate, string ViolationTime, string ViolationCategory, string ViolationLocation, string MediaTransferDate, string PhotoAvailable, string VideoAvailable, string PaymentDueDate, string AmountDue) { if (!string.IsNullOrEmpty(CameraNumber)) { int videoAvailable = 0; int photoAvailable = 0; if (bool.Parse(PhotoAvailable) == true) photoAvailable = 1; if (bool.Parse(VideoAvailable) == true) videoAvailable = 1; using (SqlConnection scTrafficSystem = new SqlConnection(ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString)) { string strNewTrafficTicket = "INSERT INTO CitationDivision.CamerasTickets(CameraNumber, VehicleTagNumber, ViolationDate, " + "ViolationTime, ViolationCategory, ViolationLocation, MediaTransferDate, PhotoAvailable, " + "VideoAvailable, PaymentDueDate, AmountDue) " + "VALUES(N'" + CameraNumber + "', N'" + VehicleTagNumber + "', N'" + DateTime.Parse(ViolationDate).ToShortDateString() + "', N'" + ViolationTime + "', N'" + ViolationCategory + "', N'" + ViolationLocation + "', N'" + MediaTransferDate + "', " + photoAvailable + ", " + videoAvailable + ", N'" + DateTime.Parse(PaymentDueDate).ToShortDateString() + "', " + double.Parse(AmountDue) + ");"; SqlCommand cmdTrafficTicket = new SqlCommand(strNewTrafficTicket, scTrafficSystem); scTrafficSystem.Open(); cmdTrafficTicket.ExecuteNonQuery(); } } return RedirectToAction("IssueTicket"); } } }
@{
ViewBag.Title = "Traffic Ticket Processing";
}
<h2 class="text-center">Traffic Ticket Processing</h2>
<hr />
@{
List<SelectListItem> ViolationCategory = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
{
string strViolationsCategoryies = "SELECT ViolationCategory " +
"FROM CitationDivision.ViolationsCategoryies;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strViolationsCategoryies, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrViolationsCategoryies = cmdTrafficSystem.ExecuteReader();
while (sdrViolationsCategoryies.Read())
{
ViolationCategory.Add(new SelectListItem { Value = @sdrViolationsCategoryies[0].ToString(), Text = @sdrViolationsCategoryies[0].ToString() });
}
}
}
@using (Html.BeginForm("SaveTicket", "TicketsProcessing", FormMethod.Post))
{
<div style="width: 550px; margin: auto;">
<table>
<tr>
<td style="width: 150px; font-weight: 600;">Camera #:</td>
<td>@Html.TextBox("CameraNumber")</td>
</tr>
<tr>
<td style="font-weight: 600;">Vehicle Tag #:</td>
<td>@Html.TextBox("VehicleTagNumber")</td>
</tr>
<tr>
<td style="font-weight: 600;">Violation Date:</td>
<td>@Html.TextBox("ViolationDate")</td>
</tr>
<tr>
<td style="font-weight: 600;">Violation Time</td>
<td>@Html.TextBox("ViolationTime")</td>
</tr>
<tr>
<td style="font-weight: 600;">Violation Type:</td>
<td>@Html.DropDownList("ViolationCategory", ViolationCategory)</td>
</tr>
<tr>
<td style="font-weight: 600;">Violation Location:</td>
<td>@Html.TextBox("ViolationLocation")</td>
</tr>
<tr>
<td style="font-weight: 600;">Date/Time Media Was Transfered:</td>
<td>@Html.TextBox("MediaTransferDate")</td>
</tr>
<tr>
<td style="font-weight: 600;">Photo is Available:</td>
<td>@Html.CheckBox("PhotoAvailable")</td>
</tr>
<tr>
<td style="font-weight: 600;">Video is Available:</td>
<td>@Html.CheckBox("VideoAvailable")</td>
<tr>
<tr>
<td style="font-weight: 600;">Payment Due Date:</td>
<td>@Html.TextBox("PaymentDueDate")</td>
</tr>
<tr>
<td style="font-weight: 600;">Amount Due:</td>
<td>@Html.TextBox("AmountDue")</td>
</tr>
</table>
</div>
<hr />
<p class="text-center"><input type="submit" name="btnIssueTicket" value="Issue Ticket" style="width: 300px" /></p>
}
Camera #: PNR-188075 Vehicle Tag #: CHW8BPG Violation Date: 04-16-2018 Violation Time: 13:46:26 Violation Category: Red Light Location: Coaster Alley Drv and Filister Rd Media Transfer Date: 04-27-2018 19:48:09 Photo Availablee: Unchecked Video Available: Checked Payment Due Date: 06-05-2018 Amount Due: 125
Radio Buttons
Radio buttons work as a mutually-exclusive group. The user can select one item at a time. As you may know already, to create a radio button, you can call the Html.RadioButton() method and pass at least two arguments. One issue is that, when creating a group of radio buttons, all of the radio buttons in the same group must use the same name, which is the first argument of their method. This means that all radio buttons in the group must be passed the same first argument. The second argument should be different for each radio button and it would hold the value of the control.
Practical Learning: Ending the Lesson
|
|
||
| Previous | Copyright © 2001-2021, FunctionX | Next |
|
|
||