OleDbDataReader reading command.ExecuteReader();

Question

Im try to write a simple Login Form with access DB. after i press login button its return to code and mark this:

OleDbDataReader reader= command.ExecuteReader();

the connection is successful but when i press login its return.

mdb name: users1.mdb table name: DataData

image: https://imgur.com/a/gl2Xfga

what my problem with my code?

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

namespace CDHW
{
    public partial class Form1 : Form
    {
        private OleDbConnection connection = new OleDbConnection();
        public Form1()
        {
            InitializeComponent();
            connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\RBA\Desktop\123\users1.mdb;
Persist Security Info=False;";
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                connection.Open();
                checkConnection.Text = "Connection Successful";
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error  " + ex);
            }
        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void btn_Login_Click(object sender, EventArgs e)
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "select * from DataData where User='" + text_Username.Text + "' and Password='" + text_Password.Text + "'";

            OleDbDataReader reader= command.ExecuteReader();
            int count = 0;
            while (reader.Read())
            {
                count = count + 1;
                //count++;
                //
            }
            if (count == 1)
            {
                MessageBox.Show("Wellcome to G Tool");
            }
            if (count == 1)
            {
                MessageBox.Show("Password is duplicated");
            }
            else
            {
                MessageBox.Show("User Name or Password is incorrect");
            }
            connection.Close();
        }
    }
}

Answer 1

The using blocks ensure that you database objects are closed and disposed even it there is an error. This insures that unmanaged resources used by these objects are cleaned up.

You can pass the connection string directly to the constructor of the connection. Likewise the constructor of the command can take the sql statement and the connection.

Use the overload of the Parameters.Add that takes the parameter name and the datatype. This helps keep us aware that the value of the parameter matches the datatype.

Since we used Count in the select statement we can use ExecuteScalar and only bring down a single piece of data rather than the entire record.

Of course in a real application you would NEVER store passwords as plain text.

    private void button1_Click(object sender, EventArgs e)
    {
        int RetVal;
        using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\RBA\Desktop\123\users1.mdb;Persist Security Info=False;"))
        {
            using (OleDbCommand cmd = new OleDbCommand("Select Count(*) From DataData Where [user] = @User And [password] = @password;", connection))
            {
                cmd.Parameters.Add("@User", OleDbType.VarChar).Value = text_Username.Text;
                cmd.Parameters.Add("@Password", OleDbType.VarChar).Value = text_Password.Text;
                connection.Open();
                RetVal = (int)cmd.ExecuteScalar();
            }
        }
        if (RetVal == 1)
            MessageBox.Show("Welcome");
        else
            MessageBox.Show("Login incorrect");
    }

Almost forgot! With the OleDb provider the Parameters must be added to the parameters collection in the same order that they appear in the sql statement. This provider will not match parameter names.

Answer 2

Note: see comments re escaping User and Password.

The first thing we should consider here is: parameters. Right now, if the username/password contain a range of characters, very bad things can happen; so:

command.CommandText = "select * from DataData where [User]=@cn and [Password]=@pw";
command.Parameters.Add(new OleDbParameter("@cn", text_Username.Text));
command.Parameters.Add(new OleDbParameter("@pw", text_Password.Text));

The next problem is plain text passwords, but that's a huge area - just... don't ever store passwords as text... ever.

Now, I'm going to assume that the above doesn't fix it; if so, you need to check how User and Password are defined in the database. The error message suggests that one of them isn't very text-like.

There's also a lot of using etc problems in this code which could lead to odd errors related to "open reader" etc; if possible, I strongly recommend a tool like Dapper, which will make it a lot easier to get this kind of code right. For example:

var userIds = connection.Query<string>(
    "select [User] from DataData where [User]=@cn and [Password]=@pw",
    new { cn = text_Username.Text, pw = text_Password.Text }).AsList();
switch(userIds.Count) {
    case 0: // TODO: doesn't exist
    case 1: // TODO: perfect
        break;
    default: // TODO: oops, multiple users
        break;
}

Answer 3

Consider using a OleDbDataAdapter with a DataTable instead.

    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    command.CommandText = "select * ... ";
    DataTable zDataTable = new DataTable;
    OleDbDataAdapter zDataAdapter = new OleDbDataAdapter;
    zDataAdapter.SelectCommand = command;
    zDataAdapter.Fill(zDataTable);
    int RecordCount = DataTable.Rows.Count;

This way you will not have to iterate through all the records to get the record count.