SelectCommand.Connection is not initialized

Question

I'm trying to access my database, from this method in my DataBaseOptions class. I'm using this with ASP.NET for web development.

Whenever I run this method, In another class, I get following error:

SelectCommand.Connection is not initialized

And it says that the error is on line 40. Here's my code:

(The line with "adapter.Fill(dt);" is line 40.

private string connectionString = "oop_string"; // Global Variable
private static SqlConnection mycon = new SqlConnection(); // Global Variable
private static SqlCommand mycmd = new SqlCommand(); // Global Variable

public void setupConnection() {
    mycon.ConnectionString = ConfigurationManager.ConnectionStrings[connectionString].ToString();
    mycmd.Connection = mycon;
}

public bool UserExists(string username, string password) {
    setupConnection();

    mycmd.CommandText = "SELECT user_id FROM users WHERE user_username = @username AND user_password = @password";
    mycmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
    mycmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;

    DataTable dt = new DataTable();
    SqlDataAdapter adapter = new SqlDataAdapter(mycmd);

    adapter.Fill(dt);

    if(dt.Rows.Count == 0) {
        return false;
    } else {
        return true;
    }
}

public int getUserID(string username, string password) {
        setupConnection();

        mycmd.CommandText = "SELECT user_id FROM users WHERE user_username = @username AND user_password = @password";
        mycmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
        mycmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;

        SqlDataReader reader = mycmd.ExecuteReader();

        if(reader.Read()) {
            return Convert.ToInt32(reader["user_id"]);
        }

        mycon.Close();
        return 0;
}

If i try to put everything from setupConnection(); to the start of UserExists(); and getUserID(); I get the following error:

ConnectionString property has not been initialized.

Which it now claims, that the error is on line 51. The "SqlDataReader reader = mycmd.ExecuteReader();".

My teacher and I, have tried everything, google, other classmates, teachers. We can't seen to find a solution.

Make sure you add mycon.Open in setupConnection() method and Add following line adapter.SelectCommand.Connection = mycon before adapter.Fill(dt);. Hope it helps — MMK, Apr 24 '13 at 08:06

score 2 · Answer 1 · answered Apr 24 '13 at 08:02

2

You are not opening the connection anywhere in your code. Also consider ecnlosing your command and those object which implements IDisposable inside a using statement

answered Apr 24 '13 at 08:02

Habib

219,104
29
407
436

Where should the opening be? I can't seem to find the spot for it. Also, by adding this, I get an error saying 'ConnectionString' can't be editied. The Connection is open. – Kevin Jensen Petersen Apr 24 '13 at 08:12
@KevinJensenPetersen, you should open the connection as late as possible and close it as early as possible. For your current structure you can open the connection after setting the connection string. – Habib Apr 24 '13 at 08:50

score 0 · Answer 2 · edited Apr 24 '13 at 08:20

0

You are not opening the connection

    public void setupConnection() {
    mycon.ConnectionString = ConfigurationManager.ConnectionStrings[connectionString].ToString();
    mycon.Open()
    mycmd.Connection = mycon;
}

edited Apr 24 '13 at 08:20

Freelancer

9,008
7
42
81

answered Apr 24 '13 at 08:17

Mandeep Singh

2,016
7
19
32

score 0 · Answer 3 · answered Apr 24 '13 at 08:19

0

You will have to give connection object while executing query which you have not given.

It is as follows:

    DataTable dt = new DataTable();
    SqlDataAdapter adapter = new SqlDataAdapter(mycmd,mycon);

answered Apr 24 '13 at 08:19

Freelancer

9,008
7
42
81

SelectCommand.Connection is not initialized

3 Answers3