Connect with OleDbConnection

Question

I am trying to connect to a database with two tables. However, after I try and log in, I have an error. The error says there is no row at spot zero. I think this is because of my connection:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

namespace Project3
{

public partial class _Default : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void login_Click(object sender, EventArgs e)
    {
        OleDbConnection connect = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\parodeghero\\Documents\\Visual Studio 2010\\Projects\\Project3\\Project3\\App_Data\\QA.mdb;Persist Security Info=True");
        //set up connection string
        OleDbCommand command = new OleDbCommand("select * from Employee where Login=@login", connect);
        OleDbParameter param0 = new OleDbParameter("@login", OleDbType.VarChar);

        param0.Value = employeeID.Text;
        command.Parameters.Add(param0);

        //middle tier to run connect
        OleDbDataAdapter da = new OleDbDataAdapter(command);

        DataSet dset = new DataSet();

        da.Fill(dset);

        //problem line
       if (dset.Tables[0].Rows[0]["Password"].ToString().Equals(password.Text))
        {

Solution 1

You need to open the connection

protected void login_Click(object sender, EventArgs e)
    {
        OleDbConnection connect = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\parodeghero\\Documents\\Visual Studio 2010\\Projects\\Project3\\Project3\\App_Data\\QA.mdb;Persist Security Info=True");
        //set up connection string
        OleDbCommand command = new OleDbCommand("select * from Employee where Login=@login", connect);
        OleDbParameter param0 = new OleDbParameter("@login", OleDbType.VarChar);

        param0.Value = employeeID.Text;
        command.Parameters.Add(param0);

        try
        {
            connect.Open();
        }catch(Exception err){ Debug.WriteLine(err.Message);}

        //middle tier to run connect
        OleDbDataAdapter da = new OleDbDataAdapter(command);

        DataSet dset = new DataSet();

        da.Fill(dset);

Solution 2

You do NOT need to open the connection. OleDbDataAdapter.Fill will open the connection AND close it if it found it closed to start with. Fill leaves the connection in the state that it finds it.

I do question your SQL. My understanding of OleDb is that it does NOT support naming parameters in SQL. It needs a place holder "?" instead of @login. You need a Parameter for each placeholder and the parameters must be added in the order they occurr. If your SQL is not valid, then you will have either a SQL Exception or no data in the DataTable, i.e. NO row 0 !

Solution 3

oledb connection complete code

http://csharp.net-informations.com/data-providers/csharp-oledb-connection.htm

        string connetionString = null;
        OleDbConnection cnn ;
        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourdatabasename.mdb;";
        cnn = new OleDbConnection(connetionString);
        try
        {
            cnn.Open();
            MessageBox.Show ("Connection Open ! ");
            cnn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Can not open connection ! ");
        }

curos