CODEWITHSUNDEEP
c#asp.netvisual-studiovisual-studio-2010visual-studio-2008
asguy
asguy

Reputation: 199

Connect with OleDbConnection

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))
        {

Upvotes: 5

Views: 89247

Answers (3)

Lyuben Todorov
Lyuben Todorov

Reputation: 14163

You need to open the connection

protected void login_Click(object sender, EventArgs e)
{
    string pathToYourFileMDB = @"C:\yourPathHere\File.mdb";
    
    try
    {
        OleDbConnection connect = new OleDbConnection($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={pathToYourFileMDB};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);
        
        //open connection
        connect.Open();
        
        //middle tier to run connect
        OleDbDataAdapter da = new OleDbDataAdapter(command);
        DataSet dset = new DataSet();
        da.Fill(dset);
    }
    catch(Exception err)
    { 
        Debug.WriteLine(err.Message);
    }
}

Upvotes: 9

curos
curos

Reputation: 11

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

Upvotes: 1

JRT
JRT

Reputation: 71

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 !

Upvotes: 7

Related Questions