Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > .net > insights

ASP.NET / SQL Server 2005 Database interaction:localhost

Dököll's Avatar
Moderator
  
Join Date: Nov 2006
Location: Upstate NY - US
Posts: 2,270
#1   Jan 22 '08
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Protected Sub Add2SQLDB_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  3.  
  4.         Dim SQLDBDataSource  As New SqlDataSource
  5.         SQLDBDataSource .ConnectionString = ConfigurationManager.ConnectionStrings("SQLDBConnectionString1").ToString
  6.         SQLDBDataSource .InsertCommandType = SqlDataSourceCommandType.Text
  7.         SQLDBDataSource .InsertCommand = "INSERT INTO LIBRARY(EmailAddress, IPAddress, DateTimeStamp, itemPrice, itemName, typeApartment, crimeRate1, crimeRate2) VALUES(@EmailAddress,@IPAddress, @DateTimeStamp, @itemPrice, @itemName, @typeApartment, @crimeRate1, @crimeRate2)"
  8.         SQLDBDataSource .InsertParameters.Add("EmailAddress", emailAddressTextBox.Text)
  9.         SQLDBDataSource .InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString())
  10.         SQLDBDataSource .InsertParameters.Add("DateTimeStamp", DateTime.Now())
  11.         SQLDBDataSource .InsertParameters.Add("itemPrice", itemPriceTextbox.Text)
  12.         SQLDBDataSource .InsertParameters.Add("itemName", itemNameTextbox.Text)
  13.         SQLDBDataSource .InsertParameters.Add("typeApartment", typeApartmentTextbox.Text)
  14.         SQLDBDataSource .InsertParameters.Add("crimeRate1", crimeRate1Textbox.Text)
  15.         SQLDBDataSource .InsertParameters.Add("crimeRate2", crimeRate2Textbox.Text)        
  16.  
  17.         Dim rowsAffected As Integer = 0
  18.         Try
  19.             rowsAffected = SQLDBDataSource.Insert()
  20.         Catch ex As Exception
  21.             Server.Transfer("Error_Submitting.aspx")
  22.  
  23.         Finally
  24.             SQLDBDataSource = Nothing
  25.         End Try
  26.  
  27.         If rowsAffected <> 1 Then           
  28.             Server.Transfer("Error_Submitting.aspx")
  29.         Else
  30.             Server.Transfer("OK_Submitting.aspx")
  31.         End If
  32.  
  33.  
  34.  
  35.     End Sub
  36. </script>
  37.  
  38. <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
  39.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  40.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  41.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  42.     &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  43.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Admin Apartment Log<br />
  44.     <br />
  45.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  46.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  47.     &nbsp;
  48.     Email Address:<asp:TextBox ID="emailAddressTextBox" runat="server"></asp:TextBox>
  49.     <asp:RegularExpressionValidator ID="regExValidator" runat="server" ControlToValidate="emailAddressTextBox"
  50.         ErrorMessage="RegularExpressionValidator" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*">*</asp:RegularExpressionValidator><br />
  51.     <br />
  52.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  53.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  54.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Price:
  55.     <asp:TextBox ID="itemPriceTextbox" runat="server"></asp:TextBox><br />
  56.     <br />
  57.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  58.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  59.     &nbsp; &nbsp; &nbsp; &nbsp; ItemName:
  60.     <asp:TextBox ID="itemNameTextbox" runat="server"></asp:TextBox><br />
  61.     <br />
  62.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  63.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  64.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Type:
  65.     <asp:TextBox ID="typeApartmentTextbox" runat="server"></asp:TextBox><br />
  66.     <br />
  67.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  68.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  69.     Crime Rate # 1:
  70.     <asp:TextBox ID="crimeRate1Textbox" runat="server"></asp:TextBox><br />
  71.     <br />
  72.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  73.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  74.     Crime Rate # 2:
  75.     <asp:TextBox ID="crimeRate2Textbox" runat="server"></asp:TextBox><br />
  76.     <br />
  77.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  78.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  79.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  80.     &nbsp; &nbsp; &nbsp; &nbsp;
  81.     <asp:Button ID="Button1" runat="server" Height="26px" Text="Submit" Width="101px" OnClick="Button1_Click" /><br />
  82.     <br />
  83.     <asp:ValidationSummary ID="ValidationSummary1" runat="server" DisplayMode="SingleParagraph" Width="224px" />
  84.     <br />
  85. </asp:Content>
  86.  
  87.  
  88.  
Please set validator for email field to fend off bogus emails.

See Next Page
Last edited by Dököll; Feb 28 '08 at 04:48 AM. Reason: Updating...



Dököll's Avatar
Moderator
  
Join Date: Nov 2006
Location: Upstate NY - US
Posts: 2,270
#2   Feb 28 '08

re: ASP.NET / SQL Server 2005 Database interaction:localhost

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

Expand|Select|Wrap|Line Numbers
  1. <%@ Master Language="VB" %>
  2.  
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  4.  
  5. <script runat="server">
  6.  
  7. </script>
  8.  
  9. <html xmlns="http://www.w3.org/1999/xhtml" >
  10. <head runat="server">
  11.     <title>Master Page</title>
  12. </head>
  13. <body>
  14.     <form id="form1" runat="server">
  15.     <div>
  16.         <asp:contentplaceholder id="ContentPlaceHolder1" runat="server">
  17.         </asp:contentplaceholder>
  18.     </div>
  19.     </form>
  20. </body>
  21. </html>
  22.  
View Data Page Next...
Dököll's Avatar
Moderator
  
Join Date: Nov 2006
Location: Upstate NY - US
Posts: 2,270
#3   Feb 28 '08

re: ASP.NET / SQL Server 2005 Database interaction:localhost

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...

Expand|Select|Wrap|Line Numbers
  1. <%@ Page Language="VB" MasterPageFile="~/DashMaster.master" Title="Thank You Page" %>
  2.  
  3. <script runat="server">
  4.  
  5. Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
  6.  
  7. End Sub
  8.  
  9.     Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  10.         If Button1.Enabled Then
  11.             Server.Transfer("SubmitPage.aspx")
  12.             'Else
  13.             'Server.Transfer("OK_Submitting.aspx")
  14.         End If
  15.     End Sub
  16. </script>
  17.  
  18. <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
  19.     Why, Thank you!<br />
  20.     <br />
  21.     Please take a look at your submissions<br />
  22.     <br />
  23.     &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  24.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  25.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  26.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
  27.     <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
  28.         AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" Width="231px" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
  29.         <Columns>
  30.             <asp:CommandField ShowSelectButton="True" />
  31.             <asp:BoundField DataField="EmailAddress" HeaderText="EmailAddress" SortExpression="EmailAddress" />
  32.         </Columns>
  33.     </asp:GridView>
  34.     &nbsp;<br />
  35.     &nbsp;&nbsp;<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Go Back to Add more Data"
  36.         Width="205px" /><br />
  37.     &nbsp; &nbsp; &nbsp;<br />
  38.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
  39.         SelectMethod="GetData" TypeName="DataSet1TableAdapters.EmailTableAdapter">
  40.     </asp:ObjectDataSource>
  41.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  42.     &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;
  43.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
  44.     &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;<br />
  45.     &nbsp;<br />
  46.     <br />
  47.     &nbsp;
  48.     <br />
  49. </asp:Content>
  50.  
  51.  
Problem submitting page next...
Dököll's Avatar
Moderator
  
Join Date: Nov 2006
Location: Upstate NY - US
Posts: 2,270
#4   Feb 28 '08

re: ASP.NET / SQL Server 2005 Database interaction:localhost

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...


Expand|Select|Wrap|Line Numbers
  1. <%@ Page Language="VB" MasterPageFile="~/DashMaster.master" Title="Problem Page" %>
  2.  
  3. <script runat="server">
  4.  
  5.     Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  6.         If Button1.Enabled Then
  7.             Server.Transfer("SubmitPage.aspx")
  8.         End If
  9.     End Sub
  10. </script>
  11.  
  12. <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
  13.     There was a problem...<br />
  14.     <br />
  15.     <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Go back and Fix" />
  16. </asp:Content>
  17.  
  18.  
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...
Last edited by Dököll; Feb 28 '08 at 05:28 AM. Reason: Forgot to add link again:-)
Reply

« previous insight | next insight »

Similar .NET Framework bytes

/bytes/development

/bytes/it

/bytes/other

Forums
Visit our community forums for general discussions and latest on Bytes

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 229,155 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved