Connecting to SQL Express 2005 via Web.Config file in Visual Studio 2003

Hi

I am following a project to build a small E-Commerce site from a book I have purchased and I have having problems connecting to the SQL Database with the code supplied.

The book is Apress Beginning ASP.NET 1.1 E-Commerce

http://www.amazon.co.uk/Beginning-ASP-Net-1-1-E-Commerce-Professional/dp/1590592549/ref=sr_11_1/202-7684451-7995058?ie=UTF8&qid=1193780707&sr=11-1

The code added to the Web.Config file is :

<configuration>

<appSettings>

<addkey="ConnectionString"value="Server=(local)\NetSDK;Integrated Security=True;Initial Catalog=JokePoint"/>

</appSettings>

The connection details are in a class file called Catalog.vb and is as follows

Imports System.Data.SqlClient

PublicClass Catalog

PublicSharedFunction GetDepartments()As SqlDataReader

'Create the connection object

Dim connectionAsNew SqlConnection(connectionString)

'Create and initialize the command object

Dim commandAsNew SqlCommand("GetDepartments", connection)

command.CommandType = CommandType.StoredProcedure

'Open the connection

connection.Open()

'Return a SqlDataReader to the calling function

Return command.ExecuteReader(CommandBehavior.CloseConnection)

EndFunction

PrivateSharedReadOnlyProperty connectionString()AsString

Get

Return ConfigurationSettings.AppSettings("ConnectionString")

EndGet

EndProperty

EndClass

The error is....

Cannot open database "JokePoint" requested by the login. The login failed. Login failed for user 'MachineName\ASPNET'.

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: Cannot open database "JokePoint" requested by the login. The login failed. Login failed for user 'MachineName\ASPNET'.

Source Error:

Line 15: 'Open the connectionLine 16:Line 17: connection.Open()Line 18: Line 19: 'Return a SqlDataReader to the calling function

Source File:C:\MyCommerceSite\JokePoint\BusinessObjects\Catalog.vb Line:17

Stack Trace:

[SqlException: Cannot open database "JokePoint" requested by the login. The login failed.Login failed for user 'MachineName\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() JokePoint.Catalog.GetDepartments() in C:\MyCommerceSite\JokePoint\BusinessObjects\Catalog.vb:17 JokePoint.DepartmentsList.Page_Load(Object sender, EventArgs e) in C:\MyCommerceSite\JokePoint\UserControls\DepartmentsList.ascx.vb:44 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Control.LoadRecursive() System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()

The connection tests ok in Visual Studio 2003. All permissions are set using SQL Management Studio Express 2005.

The book is using SQL 2000, I have been trying different connection syntax's in the Web.Config file all day and now I have a big headache. I know its something simple, can anyone please advise ?

Thanks in advance.

Mark

cracken:

<addkey="ConnectionString"value="Server=(local)\NetSDK;Integrated Security=True;Initial Catalog=JokePoint"/>

First, you are trying to connect using windows authentication and not with a SQL Server user credentials. I think the Integrated Security part is causing you the error. Have you tried to connect using SQL Server user credentials ( if any ), or you are using the windows authentication only.

Hope this will help.

|||

Try this...

web.config file:

<connectionStrings>

<addname="ConnectionString";Data Source=(local)\NetSDK;Integrated Security=True;Initial Catalog=JokePoint";

providerName="System.Data.SqlClient" />

</connectionStrings>

after that in the vb page:

Dim sepAsNew SqlConnection("connectionstring")

sep.Open()

Dim commandAsNew SqlCommand("GetDepartments", sep)

command.CommandType = CommandType.StoredProcedure

|||

Hi

Thanks for answering so quick, I am using Windows Integrated Secuity at the moment.

I have tried the suggestion above, but they were not sucessful. What I have done is changed the provier to OleDb and added a bit to the connection string.

I do not seen to get any code errors now, just login errors.

The VB file now looks like:

Imports System.Data

Imports System.Data.OleDb.OleDbConnection

Public Class Catalog

Public Shared Function GetDepartments() As OleDb.OleDbDataReader

'Create the connection object

Dim connection As New OleDb.OleDbConnection(connectionString)

'Create and initialize the command object

Dim command As New OleDb.OleDbCommand("GetDepartments", connection)

command.CommandType = CommandType.StoredProcedure

'Open the connection

connection.Open()

'Return a OleDbDataReader to the calling function

Return command.ExecuteReader(CommandBehavior.CloseConnection)

End Function

Private Shared ReadOnly Property connectionString() As String

Get

Return ConfigurationSettings.AppSettings("ConnectionString")

End Get

End Property

End Class

And the Web.Config connection string :

<configuration>

<appSettings>

<addkey="connectionString"value="Server=(local)\NetSDK; Provider=SQLOLEDB; Integrated Security=SSPI; Initial Catalog=JokePoint"/>

</appSettings>

And the error message :

Cannot open database "JokePoint" requested by the login. The login failed.

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.OleDb.OleDbException: Cannot open database "JokePoint" requested by the login. The login failed.

Source Error:

Line 16: 'Open the connectionLine 17:Line 18: connection.Open()Line 19: Line 20: 'Return a OleDbDataReader to the calling function

All access accounts and read / write permission are correct.

Any more advise would be much app.

Regards

Mark