GridView RowType

Different types of rows exist in the GidView.

"GridViewRow.RowType Property (System.Web.UI.WebControls)" ( http://bit.ly/h4fyeP )

Here's a kitteh to relax you...

DataRowView Class

"DataRowView Class (System.Data)" ( http://bit.ly/dLuGGk )

Represents a customized view of a DataRow.

Here's a kitteh to relax you...

 Back to the story!

For instance, in the RowDataBound event of a GridView, where a single row/record of data is bound,  we can create a view of the data on the row by declaring a DataRowView object & assigning the DataItem property of the row to that object. From there, we are free to pick out the items on the row that are of interest to us.

' Get a view of the data bound to the current row.

Dim drvCurrentDataRow As DataRowView = e.Row.DataItem

' Get the price field from the row.

 Dim decPrice As Decimal = drvCurrentDataRow("Price")

 ' Get the quantity field from the row.

 Dim intQuantity As Integer = drvCurrentDataRow("Quantity")

SQL INNER JOIN Keyword

The INNER JOIN keyword return rows when there is at least one match in both tables.

"SQL INNER JOIN Keyword" ( http://bit.ly/gAwOax )

Loop through a DataSet

        Dim dr As DataRow
        Dim dt As DataTable
        dt = DataSetName.Tables(0)
        For Each dr In dt.Rows
           something = dr("FeildName")
        Next

Return ID from row insert - SCOPE IDENTITY

"INSERT INTO TableName(FeildName, FeildName) VALUES (Value, Value); SELECT IDColumnName FROM TableName WHERE (IDColumnName  = SCOPE_IDENTITY())"

intID = CommandObject.ExecuteScalar

http://msdn.microsoft.com/en-us/library/ms190315.aspx

Inserting into MS SQL Date data type

Literal string YYYY-MM-DD

"date (Transact-SQL)" ( http://bit.ly/gDqI2q )

Parts of an ASP.NET Web Application

Web Forms or .aspx pages
Web Forms and .aspx pages provide the user interface for the Web application.

Code-behind pages
Code-behind pages are associated with Web Forms and contain the server-side code for the Web Form.

Configuration Files
Configuration files are XML files that define the default settings for the Web application and the Web server. Every Web application has one Web.config configuration file.

Global.asax File
Global.asax files contain the needed code for responding to application-level events that are raised by ASP.NET.

XML Web service links
XML Web srevice links allow the Web application to send and recieve data from an XML Web service.

Database connectivity
Database connectivity allows the Web application to transfer data to and from database sources.

Caching
Caching allow the Web application to return Web Forms and data more quickly after the first request.

ACCESS Database & the Bin/Debug folder

Your connection string should look like this to link your app with a DB that is placed within the Bin/Debug folder: (Access example)

Private Const _strCONNECTIONSTRING As String = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
        "Data Source=DatabaseName.accdb"

DELETE

The delete operation is used to delete records from a table. A filter must be specified for the operation to take place (or you may end up deleting everything!) 

 

DELETE FROM Customers WHERE State = @State
Deletes all the customer records matching the parameter of State..

Note the following code sample:

Public Sub deleteFromCart(CartID As Integer)

        Dim myConn As SqlConnection = New SqlConnection(MyConnString)
        myConn.Open()
        Dim strSQL As String = "DELETE FROM XYZCart WHERE CartID = @CartID"
        Dim myComm As SqlCommand = New SqlCommand(strSQL, myConn)
        myComm.Parameters.AddWithValue("@CartID", CartID)
        myComm.ExecuteNonQuery()
        myConn.Close()

End Sub

Once again, parameters are used to pass the criteria used to delete the records and the ExecuteNonQuery method of the command object is engaged to complete the operation.

UPDATE

The update operation allows us to make changes to existing fields.

Using a filter - WHERE
By using a filter (WHERE), we can specify which fields of which records to update. Once again, there is no data retrieved from the database as part of the operation. For this reason, we do not need to use a data adapter or dataset.

UPDATE Customers SET FirstName=@FirstName,LastName=@LastName WHERE CustomerID=@CustomerID

Updates the specified fields of the a customer record matching the parameter of CustomerID.

BE CAREFUL
If you omit the filter, you will update all records, as below:

UPDATE Customers SET FirstName=@FirstName,LastName=@LastName

Updates all of the specified fields of all of the customer records.

Note the following code sample:

Public Sub updateCartQuantityPlusOne(CartID As Integer)

    Dim myConn As SqlConnection = New SqlConnection(MyConnString)
    myConn.Open()
    Dim strSQL As String = "UPDATE XYZCart SET Quantity = Quantity + 1 WHERE CartID = @CartID"
    Dim myComm As SqlCommand = New SqlCommand(strSQL, myConn)
    myComm.Parameters.AddWithValue("@CartID", CartID)
    myComm.ExecuteNonQuery()
    myConn.Close()


End Sub

Once again, parameters are used to pass the data to update the record and the ExecuteNonQuery method of the command object is engaged to complete the operation.

INSERT

The insert statement modifies the contents of the database in that we use it to create new records. By default, there is no data retrieved from the database as part of the operation. For this reason, we do not need to use a data adapter or dataset.

The INSERT SQL Statement

INSERT INTO TableName (FieldName1, FieldName2) VALUES (@FieldName1, @FieldName2)

Inserts data into the specified fields, creating a new record. Note that parameters are used to pass the values that need to be inserted into the fields.

Note the following code sample:

Public Sub insertIntoCart(ProductID As Integer, Quantity As Integer)
        
    Dim myConn As SqlConnection = New SqlConnection(MyConnString)
    myConn.Open()
    Dim strSQL As String = "INSERT INTO XYZCart (ProductID, Quantity, CustomerID) VALUES (@ProductID, @Quantity, @CustomerID)"
    Dim myComm As SqlCommand = New SqlCommand(strSQL, myConn)
    myComm.Parameters.AddWithValue("@ProductID", ProductID)
    myComm.Parameters.AddWithValue("@Quantity", ProductID)
    myComm.Parameters.AddWithValue("@CustomerID", 0)
    myComm.ExecuteNonQuery()
    myConn.Close()


End Sub

Parameters are used to pass the data for insertion. Note that they are passed to the AddWithValue method in the order they are specified in the SQL statement.

Also note, the ExecuteNonQuery method of the command object is engaged to complete the operation. This is common to the insert, update & delete operations.

Parameters (place-holder values)

SQL statements may contain ‘hard-coded’ values such as the following:

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = ‘1’ AND FieldName1 = ‘X’

..or they may contain a parameter(s) that is used as a place-holder value such as in the following:

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = @ColumnID AND FieldName1 = @FieldName

Using a parameter allows the SQL statement to work dynamically - that is, to take a current value and use it in the query. A parameter is preceded by the ‘@’ symbol.

When we define a parameter, we must provide further explanation as to where the real value will come from, at run time.

Note the following code sample:

Dim strConnectionString As String = "Provider=Microsoft.Ace.OLEDB.12.0;" & _

"Data Source=C:\FolderName\DatabaseName.accdb"

Dim MyConnection As New OleDb.OleDbConnection(strConnectionString)

MyConnection.Open()

Dim MyCommand As New OleDb.OleDbCommand("SELECT * FROM Customers WHERE CustomerID = @CustomerID AND LastName = @LastName", MyConnection)

MyCommand.Parameters.AddWithValue(“@CustomerID”, lblCustomerID.Text)

MyCommand.Parameters.AddWithValue(“@LastName”, lblLastName.Text)

Dim MyDataset As New Data.DataSet

Dim MyDataAdapter As New OleDb.OleDbDataAdapter(MyCommand)

MyDataAdapter.Fill(MyDataset, "MyQueryResults")

MyConnection.Close()

This select command includes two parameters - @CustomerID and @LastName. The next two statements inform the command object of where to find the actual values to use. The @CustomerID parameter’s value can be found in the text property of the label called lblCustomerID and the @LastName parameter value can be found in the text property of the label called lblLastName.

Notes on using parameters:
    •    Parameter names are preceded by the ‘@’ symbol
    •    You may name a parameter anything you like, however convention is to name the parameter strictly after the field name it represents.
    •    Each parameter must be passed to the command object’s Parameters.AddWithValue method
    •    Parameters must be passed to the command object’s Parameters.AddWithValue method in the same order as they are defined in the SQL statement.
    •    Spelling and case must match

SELECT

A select operation enables records to be retrieved from a database. When records are retrieved, they are brought back to the application and placed into memory. Because of this, a select operation contains objects that also allow these records to be stored.

It is important to note that the select operation does not modify the contents of the database, it simply retrieves data.

Variations on the SELECT SQL statement:

SELECT * FROM TableName

Returns all fields from all records

SELECT * FROM TableName WHERE ColumnID = ‘1’

Return all fields of the record that has a ColumnID of 1

SELECT FieldName1, FieldName2 FROM TableName

Returns the fields specified, from all records

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = ‘1’

Returns the fields specified, from the record that has a ColumnID of 1

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = ‘1’ AND FieldName1 = ‘X’

Returns the fields specified, from the record that has a ColumnID of 1 and FieldName1 has a value of X.

Another example which substitutes literal values for parameter values:

Public Function getCart() As Data.DataTable

    Dim myConn As SqlConnection = New SqlConnection(MyConnString)

    myConn.Open()

    Dim strSQL As String = "SELECT * FROM XYZCart"

    Dim myComm As SqlCommand = New SqlCommand(strSQL, myConn)

    Dim dsResults As New Data.DataSet

    Dim daDataAdapter As New     Data.SqlClient.SqlDataAdapter(myComm)

    daDataAdapter.Fill(dsResults, "CartResults")

    myConn.Close()

    Return dsResults.Tables("CartResults")

End Function

At the conclusion of the operation, any retrieved results are stored in the dataset.

Datasets

The dataset is the area in memory that is used to store returned results from database queries. One dataset may contain many sets of results. For this reason, each set of query results must be stored with a unique name.

The data adapter is in charge of placing the returned results into the dataset.

MyDataAdapter.Fill(DataSetName, “QueryResultsName”)

You will notice that when the Fill method of the data adapter is called, two parameters are passed to the method:

1. The name of the dataset to fill.

2. The name that the results will be stored under.

Using parameters with queries and reports

"Using parameters with queries and reports - Access - Microsoft Office" ( http://bit.ly/cGEVmI )

Searching a Memo field for a word - Access

Criteria as follows:

Like "*keyword*"

Access Query and Filter Criteria

From http://www.fontstuff.com/access/acctut06.htm

"When constructing a query or a filter, you need to tell Access what to look for in each field. You do this by defining criteria - typing something (an "expression") into the Criteria cell of the query or filter grid. If you do not define any criteria for a particular field, Access assumes that you are applying no constraints and will display everything it has. This means that you only have to define criteria for those fields you are interested in..."

This tutorial is arranged in the following sections:

• Matching Text
• Using Wildcards
• Working with Numbers
• Working with Dates
• Excluding Things
• Finding Empty Fields

Video Tutorial: Access Queries

Fantastic video tutorial showing query creation with Access.

http://www.gcflearnfree.org/computer/lesson.aspx?id=1552&p=2

Tutorial: Create a simple database in MS Access 2007

Tutorial >>>

TUTORIAL: CREATE QUERIES IN ACCESS Using the Query Wizard

Queries allow you to pull records and fields from one or more tables based on the
conditions you define. Tutorial >>>