Database MS Access 2007 connection to C# ASP.net , login page

Question

I have a database that contains a table named "User(login,password,firstname,lastname)" . And I need to make login page . I've watched some tutorials , but it didn't help . I need to check if login and password exist in the database . and then redirect(if correct) to other page . This is what I already did:

OleDbConnection con = new OleDbConnection();
    public bool check()
    {
        con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Volodia\Documents\WebSiteDatabase.accdb";
        con.Open();
        string commandstring = "SELECT login,password FROM User";
        //objadapter = new SqlDataAdapter(CommandString, sqlconn.ConnectionString);
        OleDbDataAdapter objadapter = new OleDbDataAdapter(commandstring, con.ConnectionString);
        DataSet dataset = new DataSet();
        objadapter.Fill(dataset, "User");// it shows "Syntax error in FROM clause." here
        DataTable datatable = dataset.Tables[0];
        for (int i = 0; i < datatable.Rows.Count; i++)
        {
            string unam = datatable.Rows[i]["login"].ToString();
            string upwd = datatable.Rows[i]["password"].ToString();
            if ((unam == TextBox1.Text)&&(upwd==TextBox2.Text))
            {
                return true;
            }
        }

        return false;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (check() == true)
        {
            Response.Redirect("WebForm2.aspx");
        }
    }

Steve · Accepted Answer · 2013-05-08 12:42:56Z

The word PASSWORD is a reserved keyword for MS-Access Jet SQL. If you want to use it you need to enclose it in square brackets, the same for USER

 string commandstring = "SELECT login, [password] FROM [User]";

This will resolve the immediate problem of the Syntax Error but let me add some other code to show a different approach

 public bool check()
 {
    string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Volodia\Documents\WebSiteDatabase.accdb";
    using(OleDbConnection con = new OleDbConnection(conString)
    {
        con.Open();
        string commandstring = "SELECT count(*) as cntUser FROM [User] " + 
                               "WHERE login = ? AND [password] = ?";
        using(OleDbCommand cmd = new OleDbCommand(commandstring, con))
        {
            cmd.Parameters.AddWithValue("@p1", TextBox1.Text); 
            cmd.Parameters.AddWithValue("@p2", TextBox2.Text);
            int result = (int)cmd.ExecuteScalar();
            if(result > 0)
               return true;
        }
    }
    return false;
}

First, do not use a global connection object but create and use the connection only when needed.
Second, encapsulate the disposable objects like the connection and the command with the using statement that will ensure a correct close and dispose,
Third, pass the login and the password as conditions for the where clause (more on this later)
Fourth, use the parametrized query to avoid syntax errors and sql injection

Usually is not a good practice to store a password in clear text inside the database. You need to store only the hash of the password and recalculate this hash every time you need to check the user authenticity

Wow , this code is much more optimized and works better , thx

Collectives™ on Stack Overflow

Database MS Access 2007 connection to C# ASP.net , login page

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related