?
Solved

Same connection string works for Enterprise Library but not when I try just the hacked SqlConnection

Posted on 2007-10-21
26
Medium Priority
?
356 Views
Last Modified: 2008-01-09
I'm trying to figure out why I can't open the connection in the second set of code.  First, I know that I can use this just fine with our Enterprise Library 3.1:

Database db = DatabaseFactory.CreateDatabase("Mycompany.DataLayer.CC");  <-- works great and continues on

But when I try to create another method in our class or even in the same method (Same Transaction Scope), just testing some TransactionScope stuff by using straight up SqlCommand and SqlConnection, the conn.Open() fails and i don't have any compile error info

1                    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mycompany.DataLayer.CC"].ConnectionString))
2                    {
3                        string commandText = "select FirstName, LastName from FROM Student WHERE iStudentID = @iStudentID";
4    
5                        try
6                        {
7                            conn.Open();
0
Comment
Question by:dba123
  • 17
  • 9
26 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20119802
Try this:
string connectionString = ConfigurationManager.ConnectionStrings
"Mycompany.DataLayer.CC"].ConnectionString);
using (SqlConnection conn = new SqlConnection(connectionString ))
 {
     string commandText = "select FirstName, LastName from FROM Student WHERE iStudentID = @iStudentID";
     ...
}
Set a breakpoint there to see what the connection string is that is returned.

Jim
0
 
LVL 1

Author Comment

by:dba123
ID: 20119823
>>Set a breakpoint there to see what the connection string is that is returned.

did that, and it's valid.  so no reason why the Open() shouldn't work which is why I posted my issue.
0
 
LVL 1

Author Comment

by:dba123
ID: 20119825
it returns the exact same connection string as Database db = DatabaseFactory.CreateDatabase("Mycompany.DataLayer.CC");  did when I stepped through that line
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:JimBrandley
ID: 20119829
Can you post the entry from the event log? Maybe that contains a clue.

Jim
 
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20119841
BTW - Whenever we get far enough for it to matter, your select statemet has two "from" keywords.

Jim
0
 
LVL 1

Author Comment

by:dba123
ID: 20119846
crap, it was fine, recompiled the entire solution....duh.
0
 
LVL 1

Author Comment

by:dba123
ID: 20119849
actually no, it's not resolved, back to the same initial problem.
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20119860
Is there an entry in the event log?

Jim
0
 
LVL 1

Author Comment

by:dba123
ID: 20119862
in my web.config I have the connection string starting out as
connectionString="Data Source=MyDBName\SSS

but when I step through it's putting MyDBName\\SSS

don't know if the \\ is just escaping or what, maybe this is normal.
0
 
LVL 1

Author Comment

by:dba123
ID: 20119872
no, nothing in the event log.  I know it's the db string open command so not sure if that \\ is causing it or just that the open command is not working for whatever reason.
0
 
LVL 1

Author Comment

by:dba123
ID: 20119888
yea, the \\ is just an escape sequence because if I input the string rather than using the ConfigurationManager it tells me that \\ is just an escape sequence and so I am forced to put in \\...so that's not the problem.
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20119951
When you quickview, if a sting contains a backslash, it does escape that character.

In the catch block, try this:
catch (Exception ex)
{
   StringBuilder sb = new StringBuilder(1024);
   sb.Append(ex.Message);
   Exception e = ex.InnerException;
   while e != null)
   {
        sb.Append("  :  " + e.Message);
        e = e.InnerException;
   }
   string allExceptions = sb.ToString();
}

The see what shows up in the string.

Jim
0
 
LVL 1

Author Comment

by:dba123
ID: 20119967
problem is, I don't get that message at all because the connection isn't being opened.  It stops at
 Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 2009:                    }
Line 2010:                    catch (Exception ex)
Line 2011:                    {
0
 
LVL 1

Author Comment

by:dba123
ID: 20119977
I mean I wrapped this into a TransactionScope but that shouldn't matter.
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20120000
Can you please post the entire method?

Jim
0
 
LVL 1

Author Comment

by:dba123
ID: 20120038
ok forget that connection but I have another local database, and now I do with the same code but with this connection string get an error saying it can't authenticate the aspnet user account to the database.  I have IIS setup using that aspnet account but my database has access to my domain user account.  So it's passing through the aspnet account to authenticate instead.
0
 
LVL 1

Author Comment

by:dba123
ID: 20120053
as requested

        public static Student GetStudent_TestTransactionScopeID(System.Int32 iStudentID)
        {
            using(TransactionScope ts = new TransactionScope())
            {
           
                  // Query 1
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mycompany.DataLayer.CC"].ConnectionString))
                {
                    string commandText = "select vbnFirstName, vbnLastName from FROM Student WHERE iStudentID = @iStudentID";

                    try
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand(commandText, conn);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw;
                    }
                }
               
               
            // Query 2
                using (SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocalDatabaseCC"].ConnectionString))
                {
                    string commandText = "select FirstName FROM Student WHERE iStudentID = @iStudentID";

                    try
                    {
                        conn2.Open();
                        SqlCommand cmd2 = new SqlCommand(commandText, conn2);
                        cmd2.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw;
                    }
                }


            // Query 3 which works just fine
                Database db = DatabaseFactory.CreateDatabase("Mycompany.DataLayer.CC");
                using (DbCommand cmd = db.GetSqlStringCommand(@"
                            SELECT StudentID, UserName, UserName
                            FROM Student
                            WHERE iStudentID = @iStudentID
                            "))
                    try
                    {
                        db.AddInParameter(cmd, "@iStudentID", System.Data.DbType.Int32, iStudentID);

                        using (IDataReader reader = db.ExecuteReader(cmd))
                        {
                            if (reader.Read())
                            {
                                Student newStudent = new Student();
                                newStudent.LoadFromDataReader(reader);
                                return newStudent;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        if (ExceptionPolicy.HandleException(ex, "Mycompany.TU.DataLayer.BA.Exceptions"))
                        {
                            throw;
                        }
                    }

                return null;
                ts.Complete();
            }
        }

0
 
LVL 1

Author Comment

by:dba123
ID: 20120088
If you wonder why this method has 3 database calls, and 2 are with ExecuteQuery and the last is all through Application blocks it's because I just want to test the TransactionScope to see if I can execute all 3 queries without errors in the same scope.

I just need to figure out why the connection strings are failing in the first two.  The first one is the one I have been talking about and second I also have a problem with as it's tryign to pass the ASPNET user to authenticate with because I get the error here if I just take all the scripts out and just try the second sqlconnection by itself:

Exception Details: System.Data.SqlClient.SqlException: Cannot open database "MyDBName" requested by the login. The login failed.
Login failed for user 'MYPCNAME\ASPNET'.
0
 
LVL 1

Author Comment

by:dba123
ID: 20120108
Now here are my connection strings to help, from the web.config.  Again, the  Database db = DatabaseFactory.CreateDatabase("Mycompany.DataLayer.CC");
 is working fine.

  <connectionStrings>
    <add name="MyLocalDatabaseCC" connectionString="Data Source=(local);Initial Catalog=MyLocalDB;Integrated Security=True"
      providerName="System.Data.SqlClient" />
    <add name="Mycompany.DataLayer.CC" connectionString="Data Source=SERVERNAME1\LMS;Initial Catalog=CC;Persist Security Info=True;User ID=webuser;Password=f0946T66-5171-47e6-Y3e2-c9b34bI891g2"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
0
 
LVL 22

Accepted Solution

by:
JimBrandley earned 1000 total points
ID: 20120110
I see a few things in there.
1. The first select still has two "from" keywords.
2. Neither of the first two command objects has the parameter set.
3. The first select gets "vbnFirstName, vbnLastName"
    The second gets "FirstName"
    The third gets "UserName".
    Are those all valid columns in the student table?

Jim
0
 
LVL 1

Author Comment

by:dba123
ID: 20120126
my fault, they both actually just have FirstName and LastName, just I posted code that was not the latest.  For sure they are legit.  Let me check the rest but it can't even open the connection let alone eval the select.
0
 
LVL 1

Author Comment

by:dba123
ID: 20120130
thanks Jim, didn't even see the double From, that was actually it!  thanks much!!
0
 
LVL 1

Author Comment

by:dba123
ID: 20120134
sorry, just been a stressful weekend thanks for helping me in this sad state of affairs...I should have definitely noticed that.
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20120146
Here's the connection string I use at home, and it works:
"User ID=me;Password=mypw;Data Source=localhost;Initial Catalog=jrbvelss;Persist Security Info=False;Packet Size=4096"

You might try something along those lines to see if it is successful. Just hard-code something like that for now to see if we can get it at all.

Jim
0
 
LVL 1

Author Comment

by:dba123
ID: 20120148
and yes, no param set..
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20120163
Whoa! Are you sayng the bad syntax in the select prevented the connection from opening?

Jim
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question