• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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

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
dba123
Asked:
dba123
  • 17
  • 9
1 Solution
 
JimBrandleyCommented:
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
 
dba123Author Commented:
>>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
 
dba123Author Commented:
it returns the exact same connection string as Database db = DatabaseFactory.CreateDatabase("Mycompany.DataLayer.CC");  did when I stepped through that line
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
JimBrandleyCommented:
Can you post the entry from the event log? Maybe that contains a clue.

Jim
 
0
 
JimBrandleyCommented:
BTW - Whenever we get far enough for it to matter, your select statemet has two "from" keywords.

Jim
0
 
dba123Author Commented:
crap, it was fine, recompiled the entire solution....duh.
0
 
dba123Author Commented:
actually no, it's not resolved, back to the same initial problem.
0
 
JimBrandleyCommented:
Is there an entry in the event log?

Jim
0
 
dba123Author Commented:
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
 
dba123Author Commented:
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
 
dba123Author Commented:
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
 
JimBrandleyCommented:
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
 
dba123Author Commented:
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
 
dba123Author Commented:
I mean I wrapped this into a TransactionScope but that shouldn't matter.
0
 
JimBrandleyCommented:
Can you please post the entire method?

Jim
0
 
dba123Author Commented:
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
 
dba123Author Commented:
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
 
dba123Author Commented:
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
 
dba123Author Commented:
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
 
JimBrandleyCommented:
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
 
dba123Author Commented:
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
 
dba123Author Commented:
thanks Jim, didn't even see the double From, that was actually it!  thanks much!!
0
 
dba123Author Commented:
sorry, just been a stressful weekend thanks for helping me in this sad state of affairs...I should have definitely noticed that.
0
 
JimBrandleyCommented:
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
 
dba123Author Commented:
and yes, no param set..
0
 
JimBrandleyCommented:
Whoa! Are you sayng the bad syntax in the select prevented the connection from opening?

Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 17
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now