Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008: Problem Design Solutio
This is the forum to discuss the Wrox book ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008: Problem Design Solution by Vincent Varallo; ISBN: 9780470396865
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 3.5 Enterprise Application Development with Visual Studio 2008: Problem Design Solutio section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 20th, 2009, 12:07 PM
Authorized User
 
Join Date: Mar 2009
Posts: 79
Thanks: 4
Thanked 4 Times in 4 Posts
Default SQL Connection Error

On the completed project, I am able to get to the dashboard.
However when I choose an item from "Request to Approve" or "My Request"

I get a sql connection error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

From this block of code:
Code:
 
[Function(Name="dbo.ENTAuditObjectSelectByObjectName")]
publicISingleResult<ENTAuditObject> ENTAuditObjectSelectByObjectName([Parameter(Name="ObjectName", DbType="VarChar(255)")] string objectName)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), objectName);
return ((ISingleResult<ENTAuditObject>)(result.ReturnValue));
}
This is looking for the object PTORequestEO but it is not present becasue the string is empty. I am not able to locate PTORequestEO where is it at in the project?

I have encountered this error before and I believe it was related to the connection string or SQL permissions.

I have already checked to see if my SQL 2005 Server had remote connections turned on in Surface, which it does.
I have also checked that NT Service has access to CRUD rights on the dbo schema.

What has me confused is that I can obviously connect to the db otherwise I would not get to the Home.aspx. Any comments, suggestions are welcomed. Below is my connection string.

Code:
 
<connectionStrings> 
<add name="HRPaidTimeOff" connectionString="Data Source=WEB01TOLLOCBRLW;Initial Catalog=HRPaidTimeOff;Integrated Security=True;Network Library=dbmssocn" providerName=""/> 
</connectionStrings>

Last edited by ZeroFactorial; March 20th, 2009 at 12:18 PM.
  #2 (permalink)  
Old March 20th, 2009, 01:30 PM
Wrox Author
 
Join Date: Jan 2009
Posts: 73
Thanks: 0
Thanked 7 Times in 7 Posts
Default

Try using a SQL Login instead of integrated security in the connection string. Make sure the SQL Login you create has read, write and execute access to the database.
  #3 (permalink)  
Old March 20th, 2009, 01:32 PM
Authorized User
 
Join Date: Mar 2009
Posts: 79
Thanks: 4
Thanked 4 Times in 4 Posts
Default

Quote:
Originally Posted by varallov View Post
Try using a SQL Login instead of integrated security in the connection string. Make sure the SQL Login you create has read, write and execute access to the database.
Thanks for the tip. What would the user and pass be?
My Active Directory information or will I need to create it on the db?
  #4 (permalink)  
Old March 20th, 2009, 01:45 PM
Wrox Author
 
Join Date: Jan 2009
Posts: 73
Thanks: 0
Thanked 7 Times in 7 Posts
Default

It can be anything you want.

1. Open SQL Server Management Studio and connect to your database server.
2. Expand the Security folder and right click on the Logins folder.
3. Select "New Login..."
4. Enter the Login Name. This can be anything you want. Just remember what it is.
5. Select the SQL Server authentication radio button and enter a password.
6. Select User Mappings from the menu on the left side of the dialog box.
7. Check the box next to your database.
8. Check the box next to db_owner for the role membership. This role will allow you to have all the permissions needed in the database.
9. Click the OK button.
10. Change your connection string to use this newly added user name and password.
  #5 (permalink)  
Old March 20th, 2009, 04:35 PM
Authorized User
 
Join Date: Mar 2009
Posts: 79
Thanks: 4
Thanked 4 Times in 4 Posts
Default

Vince,

thanks for the suggestion. However even with the SQL authentication I am getting the same SQL error on the same line of code in my original post.

I think the message is misleading.

It is looking for the ObjectName field from the ENTAuditObject table.
But this table is completely empty. So the app is unable to return a result. Should this table be populated with data? I used the .sql script from Chpt 12.

I went back and read through Chpt 12 to see if i am over looking something. The only thing I can come up with is that, some of the code expects Auditing to be turned on by default but you have to manually turn this on. For example the block of code from my first post is the stored procedure for ENTAuditObjectSelectedByObjectName.

Code:
ALTER PROCEDURE [dbo].[ENTAuditObjectSelectByObjectName]
(
    @ObjectName varchar(255)
)
AS
    SET NOCOUNT ON

    SELECT ENTAuditObjectId, ObjectName, ObjectFullyQualifiedName, InsertDate, InsertENTUserAccountId, UpdateDate, UpdateENTUserAccountId, Version
      FROM ENTAuditObject
     WHERE ObjectName = @ObjectName

    RETURN
Since the string stored is null, I think this is why the app is throwing this error. The same thing happens whenever i try to Open/Edit any existing request or Submit a new one. It bombs to the original post.

What are your thoughts?


Thanks

Last edited by ZeroFactorial; March 20th, 2009 at 04:41 PM.
  #6 (permalink)  
Old March 20th, 2009, 06:52 PM
Authorized User
 
Join Date: Mar 2009
Posts: 79
Thanks: 4
Thanked 4 Times in 4 Posts
Default

Ok figured out the problem. :)

What I did was trace through, code and I found a block of code in DBHelper.cs

Code:
namespace V2.PaidTimeOffDAL.Framework
{
    public class DBHelper
    {
        private const string HRPAIDTIMEOFF_CONNSTRING_KEY = "HRPaidTimeOff";

        public static string GetHRPaidTimeOffConnectionString()
        {
            return ConfigurationManager.ConnectionStrings[HRPAIDTIMEOFF_CONNSTRING_KEY].ConnectionString;
        }

    }
}
Where HRPAIDTIMEOFF_CONNSTRING_KEY = "HRPaidTimeOff" was "HRPaidTimeOffConnectionString"

From here I traced to V2.PaidTimeOffDAL.Properties.Settings
and discovered that the connection string stored in here is still defaulted to the Varallo1 datasource with V2Application for the user.

After making the changes to this I compiled and everything seems working as it should. No more SQL Error 40. Yay!

Any additional info on the V2.PaidTimeOffDAL.Properties.Settings file and its operation will prove helpful to me and maybe others. How do we update all of these areas at once if possible?

Thanks again
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
sql connection error navbingo20 ASP.NET 2.0 Basics 0 June 23rd, 2008 06:07 PM
connection error with SQL sh.rajkumar ASP.NET 2.0 Basics 4 June 6th, 2006 12:30 AM
connection error with SQL sh.rajkumar ASP.NET 2.0 Professional 0 June 1st, 2006 09:48 PM
SQL server connection error Mayuri25 Pro VB Databases 4 September 12th, 2005 06:42 AM
SQL Server Connection error shivani_desai SQL Server 2000 1 January 16th, 2005 03:53 AM



All times are GMT -4. The time now is 05:20 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.