Home

Table Field Selection

 

Selecting all Fields

From the columns of a table, you can use all fields if you want. If you are working in the table view:

  • In the Diagram section, you can click the check box of the * field
  • In the Criteria section, you can click the arrow of the first box under the Column header and select the * field

Using * to Select all Columns 

If you are creating a connection using the Data Source Configuration Wizard, to select all columns from a table, you can click the check box of the table that holds the fields. Here is an example:

Data Source Configuration Wizard

The SQL provides its own means of performing this operation.

The most fundamental keyword used by SQL is SELECT. In order to process a request, you must specify what to select. To perform data analysis, the SELECT keyword uses the following syntax:

SELECT What FROM WhatObject;
As stated already, SQL is not case-sensitive. That means SELECT, Select, and select represent the same word.

To select everything from a table, if you are working in the table view, in the SQL section, you can type * after the SELECT operator:

Data Source Configuration Wizard 

If you are writing your SQL statement, you can use the asterisk as the What factor of your SELECT statement. Here is an example:

SELECT * FROM Students;

You can also qualify the * selector. If you are working in the table view, you have various alternatives:

  • In the Criteria section, under the Column header, you can select TableName.*
  • In the Criteria section, under the Column header, you can type *. Then, under the Table header, select the name of the table
  • In the SQL section, on the right side of the SELECT keyword, type the name of the table, followed by .*

If you are writing your SQL statement, to qualify the * selector, precede * with the name of the table followed by the period operator. Here is an example:

SELECT Students.* FROM Students;

In Lesson 24, we saw that you could create an alias for a table by preceding a 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. If you are working in the Microsoft SQL Server Management Studio, in the Table window, type the desired alias string in the Alias column corresponding to the column. If you are manually writing your SQL statement, on the left side of .*, type an alias name for the table. Then, after FROM, on the right side of the table, enter the alias name again. Here is an example:

SELECT std.* FROM Students std;

After writing the expression, if you are working in the table window, you must execute the SQL statement to see its result.

Practical LearningPractical Learning: Selecting all Fields

  1. Double-click an unoccupied area of the form and implement the Load event as follows:
     
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace SolasPropertyRental6
    {
        public partial class RentalProperties : Form
        {
    	public RentalProperties()
    	{
    	    InitializeComponent();
    	}
    
    	private void RentalProperties_Load(object sender, EventArgs e)
    	{
    	    using (SqlConnection cnnProperties =
    		new SqlConnection("Data Source=(local);" +
    				  "Database='SolasPropertyRental1';" +
    				  "Integrated Security='SSPI';"))
    	    {
    		string strSelect = "SELECT * FROM RentalProperties;";
    
    		SqlCommand cmdProperties = new SqlCommand(strSelect,
    						cnnProperties);
    		SqlDataAdapter sdaProperties = 
    			new SqlDataAdapter(cmdProperties);
    		BindingSource bsProperties = new BindingSource();
    
    		DataSet dsProperties = new DataSet("PropertiesSet");
    		sdaProperties.Fill(dsProperties);
    
    		cnnProperties.Open();
    		bsProperties.DataSource = dsProperties.Tables[0];
    
    		dgvProperties.DataSource = bsProperties;
    		foreach (DataColumn col in dsProperties.Tables[0].Columns)
    		    clbColumns.Items.Add(col.ColumnName);
    	    }
    
    	    rdoShowAllFields.Checked = true;
    	}
        }
    }
  2. Execute the application to see the result
     
    Solas Property Rental

  3. Close the form and return to your programming environment

Selecting Some Fields

As opposed to selecting all fields, you can select one particular column or a few columns whose data you want to view. If you are working from the table design:

  • In the Diagram section, you can click the check box of each of the desired fields
  • In the Criteria section, under the Column header, in each subsequent row, you can select each of the desired fields

Selecting some fields

If you are creating a database connection using the Data Source Configuration Wizard, to select one or more columns from a table, first expand the table. Then click the check box of each of the columns you want to include. Here is an example where the LastName, the Sex, the City, and the State boxes are  checked:

Data Source Configuration Wizard: Selecting Some Fields 

Once again, the SQL provides its own means of selecting some columns from a table. To do this, you can replace the What factor in our formula with the name of the desired columns. To select one column, in the What factor, specify the name of that column. For example, to get the list of last names of students, you would create the following statement:

SELECT LastName FROM Students;

You can also qualify a column by preceding it with the name of the table followed by the period operator. Here is an example:

SELECT Students.LastName FROM Students;

When you execute the statement, it would display only the column that contains the last names.

To create a SELECT statement that includes more than one column, in the What factor of our syntax, enter the name of each column, separating them with a comma except for the last column. The syntax you would use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

For example, to display a list that includes the first name, the last name, the sex, 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, Sex, City, State FROM Students;

After specifying the column(s) or after including them in your SELECT statement, when you execute the SQL statement, the name of each column would appear as the column header. Here is an example:

Selecting Some Fields 

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.Sex,
       Students.City,
       Students.State
FROM Students;

You do not have to qualify all columns, you can qualify some and not qualify some others. The above statement is equivalent to:

SELECT Students.FirstName,
       LastName,
       Students.Sex,
       City,
       State
FROM Students;

Once again, remember that 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.Sex, std.City, std.State
FROM Students std;

Practical LearningPractical Learning: Selecting Data

  1. On the form, double-click the Execute button and implement its Click event as follows:
     
    private void btnExecute_Click(object sender, EventArgs e)
    {
        if (clbColumns.CheckedItems.Count < 1)
    	return;
    
        using (SqlConnection cnnProperties =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='SolasPropertyRental1';" +
    			  "Integrated Security='SSPI';"))
       {
    	string strColumns = "";
    
    	foreach (string str in clbColumns.CheckedItems)
    	    strColumns = strColumns + ", " + str;
    
    	string strResult = "";
    
    	if (rdoShowAllFields.Checked == true)
    	    strResult = "SELECT * FROM RentalProperties";
    	else
    	    strResult = "SELECT " +
    			strColumns.Substring(1) +
    			" FROM RentalProperties";
    
    	SqlCommand cmdProperties =
    		new SqlCommand(strResult, cnnProperties);
    	SqlDataAdapter sdaProperties =
    		new SqlDataAdapter(cmdProperties);
    	BindingSource bsProperties = new BindingSource();
    
    	DataSet dsProperties = new DataSet("PropertiesSet");
    	sdaProperties.Fill(dsProperties);
    
    	cnnProperties.Open();
    	bsProperties.DataSource = dsProperties.Tables[0];
    
    	dgvProperties.DataSource = bsProperties;
        }
    }
  2. Return to the form and double-click the Close button
  3. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  4. Execute the application to see the result
  5. Click a few check boxed in the bottom control
  6. Click the Execute button
     
    Solas Property Rental
  7. Close the form and return to your programming environment
 

Published on Friday 04 January 2008

 

Previous Copyright © 2007 FunctionX, Inc. Next