Tutorials
- ASP.NET
- C#
- C++
- Java
- JavaScript
- MySQL
- PHP
- Ruby
- SQL
- VB.NET
Code
How To
News
Forums
Pictures
Videos
Icons
Newsgroups

Building a C# Chat Client and Server

A step by step tutorial teaching you how to create your own chat client and chat server easily in C#, for local networks or the Internet.

in C# Programming Tutorials

Getting Hard Drive Information

A C# tutorial showing you how to make use of WMI to extract information on disk drives, such as model, capacity, sectors and serial number.

in C# Programming Tutorials

UPS Shipping Calculator

This tutorial will teach you how to calculate the shipping cost based on the weight, height, length and depth of the box, the distance and the UPS service type.

in PHP Programming Tutorials

Create Your Own Rich Text Editor

Creating a Rich Text Editor using JavaScript is easier to do than you might think, thanks to the support of modern browsers; this tutorial will walk you through it.

in JavaScript Programming Tutorials

Tutorials

Programming Tutorials

ActionScript (5)
ASP (7)
ASP.NET (28)
Assembly (1)
C (6)
C# (132)
C++ (33)
CSS (6)
General (4)
HTML (6)
Java (28)
JavaScript (38)
MySQL (2)
Objective C (1)
Pascal (1)
PHP (61)
Python (3)
Ruby (18)
Silverlight (1)
SQL (8)
VB.NET (5)
Visual Basic (3)

IT Jobs

From CareerBuilder

Geekpedia » SQL Tutorials » Connecting to a SQL database from ASP .NET I

Connecting to a SQL database from ASP .NET I

This is part I of the tutorial that teaches you how to connect to a SQL database from ASP .NET using SQL Authentication. This part shows you how to set up MSDE for Mixed Mode and how to use Web Data Administrator to create databases and users.

On Saturday, August 21st 2004 at 07:16 AM

By Andrew Pociu (View Profile)

(Rated 4 with 41 votes)

Contextual Ads

More SQL Resources

This tutorial (divided in two parts) will show you how to connect from an ASP .NET web application to a SQL database using SQL authentication. To accomplish this you need to follow a series of steps, first to change some of the settings of SQL Server 2000 and create a database and a user and then to actually connect to the database using ASP .NET with Visual Studio .NET.

I suppose you're going to use the MSDE 2000 (Microsoft SQL Server 2000 Desktop Engine) - the free version of the database.

First set up the database using the tutorial named 'Setting up MS SQL Server 2000'.

Authentication Mode

There are two possible authentication modes for MSDE: Windows Integrated and SQL Login. By default, MSDE is using Windows Integrated authentication.

But if you create some website that you are preparing to upload to a webhost, you'll probably want to prepare it for SQL Login.

Because MSDE 2000 (being free) doesn't have a user interface we need to change the authentication mode from the registry.

Navigate using regedit to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer and double-click the LoginMode key. The value that LoginMode currently holds is probably 1 which means Windows Integrated. We change this to the value 2, which means Mixed Mode:

Creating a new database

Open Microsoft SQL Web Data Administrator (if you don't have it installed read this) - if you don't start it from the Start Menu you can usually find it at http://localhost/webadmin.

First login using the Windows Integrated option, and also leave the other fields with the default values - just press the Login button.

After you are logged in, the Databases page shows up. It displays the available databases:

Click on the Create new database link. You are asked to enter a name for the new database - enter 'MyDB' (without the quotes) and click Create.

After the database is created you can see that no there are no tables to be displayed. So let's create one by using the Create new table link. When you are prompted to enter a new name for the table choose 'MyLinks' and click Create. The table is created and we are redirected to the Edit Column page where we can add a new column to the table, and we will.

In the Column Name field enter 'Title'. Leave the Data Type to char, but change the Length property to '64' (characters). Leave the rest unchanged and press Add.

The new column was created but let's create another one.

Again click on the Create new column link and set the Column Name to 'URL' and the length field to '128'. Press submit and the second column is created:

From the left side menu choose Query cause we're going to store a value.

Run the following query there:

INSERT INTO MyLinks (Title, URL) VALUES ('Geekpedia', 'http://www.geekpedia.com')

Now that you are still at the Query page, run the following query to see the inserted rows:

SELECT * FROM MyLinks

Look at the bottom of the page and you'll see the row we just inserted:

Creating a new user

Now you're going to create a new user with a password, that we'll use to connect to the database from the web application. Again, we'll accomplish using Microsoft SQL Web Data Administrator.

From the left menu choose the Security link This will bring you to the Security page where you'll encounter two icons. Choose Logins. The Logins page shows the curent accounts that are allowed to use access the database along with their privileges.

Above the table, there is a Create new login link. Click it and from the page that shows up choose Sql Login. For the login name I recommend using aspnet and for the password... well, it's your choice. After you complete the fields and click the Create Login link, the Editing login aspnet page shows up. Go to Database Access section and check the checkbox next to MyDB:

Save changes and you can now see the result - the login has been added:

There's one more thing. We need to set the privileges for this user. So on the left menu choose Databases, from the list of the databases that showed up click MyDB, of course. The left menu now has some other new items:

Choose Users and now a page shows up with a table that contains the users that have access to this database. Obviously, we are interested in aspnet, so click the Edit link next to it. The roles for this user show up with a checkbox next to them. As we currently don't want to waste time, check them all, except db_denydatareader and db_denydatawriter. Save the settings with the Save User button.

We're done.

Now you can test it. Logout (there's a small X button in the upper-right corner of the page that will log you out) and at the login page choose SQL Login instead of Windows Integrated and enter the username (aspnet) and the password you choosed earlier.

That's it, the database is set up, now we only have to connect to it from the ASP .NET web application. This is covered in part II of the tutorial:

Connecting to a SQL database from ASP .NET II

Digg It!

Del.icio.us Reddit

StumbleIt

Newsvine

Furl

BlinkList

Rate this tutorial

Current Comments

by Alc on Thursday, July 7th 2005 at 05:51 PM

excelent tutorial, makes it soft and easy, no need to complicate things, that how a tutorial should be.

Just one thing I would like to say to newcomers, after you create the new user 'aspnet' and assign the roles, then log off. First you need to restart the mssql server service in order to login as the new user.

Once again excelent tutorial congratulations!

by Sudhakr on Friday, September 16th 2005 at 03:45 AM

it very useful to me

by amit on Wednesday, January 4th 2006 at 03:34 AM

The matter of the tutorial is really excellent .Each and everything is clearly described in it.Ifound the contents of the tutorial very good and easy to understand.

by Asutosha on Wednesday, February 1st 2006 at 01:14 AM

This Graphical and clearly describe tutorial help me a lot. i hope those who read it will really appriciate it.

excelent tutorial congratulations! and thanks

Asutosha Sarangi

by suja on Thursday, April 27th 2006 at 02:51 AM

very useful, thanks a lot!

by subbu on Sunday, February 25th 2007 at 11:44 PM

worse. i never see like this. nothing was understood.simply waste one. please delete this .

by Andrei Pociu on Monday, February 26th 2007 at 06:21 AM

Please enlighten us, what made this tutorial "simply waste one"? Most people seem to find it clear enough.

by santosh shukla on Monday, September 29th 2008 at 07:20 AM

tutorial

by Ramhari on Wednesday, November 12th 2008 at 06:48 AM

lsdkjfl;kasdfl;k jl;asdf l;kjas;dlfk

by asd on Wednesday, January 14th 2009 at 12:20 PM

Use your brain !
it's waste
he try it but all in vain
80% waste material

by asd on Wednesday, January 14th 2009 at 12:20 PM

Use your brain !
it's waste
he try it but all in vain
80% waste material

by bum on Saturday, May 16th 2009 at 11:23 PM

it's definitely waste

by Harlequin on Saturday, May 16th 2009 at 11:25 PM

thanks for writing this. asd, ramhari, subbu and bum are clearly just very impatient :D

by saroj on Tuesday, July 21st 2009 at 02:00 AM

this is only basic material to create a table and its basic operations.

by bala on Monday, August 10th 2009 at 04:49 AM

it's waste material

by hanna on Tuesday, September 22nd 2009 at 05:05 AM

i dont understand this!

by roger on Monday, November 2nd 2009 at 12:05 PM

i like the registry editing . New to me. learnt something . Good tutorial. Keep them coming.

by roger on Monday, November 2nd 2009 at 12:05 PM

i like the registry editing . New to me. learnt something . Good tutorial. Keep them coming.

by roger on Monday, November 2nd 2009 at 12:05 PM

i like the registry editing . New to me. learnt something . Good tutorial. Keep them coming.

by Nomsa Tonga on Wednesday, December 9th 2009 at 07:13 AM

How do I connect ASP with SQL 2005. I want it to look the same way as your "comment on this tutorial"

by Nomsa Tonga on Wednesday, December 9th 2009 at 07:13 AM

How do I connect ASP with SQL 2005. I want it to look the same way as your "comment on this tutorial"

by Nomsa Tonga on Wednesday, December 9th 2009 at 07:13 AM

How do I connect ASP with SQL 2005. I want it to look the same way as your "comment on this tutorial"

by 3d models on Sunday, April 4th 2010 at 12:44 PM

free download all 3d models, 3d Video Tutorials, 3D Materials, 3D Textures, Vray Materials, 3D Girls, and latest 3d jobs
http://www.all3dmodel.com

by Shahriar Nour Khondokar on Monday, April 5th 2010 at 12:07 AM

Check this link: <a href="http://www.shahriarnk.com/Shahriar-N-K-Research-Embedding-SQL-in-C-Sharp-Java.html">HOW TO EMBED SQL IN JAVA

by GloriaLane31 on Friday, July 2nd 2010 at 04:43 AM

Houses are quite expensive and not everyone is able to buy it. But, <a href="http://lowest-rate-loans.com/topics/personal-loans">personal loans</a> are created to support people in such hard situations.