Connecting to SQLSERVER with ADO.net

[Arc]

I've seen several examples on this board.. all of which were different and none of which actually seemed to work..


Does anyone have a good example using ADO.net to connect to SQL Server?

Thanks!

[Cin0s3]

This is how I do it,

This goes into the web.config file:

<appSettings>
<add key="dsnNt" value="data source=***.***.***.***;initial catalog=nnerensql;UID=******;PWD=*****"/>
</appSettings>

then I add these imports to my code behind page

Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Specialized

then my query execution goes like this:

Dim conn As String
        Dim appsetting As NameValueCollection = CType(HttpContext.Current.GetConfig("appSettings"), NameValueCollection)
        conn = CStr(appsetting("dsnNT"))
        Dim myConnection2 As SqlConnection = New SqlConnection(conn)
        Dim dsLogin As SqlCommand = New SqlCommand("select something from the database", myConnection2)
        Dim ds As SqlDataReader
        myConnection2.Open()
        ds = dsLogin.ExecuteReader()
while ds.read()
'do something
end while
ds.close
myconnection2.close

hope that helps

[Edneeis]

What doesn't work about them?

The basics of the SQL connection string are the following:

NOTE: There is no provider section to the SQL connection string like in OLEDB

'Data Source'=This is either the SQL Server name if local or an IP address of the remote server, which may also need the port if not default

'Initial Catalog'=The database in the SQL Server you want to connect to


'Integrated Security=SSPI'=This uses the windows account to login to the SQL Server and can be replaced with a username (UID) and password (PWD) if not using Windows Authentication

'Persist Security Info'=I'm not really sure what this does or if it is required but I always seem to have it in my connection strings

Here is an example connection string:
Data Source=MHC2;Initial Catalog=MHC_NET;integrated security=SSPI;

'How to connect to a SQL Server
Dim cnn As New System.Data.SQLClient.SQLConnection("Data Source=MHC2;Initial Catalog=MHC_NET;integrated security=SSPI;")
cnn.Open

[Arc]

OK thanks i'll give it a shot.