C# SQL connection problem

Question

i am using VS.NET 2008 with MS SQL server 2005 to develop a window form application but everytime i got new error in connection or after conected,

in my current code the connection opened but doesnt work after that in transaction of queries. maybe i have problem while making new DB or new datasource also m not that satisfy how to get Connecting String

this is my code....

/////////
private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            //setData();
            string ConnectingString = @"Data Source=SERVER1\SQLEXPRESS;Initial Catalog=Alkawthar;Integrated Security=True;Pooling=False";
            qry = "SELECT * FROM Table1";
            //reader = db.select_data(qry);
            ds1 = new DataSet();
            conn = new SqlConnection(ConnectingString);

            conn.Open(); 
            MessageBox.Show("connection opened");
            da.Fill(ds1,"Workers");
            NavigateRecords();
            MaxRows = ds1.Tables["Workers"].Rows.Count;
            string sql = "SELECT * From tblWorkers";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, conn);
            conn.Close();
            MessageBox.Show("connection closed");
            conn.Dispose();
        }
        catch (Exception ex)
        {
            MessageBox.Show("exception");
            MessageBox.Show(ex.Message);
        }
    }
/////////////////

Fill throws an exception also when i use reader it return null although there is data in DB thanks

You don't need both conn.Close() and conn.Dispose() (Dispose calls Close). You should use the using (conn = new Connection()) { ... } pattern instead of explicitly calling Dispose. — Albin Sunnanbo
– Albin Sunnanbo, Commented Nov 8, 2010 at 12:45
@Albin: messagebox i put it on purpose to find xactly from where is the xception, thanx i need solution not advices — salman
– salman, Commented Nov 9, 2010 at 6:24

Rune Grimstad · Accepted Answer · 2010-11-08 12:48:00Z

2

The most obvious problem here is that you access the SqlDataAdapter before initializing it. That will cause a null reference exception. Try to move the line da = new SqlDataAdapter(...) to the line before you do the da.Fill(...).

Edit:

No, wait! I see that you do two queries and two fills in there. You need to initialize the SqlDataAdapter before doing the first fill. Then you should get rid of the null reference exception.

Edit again:

Also, as commented, you don't need call both the SqlConnection.Close and SqlConnection.Dispose methods. As long as you use the SqlDataAdapter you don't even need to do the SqlConnection.Open on the connection, for the Fill method will do all that for you. As long as the connection starts out being closed, the Fill method will close it again for you when it is done.

answered Nov 8, 2010 at 12:48

Rune Grimstad

36.5k10 gold badges66 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rune Grimstad Over a year ago

@Turrau: Haha... +1'ed you too :-)

salman Over a year ago

after editing now the exception is :: invalid object name'tblWorkers' which name of the table in my data base

Rune Grimstad Over a year ago

@salman: Yep. That is the one you will get if your variable isn't initialized. You use the da object before setting it to anything.

Rune Grimstad Over a year ago

@salman: The invalid object name message means that you are trying to access an object that either doesn't exist or that your user cannot access. Try to run the same query in the query browser or sql management tool and see if it runs there. Also make sure that the user you logs on as actually can access the table. Finally, you may want to try to prefix the table name with dbo. or whatever namespace you have defined the table in.

salman Over a year ago

is it possible that my DB didnt add properly?

|

StingyJack · Accepted Answer · 2010-11-08 13:09:18Z

A few points of advice;

Like Turrau and Rune say, your stuff is in the wrong order. Open connection, Execute SQL - Get raw data, close connection. Then do your counting, etc.
Don't put message box or logging calls anywhere in between the connection opening and closing. This has burned me before, where the those calls can affect the SQL call because they fudge the exception details and make it difficult to trace. This is also applicable when using a SQL data reader.
Put the SQL stuff in a using block.

Try this...

    string _connStr = @"Data Source=SERVER1\SQLEXPRESS;Initial Catalog=Alkawthar;Integrated Security=True;Pooling=False";
    string _query = "SELECT * FROM Workers";
    DataSet _ds = new DataSet();

    try
    {

        using (SqlConnection _conn = new SqlConnection(_connStr))
        {
            SqlDataAdapter _da = new SqlDataAdapter(_query, _conn);
            _conn.Open();
            _da.Fill(_ds);
        }

        // insert null dataset or invalid return logic (too many tables, too few columns/rows, etc here.


        if (_ds.Tables.Count == 1)
        { //There is a table, assign the name to it.
            _ds.Tables[0].TableName = "tblWorkers";
        }

        //Then work with your tblWorkers

    }
    catch (Exception ex)
    {
        Console.Write("An error occurred: {0}", ex.Message);
    }

Turrau · Accepted Answer · 2010-11-08 12:48:42Z

1

Seems to me, that you use your DataAdapter before initialization. Do you get a NullReferenceException?

da.Fill(ds1,"Workers"); 
// ...
da = new System.Data.SqlClient.SqlDataAdapter(sql, conn);

answered Nov 8, 2010 at 12:48

Turrau

4521 gold badge4 silver badges11 bronze badges

2 Comments

salman Over a year ago

object refrence is not set to an instance of the object

Turrau Over a year ago

Yes, this is an NullReferenceException. Have a look at Rune's answer, it should help you.

Collectives™ on Stack Overflow

C# SQL connection problem

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related