467,876 Members | 1,200 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

home > topics > asp.net > insights > asp.net / sql server 2005 database interaction:localhost

Share your developer knowledge by writing an article on Bytes.

ASP.NET / SQL Server 2005 Database interaction:localhost

Dököll
Expert 2GB
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

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

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

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...
Jan 22 '08 #1
  • viewed: 6931
Share:

Sign in to post your reply or Sign up for a free account.

Similar topics
Connecting to SQL Server DB
14 posts views Thread by John Spiegel | last post: by
C loop error search
reply views Thread by jack112 | last post: by
BYTES.COM © 2021
About Us
Advertise
Terms Of Use
Privacy Policy
Manage Cookies
Contact Us
Sitemap

Follow us! Get the Latest Bytes Updates
By using this site, you agree to our Privacy Policy and Terms of Use.