Hi Guys,

I've been teaching myself, very slowly, the whole webdesign thing using VWD as my prefered tool. You'll be happy to here I can now design, build, and deploy sites, and I think VWD is a fantastic tool for doing so. My next challenge is to integrate dbs onto sites, the kind of thing where people enter data onto the db via the site, and others can access the data in a similar fashion - you know, FaceBook, Bebo, YouTube, dating sites etc. The problem is that SQL and dbs in general are my bette noir - I'm terrified of them. I've got the VWD manuals (Idiots etc), and I've done the VWD/SQL video training, but what I really need is a good, ground up, comprehensive - but idiot proof (for I am that person) project/s that will take me through designing and integrating SQL dbs into web projects. I'm pretty sure that doing so, just like designing websites, is pretty simple once you know it, but my fear of such coupled with my inability to find the perfect resource for doing so have brought me to a standstill. If anyone can point me in the direction of a project or simple guide that will lead me to my goal I'd be very gratefull.

Michael.

Per email - we will be working in VB. I'm a C# guy so this was a good chance to jump the fence to the other side.

1. Open Visual Web Developer, go to File, and on the dropdown choose "New Web Site...".

1a. It will default to some name like WebSite2, WebSite3, and you probably want to change this to something more descriptive (you may soon have several web sites).

2. In "Solution Explorer" on the right near the top, look for an icon that is a gray folder with a yellow cylinder in front of it and the words "App_Data".  Right click it.

2a. Choose "Add New Item".

3. Under Visual Studio installed templates choose "SQL Server Database".

3a. Since this is the first SQL database it will name it Database.mdf by default and yes, you want it to have the name Database.mdf (it will then be compatible with the connection string in the examples).

3b. Click the "Add" button.

3c. You should see it in the Solution Explorer window under App_Data now.

You should be ready now to do the work in the examples that follow.

This space is reserved to be an Index.  I see this thread growing to 20-30 posts (maybe even a few more).  I look forward working with Michael, and expect a few others may come here too, and for them, I'd like an index here to provide an overview on what they can find that will help them.

-Larry

 INDEX

1a. The aspx file for INSERT_SQL (TextMode="MultiLine" is needed because in most cases there will be several lines of SQL code)

1b. The aspx.vb file for INSERT_SQL (key features include: Imports, SqlConnection, SqlCommand, ExecuteNonQuery() which takes the commands in "mystring" and directions them to the table specified by the connection "conn") {notice that the connection conn is opened: conn.Open(), and closed: conn.Close() }

2. SQL is used to create a table (I use the line with id int IDENTITY with almost every table I make--it is automatic at this point; I didn't put a NOT NULL on Population because I didn't have the time to find all the answer--we can also use this later to show how SQL can add data to an existing record)

3. SQL Code is used to add records to an empty table (INSERT INTO puts new records into a table; the first part names the columns; the part after the word VALUES lists what goes into the record)

4a (aspx), 4b (aspx.vb). A dropdown menu lets you select a city for which the population has not yet been added (e.g. Instanbul) and a textbox lets you type in the number for the population (e.g. 11200000).  When you press the button, the code behind page has a subroutine that creates a SQL statement your input and runs it through an ExecuteNonQuery().  You will see ExecuteNonQuery() quite a few times in future examples.  New vocabulary: UPDATE, SET, WHERE. 

5. SQL code is provided to create a new table, Hotels and to give it several records.  An INNER JOIN is used to connect the two tables (Cities, Hotels) together.  An ON is used to make the join occur where the entry in the field "City" in table Cities is the same as the entry in the field "City" in the table "Hotels". An AND is used to combine two statements for the WHERE (you want the search to find a number between two extremes defined by variables).  The SQL statement is broken into segments with the & character between segments.  The segments are either text or variables.  Apostrophes are added to the text and placed so that they will encapsulate the number provided by the variable.  Data is taken from both tables to construct sentences.  New vocabulary: INNER JOIN, ON, AND.

 5c. The INNER JOIN was rather complicated (but introduced early because it is so helpful).  Another section provides a little more explanation about it.

After 5, a review/overview page will provide more discussion on the various new features introduced in 1 through 5.

6. TRUNCATE TABLE removes the contents of a table but the structure of the table remains.

7. ALTER TABLE, ADD and ALTER TABLE, DROP COLUMN  (7a. and 7b)

8. INNER JOINs are used to connect together 4 tables.

9. GROUP BY (with explanation of how to avoid common error message) - an upgrade to this is needed and will be coming

10. Math Functions: SUM, AVG, STDEV, ExecuteScalar()

11. ORDER BY

12. A Dropdownlist is used to change the SQL command (WHERE, ORDER BY); the commands are not new but the format is new

13. Aggregate functions (SUM, COUNT, AVG, MIN, MAX) are compared using the dropdownlist-to-change-SQL-command pages introduced in 12.

14. A stored procedure is introduced; it uses the word EXECUTE

15. a stored procedure includes two parameters and is used to  insert new records into a table.

16. FULL JOIN, LEFT JOIN, RIGHT JOIN (cousins of INNER JOIN)

17. FOREIGN KEY (a cousin to PRIMARY KEY)

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Inject_SQL.aspx.vb" Debug="true" Inherits="Inject_SQL" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Beginner SQL Injection Box</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:TextBox ID="SQL_Inject_Box" Width="700px" Height="200px" runat="server" 
            TextMode="MultiLine"></asp:TextBox><br />
        
        <asp:Button ID="SQL_Inject_Button" runat="server" Text="Inject" />
        
        <p>Warning: Do not let any other person have access to this web page! Someone else (hacker) might use this!  Keep this web page secret!  The content on this page is kept simple for purposes of helping a beginner.</p>
        
        <p>Your complimentary link to what you will want to learn: <a href="http://msdn2.microsoft.com/en-us/magazine/cc163917.aspx">Microsoft paper on SQL Injection Attacks</a></p>
    
    </div>
    </form>
</body>
</html>

Imports System.Data.SqlClient

Imports System.Data

Partial Class Inject_SQL

     Inherits System.Web.UI.Page

     Protected Sub SQL_Inject_Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SQL_Inject_Button.Click

       Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")

       Dim mystring As String = SQL_Inject_Box.Text

       Dim myCommand As New SqlCommand(mystring, conn)

       conn.Open()

       myCommand.ExecuteNonQuery()

       conn.Close()

       SQL_Inject_Box.Text = ""

     End Sub

End Class

CREATE TABLE Cities
(id int IDENTITY(1,1) PRIMARY KEY,
City nchar(20) NOT NULL,
Country nchar(20) NOT NULL,
Latitude int NOT NULL,
Population int)

INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Oslo','Norway','59','560000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Stockholm','Sweden','59','800000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Moscow','Russia','55','8300000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Berlin','Germany','52','3400000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Amsterdam','Netherlands','52','730000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('London','United Kingdom','51','7000000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Praque','Czech Republic','50','1200000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Paris','France','48','2200000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Vienna','Austria','48','1500000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Munich','Germany','48','740000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Budapest','Hungary','47','1800000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Zurich','Switzerland','47','380000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Montreal','Canada','45','1600000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Florence','Italy','43','370000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Toronto','Canada','43','2500000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Marseille','France','43','1300000')	
INSERT INTO Cities (City, Country, Latitude, Population) VALUES ('Boston','USA','42','4300000')	
INSERT INTO Cities (City, Country, Latitude) VALUES  ('Rome','Italy','41')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Instanbul','Turkey','41')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Denver','USA','39')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Athens','Greece','38')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Seoul','South Korea','37')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Algiers','Algeria','36')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Tokyo','Japan','35')	
INSERT INTO Cities (City, Country, Latitude) VALUES ('Baghdad','Iraq','33')

The aspx page below (everything below the ***) has a dropdownlist with the names of cities to which a population has not been added yet (the value in the table is NULL).  Population data is provided for the five remaining cities:

Rome: 2,700,000. Instanbul : 11,200,000. Athens : 750,000. Denver : 2,400,000. Seoul : 10,400,000.

(Note: this page has two “issues” that I want to improve upon them later—for now it will serve the purpose of introducing several things: connecting a dropdown list to the code behind page, using the selection of the dropdown list to put the name of a city in a SQL statement, using a number typed into a textbox to put a number into a SQL statement)

I hope that this is example is small enough that for just looking at it, you can see what everything is doing.  If there is any question, don't hesitate to ask.

*************

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="DropDown.aspx.vb" Inherits="DropDown" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Drop Down</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:DropDownList ID="AddPopulationDropDownList" runat="server" AutoPostBack="True"
            DataSourceID="AddPopulationSqlDataSource" DataTextField="City" 
            DataValueField="id">
        </asp:DropDownList>
        
        <asp:SqlDataSource ID="AddPopulationSqlDataSource" runat="server"  
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            SelectCommand="SELECT * FROM [Cities] WHERE [Population] IS NULL"></asp:SqlDataSource>
            
                
    
        Update population to: <asp:TextBox ID="PopulationTextBox" runat="server"></asp:TextBox>
        
        <asp:Button ID="PopulationUpdateButton" runat="server" Text="Update Population" /> <br /><br />
        
        <div id="commandline" runat="server">You need to choose a city from the dropdown menu</div>
       
    </div>
    </form>
</body>
</html>

Imports System.Data.SqlClient
Imports System.Data

Partial Class DropDown
    Inherits System.Web.UI.Page

    Protected Sub AddCity(ByVal sender As Object, ByVal e As System.EventArgs) Handles PopulationUpdateButton.Click

        Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")

        Dim thecommand As String = "UPDATE Cities SET Population='" + PopulationTextBox.Text + "' WHERE id='" + AddPopulationDropDownList.SelectedValue.ToString() + "'"
        Dim myCommand As New SqlCommand(thecommand, conn)

        commandline.InnerHtml = "<i>The SQL command executed just now was: </i>" + thecommand

        conn.Open()
        myCommand.ExecuteNonQuery()
        conn.Close()


    End Sub

End Class

Assume that after traveling to several of the cities, you decide you want to buy several hotels.  You later want to list the hotels between two values, an upper latitude and a lower latitude.  If there was one hotel in each city you would want to just add a new column to your Cities table.  However, for the large cities you have found several hotels that interest you, so you want to make new table, Hotels, that can be used to associate several hotels to a city.  Each hotel will be a separate record (a separate row) in the table.
 
 
CREATE TABLE Hotels
(id int IDENTITY(1,1) PRIMARY KEY,
City nchar(20) NOT NULL,
Hotel nchar(50) NOT NULL)

INSERT INTO Hotels (City, Hotel) VALUES ('Paris','Hotel de Crillon')
INSERT INTO Hotels (City, Hotel) VALUES ('Paris','Hotel de Vendome')
INSERT INTO Hotels (City, Hotel) VALUES ('Paris','Hotel Regina')
INSERT INTO Hotels (City, Hotel) VALUES ('Berlin','The Regent Hotel Berlin')
INSERT INTO Hotels (City, Hotel) VALUES ('Berlin','Rocco Forte Hotel de Rome')
INSERT INTO Hotels (City, Hotel) VALUES ('Berlin','Grand Hyatt Berlin')
INSERT INTO Hotels (City, Hotel) VALUES ('Moscow','Renaissance Moscow Hotel')
INSERT INTO Hotels (City, Hotel) VALUES ('Rome','Hotel Marco Polo')
INSERT INTO Hotels (City, Hotel) VALUES ('Stockholm','Birger Jarl')
INSERT INTO Hotels (City, Hotel) VALUES ('Zurich','Hotel Eden au Lac Zurich')
INSERT INTO Hotels (City, Hotel) VALUES ('Tokyo','Mandarin Oriental Tokyo')
INSERT INTO Hotels (City, Hotel) VALUES ('Algiers','Sofitel Alger')

You can get information from both tables if you use an INNER JOIN.

For an INNER JOIN, both tables will be named in the SQL statement and identified with letters a and b (a and b were arbitrary choices).  Two textboxes are used to collect an upper and a lower latitude, and the INNER JOIN will get all hotels with latitudes that are numbers between the two numbers that you selected.  For example, if you chose 38 for lower and 52 for higher then all hotels between the latitudes of 38 and 52 will be listed.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="INNER_JOIN.aspx.vb" Debug="true" Inherits="INNER_JOIN" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>INNER JOIN</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        Lower Latitude: <asp:TextBox ID="LowerLatitudeTextBox" runat="server" />
    
        <br /><br />
        
        Upper Latitude: <asp:TextBox ID="UpperLatitudeTextBox" runat="server" />
        
        <br /><br />
        
        <asp:Button ID="Button1" runat="server" Text="Done" />
        
        <br /><br />
        
        <div id="sentences" runat="server" />
        
    </div>
    </form>
</body>
</html>

Imports System.Data.SqlClient
Imports System.Data

Partial Class INNER_JOIN
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")

        Dim lowerlatitude As Double = CDbl(LowerLatitudeTextBox.Text)
        Dim upperlatitude As Double = CDbl(UpperLatitudeTextBox.Text)

        Dim sb As StringBuilder = New StringBuilder()

        conn.Open()

        Dim sqlstatement As String = "SELECT *, a.Latitude AS Latitude, a.City AS City, b.Hotel AS Hotel FROM Cities a INNER JOIN Hotels b ON a.City=b.City WHERE a.Latitude < '" & upperlatitude & "' AND a.Latitude > '" & lowerlatitude & "'"
        Dim command As SqlCommand = New SqlCommand(sqlstatement, conn)
        Dim reader As SqlDataReader = command.ExecuteReader()

        While reader.Read()
            sb.Append("The hotel <i>" & reader("Hotel") & "</i>, located near Latitude " & reader("Latitude") & ", is in the city of " & reader("City") & ".<br />")
        End While
        reader.Close()

        conn.Close()

        sentences.InnerHtml = sb.ToString()

    End Sub
End Class