As we saw in part 2, you need to code SQL statements to use the database in a program. Creating the SQL part of the program is usually a two step process:

Step 1: Write and test the SQL coding

Step 2: Integrate the SQL into the program

To accomplish Step 1, it's very useful to have a tool that can execute SQL statements by themselves. Fortunately, there's one integrated into VWD. To open the Query Builder, first select the Database Explorer window in VWD.

Then right-click the database and select New Query. Select the AVBQuestion table in the Add Table dialog. A new window will open like the one shown below.

--------
Click Here to display the illustration
Click the Back button on your browser to return
--------

This tool consists of four panes:

• Diagram Pane - View the tables, manage joins, and select fields
• Grid Pane - Manage individual fiels in the query
• SQL Pane - Edit the SQL code for the query directly
• Result Pane - View the result of the query.

Most SQL statements are some form of SELECT SQL statement. That's why the 'starter' SQL ...

SELECT
FROM   AVBQuestion

... is already in the SQL Pane.

But our initial function will be to add the data identifying an email message to the database. This type of SQL statement is an INSERT INTO statement. Learning about SQL is, again, a very large subject and this is a VB.NET tutorial. To short-cut the process, this is the statement that will do the job.

INSERT INTO AVBQuestion
(AVBQuestionFrom, AVBQuestionSubject, AVBQuestionMessage, AVBQuestionDate)
VALUES
('Bush@whitehouse.com', 'Why Are You Still Here!!!', 'Why?', '20Jan2009')

You can add this statement to the SQL Pane and click the Execute SQL button (the red exclamation point) to add the row to the database.

Finishing the code

To complete the program, we now need to complete Step 2: Integrate the SQL into the program. This statement makes the code simpler. Add ...

Imports System.Data.SqlClient

... to the top of the Default.aspx.vb code.

Since all the data we want to add to the database is available when a message is sent, let's add the code in the AVBSend_Click subroutine. We're also going to use a Using block here to release the resources (the SQLCommand and SqlConnection objects) as quickly as possible. The complete code is at the bottom of the article. Here are some highlights.

Remember that you can copy the connection string from the properties of the AVBQuestions database to myConnection.ConnectionString.

The SQL command that was developed in the Query Builder is built from strings. In my opinion, the easiest way is to concatenate the strings. The first part is constant:

myCommand.CommandText = "INSERT INTO AVBQuestion" & _
"(AVBQuestionFrom, AVBQuestionSubject, " & _
"AVBQuestionMessage, AVBQuestionDate)" & _
"VALUES ('"

The values sent by the web page and the system date are concatenated onto this.

myCommand.CommandText += AVBFrom.Text & "', '"
myCommand.CommandText += AVBSubject.Text & "', '"
myCommand.CommandText += AVBMessage.Text & "', '"
myCommand.CommandText += Now & "')"

To update a database, you can use the ExecuteNonQuery() method.

The complete database code is below. (The email code can be found in a previous segment of the tutorial.)

Dim myConnectionString As String = _
   "Data Source=.\SQLEXPRESS;" & _
   "AttachDbFilename=" & _
   "'C:\Users\Milovan\Documents\" & _
   "Visual Studio 2008\WebSites\" & _
   "AVBAsk\App_Data\AVBQuestions.mdf';" & _
   "Integrated Security=True;" & _
   "User Instance=True"
Using myConnection As New SqlConnection(myConnectionString)
   Dim myCommand As New SqlCommand()
   myCommand.Connection = myConnection
   myCommand.CommandText = "INSERT INTO AVBQuestion" & _
   "(AVBQuestionFrom, AVBQuestionSubject, " & _
   "AVBQuestionMessage, AVBQuestionDate)" & _
   "VALUES ('"
   myCommand.CommandText += AVBFrom.Text & "', '"
   myCommand.CommandText += AVBSubject.Text & "', '"
   myCommand.CommandText += AVBMessage.Text & "', '"
   myCommand.CommandText += Now & "')"
   myConnection.Open()
   myCommand.ExecuteNonQuery()
End Using

The code we have been using to access the database is the "old school" way of doing it because the raw SQL statements are embedded right in the program. It's good for precise control and even better for seeing exactly what's happening. But next time, we check out a more productive way to do it: databound controls.