ASP.NET / SQL Server 2005 Database interaction:localhost

2,364 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

  
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

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

 
<%@ 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_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

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

Jan 22 '08 #1

Subscribe Post Reply

7529

by: Asad Khan | last post by:

Hello, I was using MySQL till a few days ago just fine. Now everytime I try to run it using mysql.exe, I get the following error: "can't connect to mysql server 'localhost' (10061)" What...

MySQL Database

Connecting to SQL Server DB

by: John Spiegel | last post by:

Hi all, I'm missing something, probably stupid, on connecting to a SQL Server database from an aspx file. I haven't really done much w/ SQL Server and suspect that it's a problem on that side. ...

ASP.NET

Server Reference In SQL Server 2005 Connection String

by: Joey | last post by:

Hello all, I develop on two or three different machines at different times. I need a connection string in my app that will always reference the local instance (for whatever machine) of Microsoft...

ASP.NET

For permissions and roles, how to use sql server 2005 standard edition instead of express?

by: keithb | last post by:

I developed a web site that uses sql server express for permissions and roles. Now I need to deploy it on a server that uses sql server 2005 standard edition instead of sql server express. What do...

ASP.NET

ASPNETDB.MDF when no SQLExpress, only SQL Server 2005?

by: nick | last post by:

I have only SQL Server 2005 installed on my PC. And I tried to add the following rows in web.config to use SQL Server 2005 instead of Express: <connectionStrings> <clear /> <add...

ASP.NET

Can't connect to localhost using odbc string connections

by: 111mike | last post by:

Hello, Here's my problem. I cannot connect to mysql database using odbc string connections or dns. I keep getting a "cannot connect to mysql server localhost." I'm running windows XP Pro and...

MySQL Database

Exception when connecting to MsSQL server

by: bg_ie | last post by:

Hi, I like to develope an application which uses a MsSQL database. I believe that C# works really well with MsSQL. To start with, I'd like to host the sql server locally on my machine as I...

C# / C Sharp

Creating a database in Sun Java System Application Server 9

by: astolpho | last post by:

I am using a slightly outdated reference book on J2EE programming. It gives 2 methods of creating a database used in its casestudies. The first is an ANT script that gives the following output: ...

Java

Retrieving Date & Time from a web server (localhost)

by: teo | last post by:

I'm developing a software (a WinApp, not a WebApp) to retrieve Date & Time from a web server, but firstly I need to to test it on my PC, with "localhost" I'm using the VB Net language (below...

ASP.NET

Cloud Servers without Credit Card and Email Registration: A Simpler Way to Get on the Cloud

by: CloudSolutions | last post by:

Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...

General

Wordpress or something else?

by: Faith0G | last post by:

I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

Content Management Systems

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

ASP.NET / SQL Server 2005 Database interaction:localhost

Similar topics