Tutorials and Code

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

Syndication

My Account

Community Forums

Webmaster Tools

Tech Jobs

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

Connecting to a SQL database from ASP .NET II

This is part II 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 connect to the database using ASP .NET code and Visual Studio .NET's features.

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

By Andrew Pociu (View Profile)

(Rated 3.8 with 36 votes)

Contextual Ads

More ASP.NET Resources

This is part II of the tutorial named: Connecting to SQL from ASP .NET I

In the first part of the tutorial we dealed with the SQL database. Now we're going to make the ASP .NET web application that will establish a connection to the database we created. We're going to do it the Visual Studio way - as quick as possible with very few effort.

So fire up Visual Studio .NET and create a new ASP .NET Web Application - name it 'SQLConnection'.

Do a Ctrl+Alt+S shortcut that brings the Server Explorer window. Browse through Server > YourComputerName > SQL Servers > YourInstanceName.

NOTE

If you cannot see the instance name (in the picture on the left the instance name is WORKSTATION - the second, written all in uppercase), right click SQL Servers and choose Register SQL Server Instance. Leave the default text in the fields and a new instance will be created using your computer's name.

We are interested in the MyDB database, and if you click the [+] sign next to it you can see that it is freely browseable. It can be browsed because Visual Studio authenticates using Windows NT Integrated Security but one of the purposes of this tutorial is to connect to it using SQL Authentication (in fact, that's why we created that SQL login in the first tutorial).

We can simply connect to MyDB using different authentication: right click the database name and choose Change Login... uncheck the checkbox named 'Use Windows NT Integrated Security' and in the Login field enter the login name that we created in the first tutorial (aspnet) and the password you chose:

Everything should work just fine and you should be able to browse the database now, just as you did using Windows Integrated Authentication.

You can freely browse the database in the Server Explorer:

Server Explorer also shows us the table and the columns. What's great about this is that you can actually drag and drop the columns and even the table on the design area of the Web Application. And this is what we're going to do, as I said earlier, we're going to do this the Visual Studio way - quick and simple.

So grab the MyLinks (dbo) item and drop it on the design area. You are warned that the password is saved as clear text - ignore and choose Include password. Now a Data Adapter Configuration Error pops up that tells us it couldn't generate the UPDATE and DELETE statements. We don't mind, we only want to retrieve data from the database right now.

sqlConnection1 and sqlDataAdapter1 are created. Now we only have to code the part where we retrieve rows from the database. And thanks to Visual Studio .NET, it won't take long.

Open the code behind file (WebForm1.aspx.cs) and inside Page_Load use the following code:

// Open the connection

sqlConnection1.Open();

// Create a new DataReader and assign it to select command

System.Data.SqlClient.SqlDataReader sqlDataReader1 = sqlSelectCommand1.ExecuteReader();

// Loop through the DataReader

while(sqlDataReader1.Read())

{

   // Display the value inside the Title column

   Response.Write(sqlDataReader1["Title"] + " - ");

   // And the value inside the URL column

   Response.Write(sqlDataReader1["URL"] + "<br />");

}

Compile and see the result in Internet Explorer:

We successfully connected to a SQL database from an ASP .NET application using SQL Authentication!

Get yourself a beer or whatever you may drink and enjoy your accomplishment.

Digg It!

Del.icio.us Reddit

StumbleIt

Newsvine

Furl

BlinkList

Rate this tutorial

Current Comments

by ctlim on Monday, March 21st 2005 at 09:42 PM

Demand: Can you convert the following code into vb. Thanks.
// Open the connection

sqlConnection1.Open();

// Create a new DataReader and assign it to select command

System.Data.SqlClient.SqlDataReader sqlDataReader1 = sqlSelectCommand1.ExecuteReader();

// Loop through the DataReader

while(sqlDataReader1.Read())

{

// Display the value inside the Title column

Response.Write(sqlDataReader1["Title"] + " - ");

// And the value inside the URL column

Response.Write(sqlDataReader1["URL"] + "<br />");

}

by sdf on Monday, March 28th 2005 at 05:16 AM

asdfadsf

by Rodney on Tuesday, March 29th 2005 at 07:47 AM

Great tutorial! Thanks

by Javier on Monday, August 15th 2005 at 09:18 PM

¿como guardo datos en la base?

how save datas on the DB?

by Rajendran on Wednesday, October 5th 2005 at 06:02 AM

Simple and best, I like this site...

by abiola on Thursday, October 13th 2005 at 06:54 AM

Good job! You guys have exposed me to how to connect to msde database. God bless u all.

by -_- on Wednesday, November 9th 2005 at 04:49 AM

I follow all the steps, everything goes very fine but except the last step to view the page in IE, error msg: SQL Server does not exist or access denied.

Pls advise, many thanks!!!

:<

by :) on Wednesday, November 16th 2005 at 04:39 AM

I believe that the cause of this error is that SQL Server does not exist or access denied.

by bsrikanth on Sunday, December 11th 2005 at 02:15 AM

hwo to write a function to connect any database, any table

by kin on Tuesday, December 20th 2005 at 08:34 AM

It is a very very good tutorial. I follow all the steps but
the code behind file is a bit confusing. The extension for
my code behind file is WebForm1.aspx.vb and not WebForm1.aspx.cs. I tried to copy the code and compile
all the same but generated a lot of errors. Please help.
thanks in advance!

by Andrei Pociu on Tuesday, December 20th 2005 at 08:41 AM

You are using Visual Basic .NET, and this is a C# tutorial. You'll need to create an C# ASP.NET website instead of a Visual Basic .NET ASP.NET website for the code to work. You can choose the type of project in Visual Studio when you create a new solution.

Then the code-behind file will have the .aspx.cs extension and the code will work.

Good luck!

by kin on Tuesday, December 20th 2005 at 09:50 AM

Great job Andrei ! It works using c#. Do you know any useful tutorial that address connecting to MsSQL using
vb.net?

by Andrei Pociu on Tuesday, December 20th 2005 at 10:05 AM

The sample code at <a href="http://www.startvbdotnet.com/ado/sqlserver.aspx" target="_blank">http://www.startvbdotnet.com/ado/sqlserver.aspx</a> should do it.

by manoj dhamija on Thursday, February 9th 2006 at 11:23 PM

hey great tutorial it helps me alot

by srinivasan on Thursday, February 23rd 2006 at 06:29 AM

Db connection works fine. thanks alot frienz

by chandra on Thursday, April 20th 2006 at 09:25 AM

excellent

by r6r564 on Monday, December 18th 2006 at 09:50 AM

nice

by friend on Thursday, February 22nd 2007 at 05:10 AM

hii
This is the worst article that i have ever read
dont ever publish this again
beware bye

frnd

by haha on Thursday, February 22nd 2007 at 05:10 AM

hii
hahaha

by worstttttttttttttttt on Thursday, February 22nd 2007 at 05:11 AM

This is the worst article that i have ever read
dont ever publish this again
beware bye

by Ajay Vaid on Friday, April 27th 2007 at 05:34 AM

I Like It ,Its Really HelpFul For Me
thanks

by PREM on Thursday, May 17th 2007 at 10:04 AM

Thank you...excellent

by VB.Net on Thursday, June 14th 2007 at 02:19 PM

' Open the connection

sqlConnection1.Open()

' Create a new DataReader and assign it to select command

Dim sqlDataReader1 As System.Data.SqlClient.SqlDataReader = sqlSelectCommand1.ExecuteReader()

' Loop through the DataReader

While sqlDataReader1.Read()

' Display the value inside the Title column

Response.Write(sqlDataReader1("Title") + " - ")

' And the value inside the URL column

Response.Write(sqlDataReader1("URL") + "<br />")
End While

by Glory Ukoh on Monday, July 2nd 2007 at 06:29 AM

Thank goodness, you guys just let my cat out of its bag; I am overlly impressed. Thanks

by Razareal on Thursday, July 5th 2007 at 01:08 AM

It's very helpful for me to start my work unsing ASP.NET and SQL SERVER.

by arun on Thursday, August 16th 2007 at 03:29 AM

its very useful for my career

by saranya on Thursday, December 6th 2007 at 12:44 AM

Regarding ASP.Net i have one doubt,when i am trying to connect the webapplication with sql server database it showing the following error
Server Error in '/Sample' Application.

--------------------------------------------------------------------------------

INSERT permission denied on object 'Info', database 'master', schema 'dbo'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: INSERT permission denied on object 'Info', database 'master', schema 'dbo'.

but in windows application i can able to work
please give one solution

by Pez on Thursday, December 6th 2007 at 01:20 AM

Looks like you don't have permission over the Insert command. Check the user's credentials.

by Balaji.V.P on Sunday, January 6th 2008 at 04:52 AM

I need the query "How to connect the two and more table in the database and how can fetch the data from the connected database.
I am using SQL 2000 Server

by dcx on Friday, February 1st 2008 at 12:55 AM

dfg

by Rishh on Thursday, March 27th 2008 at 06:13 AM

I have 1 doubt in sql server.I made a website on asp.net and when I tried 2 connect to sql server it gave an error #26..how to resolve it?It says that sql server does not allow remote connections..

by suraj manbahadur thadarai on Monday, June 2nd 2008 at 08:09 AM

doesn't clear my doubts at all

by asdf on Monday, June 23rd 2008 at 02:03 AM

dffsdf

by keka on Monday, August 18th 2008 at 06:26 AM

keko keka

by surender on Tuesday, March 24th 2009 at 03:35 AM

not satisfied

by Deepak on Thursday, April 16th 2009 at 07:18 AM

Hello
I want the connectivity code withsql server in asp.net application with is property.

Plzzzzzzzz
help its really very important to me..........

by Shah Rukh Khan on Thursday, June 18th 2009 at 03:50 PM

After reading this code, I serioulsy wish that I was a programmer rather than an actor. Great minds!!!