ASP.NET / SQL Server 2005 Database interaction:loc alhost

[Dököll]

ASP.NET and SQL Server 2005 Database interaction includes:

Language: ASP.NET
Connectivity: SQL Server 2005

Foreword: This database connectivity is nothing of genius, it is a simple connection, with SQL statements and VB code. It will require some knowledge of the VS Web Dev 2005 Express platform to make more sense of this Article.

TIPS: Visit/Download free software and videos to familiarize with these VS Web Dev 2005 editions, a friend referred me to this:-)

If you have Visual Studio 2005 installed:

(1) Start a new ASP.NET Web site
(2) Give folder name of choice
(3) Accept all defaults, including adding a Master Page
(4) Say okay
(5) Add a form, call it DataCentral
(6) Add 6 textboxes and one button
(7) Add an SQL database, call it SQLDB, add it to the App_Data folder
(8) Allow Nulls for only the IP address being returned in this database
(9) Add 6 fields all together in table (read code to figure out how to name fields)
(7) Add Table by right-clicking on Tables in Database Explorer
(8) Save and Exit database design screen

 
Protected Sub Add2SQLDB_Click(ByVal sender As Object, ByVal e As System.EventArgs)
 
        Dim SQLDBDataSource  As New SqlDataSource
        SQLDBDataSource .ConnectionString = ConfigurationManager.ConnectionStrings("SQLDBConnectionString1").ToString
        SQLDBDataSource .InsertCommandType = SqlDataSourceCommandType.Text
        SQLDBDataSource .InsertCommand = "INSERT INTO LIBRARY(EmailAddress, IPAddress, DateTimeStamp, itemPrice, itemName, typeApartment, crimeRate1, crimeRate2) VALUES(@EmailAddress,@IPAddress, @DateTimeStamp, @itemPrice, @itemName, @typeApartment, @crimeRate1, @crimeRate2)"
        SQLDBDataSource .InsertParameters.Add("EmailAddress", emailAddressTextBox.Text)
        SQLDBDataSource .InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString())
        SQLDBDataSource .InsertParameters.Add("DateTimeStamp", DateTime.Now())
        SQLDBDataSource .InsertParameters.Add("itemPrice", itemPriceTextbox.Text)
        SQLDBDataSource .InsertParameters.Add("itemName", itemNameTextbox.Text)
        SQLDBDataSource .InsertParameters.Add("typeApartment", typeApartmentTextbox.Text)
        SQLDBDataSource .InsertParameters.Add("crimeRate1", crimeRate1Textbox.Text)
        SQLDBDataSource .InsertParameters.Add("crimeRate2", crimeRate2Textbox.Text)        
 
        Dim rowsAffected As Integer = 0
        Try
            rowsAffected = SQLDBDataSource.Insert()
        Catch ex As Exception
            Server.Transfer("Error_Submitting.aspx")
 
        Finally
            SQLDBDataSource = Nothing
        End Try
 
        If rowsAffected <> 1 Then           
            Server.Transfer("Error_Submitting.aspx")
        Else
            Server.Transfer("OK_Submitting.aspx")
        End If
 
 
 
    End Sub
</script>
 
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Admin Apartment Log<br />
    <br />
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp;
    Email Address:<asp:TextBox ID="emailAddressTextBox" runat="server"></asp:TextBox>
    <asp:RegularExpressionValidator ID="regExValidator" runat="server" ControlToValidate="emailAddressTextBox"
        ErrorMessage="RegularExpressionValidator" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*">*</asp:RegularExpressionValidator><br />
    <br />
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Price:
    <asp:TextBox ID="itemPriceTextbox" runat="server"></asp:TextBox><br />
    <br />
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; ItemName:
    <asp:TextBox ID="itemNameTextbox" runat="server"></asp:TextBox><br />
    <br />
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Type:
    <asp:TextBox ID="typeApartmentTextbox" runat="server"></asp:TextBox><br />
    <br />
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    Crime Rate # 1:
    <asp:TextBox ID="crimeRate1Textbox" runat="server"></asp:TextBox><br />
    <br />
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    Crime Rate # 2:
    <asp:TextBox ID="crimeRate2Textbox" runat="server"></asp:TextBox><br />
    <br />
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp;
    <asp:Button ID="Button1" runat="server" Height="26px" Text="Submit" Width="101px" OnClick="Button1_Click" /><br />
    <br />
    <asp:ValidationSummary ID="ValidationSummary1" runat="server" DisplayMode="SingleParagraph" Width="224px" />
    <br />
</asp:Content>
 
 
 

Please set validator for email field to fend off bogus emails.

See Next Page

Master Page

(1) Master page holds look and feel of additional web site pages
(2) Master page can be used to change colour or background of pages

<%@ Master Language="VB" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
 
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Master Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:contentplaceholder id="ContentPlaceHolder1" runat="server">
        </asp:contentplaceholder>
    </div>
    </form>
</body>
</html>
 

View Data Page Next...

View Data Page

(1) Right-click on path to your project
(2) Add New Item
(3) Choose Web form
(4) Call it OK_Submitting
(5) Say Yes to Master Page to it is included
(6) Right-click on path to your project again
(7) Hit Add New Item
(8) Choose Dataset
(9) Name it whatever you want

(a) Say Yes when you get a pop up
(b) Your database should now be listed in dropdown
(c) Hit All defaults, Next, Next, Next
(d) When asked to type up query, use 'Query Builder' instead
(e) Add database table being used in this project
(f) Choose all colums in Query builder view
(g) Say OK, then Finish
(h) Go back to OK_Submitting page
(i) Choose desgin view
(j) Drag from your left, under Data, a Gridview (Gridview1 as default)
(k) Bind to DataSource using Choose Data Source
(l) Choose Object in Data Source Configuration wizard pop up
(m) Bind to YourTableNameTa bleAdapter option from dropdown
(n) Hit Next, if not already selected, choose only option in dropdown
(o) Hit Next, then Finish


Now you can add code below...

<%@ Page Language="VB" MasterPageFile="~/DashMaster.master" Title="Thank You Page" %>
 
<script runat="server">
 
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
 
End Sub
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If Button1.Enabled Then
            Server.Transfer("SubmitPage.aspx")
            'Else
            'Server.Transfer("OK_Submitting.aspx")
        End If
    End Sub
</script>
 
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    Why, Thank you!<br />
    <br />
    Please take a look at your submissions<br />
    <br />
    &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
        AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" Width="231px" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
            <asp:BoundField DataField="EmailAddress" HeaderText="EmailAddress" SortExpression="EmailAddress" />
        </Columns>
    </asp:GridView>
    &nbsp;<br />
    &nbsp;&nbsp;<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Go Back to Add more Data"
        Width="205px" /><br />
    &nbsp; &nbsp; &nbsp;<br />
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
        SelectMethod="GetData" TypeName="DataSet1TableAdapters.EmailTableAdapter">
    </asp:ObjectDataSource>
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br />
    &nbsp;<br />
    <br />
    &nbsp;
    <br />
</asp:Content>
 
 

Problem submitting page next...

Problem Submitting Page

(1) Right-click on path to your project
(2) Add New Item
(3) Choose Web form
(4) Call it Error_Submittin g
(5) Say Yes to Master Page to it is included
(6) Right-click on path to your project again
(7) Drag a button on Error_Submittin g form
(8) Called Button1 as default
(9 Add code below

Code allows user to go back and fix problem...

<%@ Page Language="VB" MasterPageFile="~/DashMaster.master" Title="Problem Page" %>
 
<script runat="server">
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If Button1.Enabled Then
            Server.Transfer("SubmitPage.aspx")
        End If
    End Sub
</script>
 
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    There was a problem...<br />
    <br />
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Go back and Fix" />
</asp:Content>
 
 

For more information on this and other ASP.NET examples, please visit Microsoft's ASP.NET home page via Get Started The Official Microsoft ASP_NET Site

http://www.asp.net/get-started/

ASP.NET, The End...