Connecting to SQL Server Exrpress - using web.config

Hello,

So I am just getting started with .NET,

I am using Visual Web Developer 2005 Express and

SQL Server 2005 Express on XP Media Center (same as XP Pro)

ASP.NET version 2.0.??

Anyways, I am having the time of my life figuring out how to connect to SQL Server from within my C#, as follows,

(wheresettings.connection = ".\SQLEXPRESS;Database=MyDatabase;Integrated Security=SSPI;" in my web.config)

SqlConnection conn = new SqlConnection(Settings.Connection);
SqlDataReader rdr = null;

try
{
conn.Open();
SqlCommand cmd = new SqlCommand("select * from mytable", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}

the above yields unspeakable errors, namely

<font color="red"><b>Cannot open database requested by login. login failed for user 'computername/SQLEXPRESS'</b></font> , etc

AND

<font color="red"><b>A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)</b></font>

If you have ANY information on the suspect, please, please report it to me. Greatly appreciated!

Thanks, Cam

I suggest that you investigate what the connection string should be like:

1) Create an empty text file in windows explorer and rename it to X.UDL
2) Double click on it and the datalink provider dialog will appear.
3) Select the provider tab. Find the provider for your data access method and click next.
4) Select your source
5) Test the connection and save it.
6) Compare the contents of X.UDL with your connections string.

You can also look on http://www.connectionstrings.com/ for examples of connection strings

Connecting to SQL Server 2005 using vb codes.

The data resides in app_data.

In my webconfig file I have this.

<addname="vau"connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|vau.mdf"providerName="System.Data.SqlClient"/>

I understand that there are several different ways to connect and manage information to the database. I think I want to use the behind the codes (.aspx.vb) and on the top of the page I have this.Imports System.Data.

That is where I am stuck. Can you push me in the direction by either explaing to me or post a link that has more information about this.

Thanks.

1st, add a reference to System.Configuration (Assuming VS.Net 2005)

VB.Net code:

Dim cnAs New SqlConnection(ConfigurationManager.ConnectionStrings("vau").ConnectionString)Try cn.Open()Dim cmdAs New SqlCommand() cmd.CommandText ="Select * from SomeTable" cmd.Connection = cnDim rdrAs SqlDataReader = cmd.ExecuteReaderWhile rdr.Read'Do stuffEnd While Catch exAs Exception'Handle errors hereFinally If cn.State = ConnectionState.OpenThen cn.Close()End Try

|||

Whoops, you need another Imports statement:

Imports System.Data.SqlClient

|||

That worked well for select statement but I will also use insert as well as update sql statement. Can you give me examples for those as well. Thanks a ton.

|||

Dim cnAs New SqlConnection(ConfigurationManager.ConnectionStrings("vau").ConnectionString)Try cn.Open()Dim cmdAs New SqlCommand() cmd.CommandText ="Update SomeTable Set SomeField=SomeValue"'Alternatively cmd.CommandText = "Insert Into SomeTable (fields) values (values)" cmd.Connection = cnDim rowsUpdatedAs Integer = cmd.ExecuteNonQuery()Catch exAs Exception'Handle errors hereFinally If cn.State = ConnectionState.OpenThen cn.Close()End Try