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">
-
-
-
-
-
Admin Apartment Log<br />
-
<br />
-
-
-
-
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 />
-
-
-
Price:
-
<asp:TextBox ID="itemPriceTextbox" runat="server"></asp:TextBox><br />
-
<br />
-
-
-
ItemName:
-
<asp:TextBox ID="itemNameTextbox" runat="server"></asp:TextBox><br />
-
<br />
-
-
-
Type:
-
<asp:TextBox ID="typeApartmentTextbox" runat="server"></asp:TextBox><br />
-
<br />
-
-
-
Crime Rate # 1:
-
<asp:TextBox ID="crimeRate1Textbox" runat="server"></asp:TextBox><br />
-
<br />
-
-
-
Crime Rate # 2:
-
<asp:TextBox ID="crimeRate2Textbox" runat="server"></asp:TextBox><br />
-
<br />
-
-
-
-
-
<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 YourTableNameTableAdapter 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 />
-
-
-
-
-
<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>
-
<br />
-
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Go Back to Add more Data"
-
Width="205px" /><br />
-
<br />
-
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
-
SelectMethod="GetData" TypeName="DataSet1TableAdapters.EmailTableAdapter">
-
</asp:ObjectDataSource>
-
-
-
-
<br />
-
<br />
-
<br />
-
-
<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_Submitting
(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_Submitting 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...
