SQLCommand Fails When Connection Parameter is Not Set

Question

I am having an interesting time using the Statement below that reads like this

SqlCommand RiskRevalCommand = 
    new SqlCommand("select * from CreditAdmin.dbo.CreditData_Test");

I took the SQL statement straight from a query in SQL Server Management Studio, so I know it works in there, but now it causes an exception to be thrown when the program attempts to execute this line:

SqlDataReader reader = RiskRevalCommand.ExecuteReader();

and the error reads:

ExecuteReader: Connection property has not been initialized.

SqlConnection xavierConnection = 
    new SqlConnection("user id=FB\\user;"  +
        "password=password;" +
        "server=dataserver;" +
        "Trusted_Connection=yes;" +
        "database=CreditAdmin;" +
        "connection timeout=15");

try
{
    xavierConnection.Open();
}
catch (Exception e)
{
    MessageBox.Show(e.Message);
}

try
{
    SqlCommand RiskRevalCommand = new SqlCommand("select * from CreditAdmin.dbo.CreditData_Test");

SqlDataReader reader = RiskRevalCommand.ExecuteReader();

while (reader.Read())
{
    try
    {
        double.TryParse(reader["Available Balance"].ToString(), out _availability);
        ...
    }
}
catch (Exception e)
{
    MessageBox.Show(e.Message);
}

//close the connection
try
{
    xavierConnection.Close();
}
catch (Exception e)
{
    MessageBox.Show(e.Message);
}

What should I change about my SQL statement so that It doesn't blow up, and so that I can still do the TryParsing for the fields?

Also how the heck does this break when this works?

(It's what was in the place of the select * I'm using now)

What you really need to do is put your SqlConnection, SqlCommand, and SqlDataReader into `using` blocks. You also need to remove your try/catch blocks. Maybe have one at the top level to ensure that your MessageBox.Show appears. And in that, you should display `ex.ToString()` and not `ex.Message` — John Saunders, Apr 12 '13 at 01:28

code4life · Accepted Answer · 2013-04-12T17:27:33.407

3

Remember to add the SqlConnection in the SqlCommand constructor.

http://msdn.microsoft.com/en-us/library/877h0y3a.aspx

Like:

var cmd = new SqlCommand(thisSelectStatementString, myConnection);

Or as Kirk mentioned:

var cmd = myConnection.CreateCommand(thisSelectStatementString);

edited Apr 12 '13 at 17:27

answered Apr 12 '13 at 01:14

code4life

15,655
7
50
82

1

*Or* just use `myConnection.CreateCommand()` and have it do that for you. – Kirk Woll Apr 12 '13 at 01:57

Pyromancer · Answer 2 · 2013-04-12T01:19:28.213

I think you're connection is blocked in the try scope above, try this one

        try
        {
            xavierConnection.Open();

            SqlCommand RiskRevalCommand = new SqlCommand("select * from CreditAdmin.dbo.CreditData_Test", xavierConnection/*don't forget this*/);


            SqlDataReader reader = RiskRevalCommand.ExecuteReader();

            while (reader.Read())
            {
               //no need to try-catch here
               double.TryParse(reader["Available Balance"].ToString(), out _availability);
                    ...
            }
         }

         catch (Exception e)
         {
              MessageBox.Show(e.Message);
         }

         finally
         {
              xavierConnection.Close();
         }

and also you set the Trusted_Connection=true but you already set a username and a password change it to false,

try this format in your connection string

ConnectionString = "server=Server; user id=FB\\user; password=top$secret;" +
            "database=dataserver; Trusted_Connection=false; Asynchronous Processing=true";

Nope, the OP's connection is declared in the outer-most scope. — Kirk Woll, Apr 12 '13 at 01:57
yeah, i didn't notice,because the code is full of try catch :D — Pyromancer, Apr 12 '13 at 02:02

SQLCommand Fails When Connection Parameter is Not Set

2 Answers2