Exception: System.InvalidOperationException Trying

Question

I am trying to create a login Form. But Having Problems with database. I have created a windows form which consists of a user name and password, and a login button. But I think statement :

DataAdapterObject.Fill(DataTableObject) 

has some error. I am using Visual Studio Profesional 2013 Update 4 and Sql Server 2014 Enterprise Editon.

The Code is as follows :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Society_Accounting_Software
{
 public partial class LoginScreen : Form
{
    SqlConnection databaseConnect = new SqlConnection();


    public LoginScreen()
    {
        SqlConnection databaseConnect = new SqlConnection();
        databaseConnect.ConnectionString = "Data Source=GAURAV-PC;Initial Catalog=SocietyAccountingDatabase;Integrated Security=True";
        InitializeComponent();

    }

    private void label1_Click(object sender, EventArgs e)
    {

    }

    private void Form1_Load(object sender, EventArgs e)
    {


        SqlConnection databaseConnect = new SqlConnection("Data Source=GAURAV-PC;Initial Catalog=SocietyAccountingDatabase;Integrated Security=True");

        databaseConnect.Open();

    }
    private void textBox1_TextChanged(object sender, EventArgs e)
    {

    }

    private void label2_Click(object sender, EventArgs e)
    {

    }

    private void textBox1_TextChanged_1(object sender, EventArgs e)
    {

    }

    private void button1_Click(object sender, EventArgs e)
    {
        string queryString = "select UserId,UserPassword from UserAccounts where UserId='gaurav' AND  UserPassword='test123'";
        SqlConnection databaseConnect = new SqlConnection();
        databaseConnect.ConnectionString = "Data Source=GAURAV-PC;Initial Catalog=SocietyAccountingDatabase;Integrated Security=True";
        databaseConnect.Open();
        string userName = UserNameTextBox.Text;
        string Password = PasswordTextBox.Text;
        SqlCommand SqlCommandObject = new SqlCommand("select UserId,UserPassword from UserAccounts where UserId='"+userName+"' AND  UserPassword='"+Password+"'");
        SqlDataAdapter DataAdapterObject = new SqlDataAdapter(SqlCommandObject);
        DataTable DataTableObject = new DataTable();
        DataAdapterObject.Fill(DataTableObject);



        if (DataTableObject.Rows.Count > 0)
        {
            MessageBox.Show("Login Sucessful");
            AdminConsoleForm AdminConsole= new AdminConsoleForm();
            this.Hide();
            AdminConsole.Show();

        }
        else
        {
            MessageBox.Show("Invalid Login Name And Password Please Try Again!");

        }

        databaseConnect.Close();




        //AdminConsoleForm AdminConsole= new AdminConsoleForm();
        //this.Hide();
        //AdminConsole.Show();





    }
}
}

Can any one help?

Answer 1

A Simple code for a login form:

namespace Society_Accounting_Software
{
public partial class LoginScreen : Form
{
    SqlConnection databaseConnect = new SqlConnection();


    public LoginScreen()
    {
        SqlConnection databaseConnect = new SqlConnection();
        databaseConnect.ConnectionString = "Data Source=GAURAV-PC;Initial Catalog=SocietyAccountingDatabase;Integrated Security=True";
        InitializeComponent();

    }

    private void label1_Click(object sender, EventArgs e)
    {

    }

    private void Form1_Load(object sender, EventArgs e)
    {


    }
    private void textBox1_TextChanged(object sender, EventArgs e)
    {

    }

    private void label2_Click(object sender, EventArgs e)
    {

    }

    private void textBox1_TextChanged_1(object sender, EventArgs e)
    {

    }

    private void button1_Click(object sender, EventArgs e)

    {

        try

        {

            if (!(UserNameTextBox.Text == string.Empty))

            {

                if (!(PasswordTextBox.Text== string.Empty))

                {

                   //this represent your connection to database
                    String str = "Data Source=GAURAV-PC;Initial Catalog=SocietyAccountingDatabase;Integrated Security=True";

                    String query = "select * from UserAccounts where userid = '"+UserNameTextBox.Text+"'and password = '"+this.PasswordTextBox.Text+"'";

                    SqlConnection con = new SqlConnection(str);

                    SqlCommand cmd = new SqlCommand(query, con);

                    SqlDataReader dbr;

                    con.Open();

                    dbr = cmd.ExecuteReader();

                    int count = 0;

                    while (dbr.Read())

                    {

                        count = count + 1;

                    }

                    con.Close();

                    if (count == 1)

                    {
                        AdminConsoleForm objmain = new AdminConsoleForm();
                        objmain.Show(); //after login Redirect to second window  
                        this.Hide();//after login hide the  Login window   

                    }

                    else if (count > 1)

                    {

                        MessageBox.Show("Duplicate username and password", "login page");

                    }

                    else

                    {

                        MessageBox.Show(" Username and Password Incorrect", "login page");

                    }

                }

                else

                {

                    MessageBox.Show(" Password Empty", "login page");

                }


            }
            else

            {

                MessageBox.Show(" Username Empty", "login page");

            }




        }

        catch (Exception es)

        {

            MessageBox.Show(es.Message);
        }

    }
}

I have kept the code really simple. You can do salting if you want security.

Answer 2

First, as your form load, you've already instantiated a connection string and immediately open it. Second, as user clicks the button to login, you've instantiated the same connection string and open it again, which makes the operation redundant. Because the connection state of that Database is already Open which you're trying to Open again during button click which makes the operation impossible or cannot be processed.

Answer 3

Wherever possible you should be using database connections like so.

using(var connection = new SqlConnection(connectionString))
{
    connection.Open();
    //...
}

At the moment you are randomly creating connections in various methods ontop of having a connection field, this is pretty confusing.

You should not be concatenating SQL and should use parameterised queries

using(var connection= new SqlConnection(connectionString))
{
    connection.Open();

    var sql = @"SELECT password FROM users WHERE userid = @userid";

    var command = new SqlCommand(sql, connection);
    command.Parameters.Add("@userid", SqlDbType.VarChar);
    command.Parameters["@userid"].Value = username;

    // ....
}

Passwords should never be stored as plaintext and you should not be able to query the password 'directly'. A simple method of securing passwords is to hash the password with a random salt, storing the salt along with the hash in the database.

You don't really need a DataTable you should only have a single row returned and a DataReader is enough in this scenario.

using(var connection= new SqlConnection(connectionString))
{
    connection.Open();

    var sql = @"SELECT password, salt FROM users WHERE userid = @userid";

    var command = new SqlCommand(sql, connection);
    command.Parameters.Add("@userid", SqlDbType.VarChar);
    command.Parameters["@userid"].Value = username;

    using(var reader = command.ExecuteReader())
    {
        if (reader.Read())
        {
            var password = reader.GetString(0);
            var salt = reader.GetString(1);

            return CheckPassword(password, salt, PwrdTextBox.Text);
        }

        Debug.WriteLine("The user {0} does not exist", username);
        return false;
    }
}

I would recommend reading through this article Salted Password Hashing - Doing it Right on CodeProject.