CODEWITHSUNDEEP
c#.netdatabasems-access
Armin Malek
Armin Malek

Reputation: 25

reading data with OleDbDataReader

I'm trying to get the value of a Field (User Access level it's 1 or 2 in string format) after login

OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dsms.accdb");
                        connection.Open();
OleDbDataReader reader = null;
OleDbCommand command = new OleDbCommand("SELECT AL From Users WHERE Username='" + textusername.text + "'", connection);
reader = command.ExecuteReader();

if( reader.HasRows)
{
    MessageBox.Show("success","status");
    label1.Text = reader.GetString(1);
}
else
    MessageBox.Show("failur", "status");


connection.Close();

I did execute the code in Access and it's was totally fine but in the program, it says "No data exist for the row/column"

Upvotes: 1

Views: 886

Answers (1)

Steve
Steve

Reputation: 216243

The main problem in your code is the fact that you need to call reader.Read() to get anything out from a DataReader. Just calling HasRows doesn't position the reader on the first record of your query.

There are other problems in your code. Disposable objects like connections, commands and readers should be created in a using statement to ensure proper disposition after use and because you have only one field in your query, you should use the index 0 to retrieve it not 1.

Finally the most important one. You should NEVER concatenate strings to build an sql query. In this way a malicious user could write anything in your textbox, even valid sql commands that could be executed against your database. It is called Sql Injection and if you search for these terms you will find very detailed discussions about it. However, to avoid this problem (and others like parsing input with apostrophes) you use a parameterized query like below.

using(OleDbConnection connection = new OleDbConnection(.....))
using(OleDbCommand command = new OleDbCommand("SELECT AL From Users WHERE Username=@name", connection);
{
    connection.Open();
    command.Parameters.Add("@name", OleDbType.VarWChar).Value = txtusername.text;
    using(OleDbDataReader reader = command.ExecuteReader())
    {
        if( reader.Read())
        {
           MessageBox.Show("success","status");
           label1.Text = reader.GetString(0);
        }
        else
           MessageBox.Show("failur", "status");
    } 
}

Upvotes: 1

Related Questions