Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

Partial Class _Default
    Inherits System.Web.UI.Page

    Dim MyConnection As SqlConnection

    Sub Page_Load(ByVal Src As Object, ByVal e As EventArgs)
        ' Create a connection to the "pubs" SQL database located on 
        ' the local computer.
        myConnection = New SqlConnection("server=localhost;" _
           & "database=pubs;Trusted_Connection=Yes")
        ' Check whether this page is a  postback. If it is not 
        ' a  postback, call a custom BindGrid function.
        If Not IsPostBack Then
            BindGrid()
        End If
    End Sub

    ' Implement an AddAuthor_Click function. This function does some data 
    ' validation on the input form and builds a parameterized command containing 
    ' all the fields of the input form.  Then it executes this command to the 
    ' database and tests (using the try command) whether the data was added. 
    ' Finally, it rebinds the DataGrid to show the new data.
    Sub signUpButton_Click(ByVal Sender As Object, ByVal e As EventArgs)
        Dim myCommand As SqlCommand
        Dim insertCmd As String
        ' Check that four of the input values are not empty. If any of them
        '  is empty, show a message to the user and rebind the DataGrid.
        If (txtfname.Text = "" Or txtlname.Text = "" Or username.Text = "" _
           Or txtphone.Text = "" Or txtstate.Text = "" Or txtzip.Text = "" Or txtpassword.text = "" Or txtcity.Text = "" Or txtemail.Text = "" Or txtaddress.Text = "") Then
            Message.InnerHtml = "ERROR: Information Incomplete."
            Message.Style("color") = "red"
            BindGrid()
            Exit Sub
        End If
        ' Build a SQL INSERT statement string for all the input-form
        ' field values.
        insertCmd = "insert into Members values (@DateJoined, @User, @Password, @LName, @FName," _
           & "@Phone, @Address, @City, @State, @Zip, @Email);"
        ' Initialize the SqlCommand with the new SQL string.
        myCommand = New SqlCommand(insertCmd, MyConnection)
        ' Create new parameters for the SqlCommand object and
        ' initialize them to the input-form field values.
        myCommand.Parameters.Add(New SqlParameter("@DateJoined", _
           SqlDbType.VarChar, 11))
        myCommand.Parameters("@DateJoined").Value = TimeLabel.Text
        myCommand.Parameters.Add(New SqlParameter("@User", _
           SqlDbType.VarChar, 11))
        myCommand.Parameters("@User").Value = username.Text
        myCommand.Parameters.Add(New SqlParameter("@Password", _
           SqlDbType.VarChar, 11))
        myCommand.Parameters("@Password").Value = txtpassword.Text
        myCommand.Parameters.Add(New SqlParameter("@LName", _
           SqlDbType.VarChar, 40))
        myCommand.Parameters("@LName").Value = txtlname.Text
        myCommand.Parameters.Add(New SqlParameter("@FName", _
           SqlDbType.VarChar, 20))
        myCommand.Parameters("@FName").Value = txtfname.Text
        myCommand.Parameters.Add(New SqlParameter("@Phone", _
           SqlDbType.Char, 12))
        myCommand.Parameters("@Phone").Value = txtphone.Text
        myCommand.Parameters.Add(New SqlParameter("@Address", _
           SqlDbType.VarChar, 40))
        myCommand.Parameters("@Address").Value = txtaddress.Text
        myCommand.Parameters.Add(New SqlParameter("@City", _
           SqlDbType.VarChar, 20))
        myCommand.Parameters("@City").Value = txtcity.Text
        myCommand.Parameters.Add(New SqlParameter("@State", _
           SqlDbType.Char, 2))
        myCommand.Parameters("@State").Value = txtstate.Text
        myCommand.Parameters.Add(New SqlParameter("@Zip", _
           SqlDbType.Char, 5))
        myCommand.Parameters("@Zip").Value = txtzip.Text
        myCommand.Parameters.Add(New SqlParameter("@Email", _
           SqlDbType.VarChar, 1))
        myCommand.Parameters("@Email").Value = txtemail.Text
        myCommand.Connection.Open()
        ' Test whether the new row can be added and  display the 
        ' appropriate message box to the user.
        Try
            myCommand.ExecuteNonQuery()
            Message.InnerHtml = "<b>Record Added</b><br>" & insertCmd
        Catch ex As SqlException
            If ex.Number = 2627 Then
                Message.InnerHtml = "ERROR: A record already exists with " _
                   & "the same primary key"
            Else
                Message.InnerHtml = "ERROR: Could not add record, please " _
                   & "ensure the fields are correctly filled out"
                Message.Style("color") = "red"
            End If
        End Try

        myCommand.Connection.Close()
        BindGrid()
    End Sub

    ' BindGrid connects to the database and implements a SQL 
    ' SELECT query to get all the data in the "Authors" table 
    ' of the database.
    Sub BindGrid()
        Dim myConnection As SqlConnection
        Dim myCommand As SqlDataAdapter
        ' Create a connection to the "pubs" SQL database located on 
        ' the local computer.
        myConnection = New SqlConnection("server=localhost;" _
           & "database=pubs;Trusted_Connection=Yes")
        ' Connect to the SQL database using a SQL SELECT query to get all 
        ' the data from the "Authors" table.
        myCommand = New SqlDataAdapter("SELECT * FROM Members", _
           myConnection)
        ' Create and fill a new DataSet.
        Dim ds As DataSet = New DataSet()
        myCommand.Fill(ds)
        ' Bind the DataGrid control to the DataSet.
        MyDataGrid.DataSource = ds
        MyDataGrid.DataBind()
    End Sub

End Class

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

<!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>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
   
    <asp:label runat="server" id="TimeLabel" Visible="false" />
    <ASP:DataGrid id="MyDataGrid" runat="server"
            Width="700"
            BackColor="#ccccff" 
            BorderColor="black"
            ShowFooter="false" 
            CellPadding="3" 
            CellSpacing="0"
            Font-Name="Verdana"
            Font-Size="8pt"
            HeaderStyle-BackColor="#aaaadd"
            EnableViewState="false"
          />

    <table>
    <tr><td align="right">Create a Username:</td><td><asp:TextBox ID="username" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">Choose a Password</td><td><asp:TextBox ID="txtpassword" TextMode="Password" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">First Name:</td><td><asp:TextBox ID="txtfname" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">Last Name:</td><td><asp:TextBox ID="txtlname" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">Address:</td><td><asp:TextBox ID="txtaddress" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">City:</td><td><asp:TextBox ID="txtcity" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">State:</td><td><asp:TextBox ID="txtstate" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">Zip:</td><td><asp:TextBox ID="txtzip" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">Phone:</td><td><asp:TextBox ID="txtphone" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">Email:</td><td><asp:TextBox ID="txtemail" runat="server"></asp:TextBox></td></tr>
    <tr><td align="right">
        <asp:Button ID="signUpButton" runat="server" Text="Sign Up" /></td><td><span id="Message" EnableViewState="false" 
                  style="font: arial 11pt;" runat="server"/></td></tr>
    </table>
    </form>
</body>
</html>