View Full Version : connect to sql server 2000 using asp.net

03-29-2007, 07:21 PM
Hey guys newbie here. I tried to google for sql connection using asp.net but didn't get what they were talking about.

I got a page called getData.aspx, and a table name "tblTest" with columns "Name" value "Test".

I want to retrieve that data from tblsTest by displaying the name "Test". But I don't know how to connect to my database which is MS. Sql server 2000. Where to put that code in vb file or that aspx file? Anyone could help please?


03-29-2007, 07:28 PM
You need to set a connection string in your web.config file:

<add name="LocalSQLServer" connectionString="Persist Security Info=False;User ID=dbUser;pwd=password;Initial Catalog=databaseName;Data Source=sqlserverip;" providerName="System.Data.SqlClient"/>

then in your page you need the
Imports System.Data.SqlClient Line at the top.
then you declare a connection where you need it in your code

and then you can declare a command and execute the query to get it in a datareader

Dim cnnConnexion As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSQLServer").ConnectionString)
Dim cnnCommand as New SqlCommand("SELECT * FROM Test", cnnConnexion)
Dim drReader as DataReader

drReader = cmdCommand.ExecuteReader()
if drReader.HasRows() then
while drReader.Read()
'code to do
end while
end if
Catch ex as exception
'Error handling
end try

hope that helps ^^ (this is VB.Net code, btw)

Oh, and you can also use the connection string directly into the declaration if you dont want to set the web.config file bit like
Dim cnnConn as new SqlConnection("Persist Security Info=False;User ID=dbUser;pwd=password;Initial Catalog=databaseName;Data Source=sqlserverip;")
but I'm not 100% sure about that one cuz I never used it

03-29-2007, 07:49 PM
Just to clarify that vb script I should put in my getData.aspx, correct? would it allow me to do that?

Also, in the web config, my SQL server database is using windows credentials, so I will just put my windows USerID and password then?

03-29-2007, 08:18 PM
to your first question: yeah the code I gave you is to be used either in a codebehind file or in the page between <% %> tags (Though I've never used that approach) In, let's say, the Page_load sub.

To your second question: Is your database on the local computer or on a remote server?

03-29-2007, 08:22 PM
Yeah it's on my local computer about the database.

03-30-2007, 05:25 PM
Hello? how can I put the username and password?

03-30-2007, 06:38 PM
Ah, sorry I didn't have time to come back here to try and help before now (and even now I kinda have a very small span of time).

I have never tried to use a local database myself. My guess would be something like

Persist Security Info=False;User ID=WindowsUser;pwd=Windowspassword;Initial Catalog=databaseName;Data Source=;

or this link might help you:

03-30-2007, 09:54 PM
Integrated Security=SSPI
might be worth looking into for you

04-04-2007, 08:14 PM
<%Imports System.Data.SqlClient %>
<%@ Page Language="VB" %>

Dim cnnConnexion As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSQLServer").ConnectionString)
Dim cnnCommand As New SqlCommand("SELECT * FROM Test", cnnConnexion)
Dim drReader As DataReader

drReader = cnnCommand.ExecuteReader()
If drReader.HasRows() Then
While drReader.Read()
'code to do
End While
End If
Catch ex As Exception
'Error handling
End Try

I got the Imports statement as Syntax error, and those classes of sql cannot are undeclared. Could anyone help?

04-04-2007, 08:52 PM

<%Imports System.Data.SqlClient %>
<%@ Import Namespace="System.Data.SqlClient" %>

04-04-2007, 09:44 PM

I know this is of no help, but I really struggled with this topic (see previous posts). Ended up being a convoluted process of trial and error to get anywhere. Have you explicitly stated that you're developing locally? If so, what are you using? Consider downloading VWDE in order to do away with a lot of superfluous code; connection strings and user profiles are automatically created; though deploying it is another matter entirely! For this, forums were of very little help.


04-05-2007, 08:13 PM
Thank you it works now. Max I don't know what are you trying to say? nikkih has been very helpful to me here ;)

04-08-2007, 02:27 PM

I'm not 'trying' to say anything, I simply wrote that I too had a similar problem which was later resolved through a process of trial and error. Forums are indeed a wonderful resource, but were of very little help to me as regards deploying a database.

04-25-2011, 10:40 PM
Thank you it works now. Max I don't know what are you trying to say? nikkih has been very helpful to me here ;)

Oh, I know what he's trying to say. Its been YEARS and I still think "dot net" is a unnecessarily convoluted disaster.

I long for the days of notepad and classic ASP....... AND YES, it was possible to develop entire SECURE web sites using only that. :thumbsup:

05-06-2011, 06:46 AM
<add key="con" value="Server=.; Trusted_Connection=True; Database=Database name"/>