Problems using OleDbDataAdapter with access database

[//Gonz]

Hi guys
i'm trying to just do a simple email/password validation on two text boxes.
This code runs but when i type in a correct email address it throws an error "Object reference not set to an instance of an object." on

AdAcc.InsertCommand.Connection = OleAcc;

I'm thinking i may need to link it to the database somehow.
And i don't want to use Try/Catch!!

Sorry if this is outrageously noobish.

public void tbemail_LostFocus(object sender, EventArgs e)
        {
            if ((tbemail.Text == "")||(tbemail.Text == "Email"))
            {
                tbemail.Text = "Email";
                i = false;
            }
            else
            {
                String emQuery = "SELECT [Email] FROM Accounts where Email = "+tbemail.Text.ToString()+"";
                if (emQuery == null)
                {
                    i = false;
                }
                else
                {
                    i = true;
                }
            }
        }

public void button1_Click(object sender, EventArgs e)
        {
            OleDbConnection OleAcc = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\VespaModC#project\\Database\\Accounts.accdb;");
            
            if (i == true)
            {
                OleDbDataAdapter AdAcc = new OleDbDataAdapter();
                OleAcc.Open();
                AdAcc.InsertCommand.Connection =  OleAcc; // connects data adapter to 

                AdAcc.InsertCommand.CommandText = "SELECT [Password] FROM Accounts where Email = " + tbemail.Text.ToString() + ""; // runs the query
                AccDS.Clear();  // clears dataset
                int AccDSPW = AdAcc.Fill(AccDS, "Password"); // fills dataset with data
                if (AccDSPW > 0)
                {
                    DataTable dtacc = AccDS.Tables["Password"]; // puts found password in data table
                    String PW = dtacc.ToString(); // converts data table to string for validation
                    if (tbpassword.Text == PW) // checks if password matches
                    {

                        OleAcc.Close();
                        this.Hide();                    // opens
                        Main MainForm = new Main();     // main
                        MainForm.Show();                // form
                    }
                    else // Password Does Not Match
                    {
                        dbIP incpwform = new dbIP(); // Incorrect password diag box
                        incpwform.Show();
                    }
                }
            }
            else // Email does not match
            {
                dbIEA incemform = new dbIEA(); // Incorrect Email diag box
                incemform.Show();
            }
        }

[gusano79]

Hi guys
i'm trying to just do a simple email/password validation on two text boxes.
This code runs but when i type in a correct email address it throws an error "Object reference not set to an instance of an object." on AdAcc.InsertCommand.Connection = OleAcc;

This is happening because AdAcc.InsertCommand is null. You need to create a new OleDbCommand and assign it to AdAcc.InsertCommand. The linked MSDN page has an example.

[//Gonz]

Thankyou gusano79

Would you be able to give an example?

[//Gonz]

I have tried all sorts of ways to solve this but i just cannot achieve what i want to do.
Basically i want to use if else conditions to query a database to see if an entered in email/password matches that of the database.

Any help would be very appreciated

[charlybones]

I use the following code to connect to an Access 2.0 mdb file and store values in a DataAdapter. It works fine.

connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path_to_MDB_file";OLE DB Services=-4;");
connection.Open();


String query = "select * from Blah where Blaaah1 = blah2";
OleDbCommand cmdLines = new OleDbCommand(query, connection); //create command
DataSet aSet = new DataSet();//create dataset to store results
OleDbDataAdapter aAdapter = new OleDbDataAdapter(query, connAcc); //create data adapter
aAdapter.Fill(aSet);//fill the dataset using the dataAdapter

from here, you can use

aSet.Tables[index].select("filter query here"); //etc to look for stuff..

Hope this helps.

[//Gonz]

thanks for your help charlybones

im having a problem with the part before now

string stem = null;
            String EmQuery = "SELECT [Email] FROM Accounts WHERE Email = " + tbemail.Text + ""; // adds query to "EmQuery"
            OleDbCommand command = new OleDbCommand(EmQuery, AccConn);
            OleDbDataAdapter AdAcc = new OleDbDataAdapter(EmQuery, AccConn); // runs the query
            if (AdAcc == null)
            {
                i = false;
            }
            else
            {
// run rest of code

if i is false a dialog message shows up saying "email not registered"

[//Gonz]

I realised now why this bit of code isnt working:
OleDbDataAdapter AdAcc doesnt ever change once it has been declared.

I am now wondering how i could see if the results are null before

AdAcc.Fill(AccDS);//fill the dataset using the dataAdapter

runs.
otherwise AccDS returns a null error.

[//Gonz]

please can someone help?
it must be pretty straightforward and it would save me hours of browsing the internet for answers
thanks

[gusano79]

thanks for your help charlybones

im having a problem with the part before now

string stem = null;
            String EmQuery = "SELECT [Email] FROM Accounts WHERE Email = " + tbemail.Text + ""; // adds query to "EmQuery"
            OleDbCommand command = new OleDbCommand(EmQuery, AccConn);
            OleDbDataAdapter AdAcc = new OleDbDataAdapter(EmQuery, AccConn); // runs the query
            if (AdAcc == null)
            {
                i = false;
            }
            else
            {
// run rest of code

if i is false a dialog message shows up saying "email not registered"

You don't seem to understand how data adapters work.

Consider this line:

OleDbDataAdapter AdAcc = new OleDbDataAdapter(EmQuery, AccConn); // runs the query

This doesn't run the query. All it does is construct the OleDbDataAdapter object, which in this case initializes the SelectCommand property of the adapter for later use by its Fill method.

Constructors don't return null, either, so the code for if (AdAcc == null) should never actually happen.

Another issue:

DataTable dtacc = AccDS.Tables["Password"]; // puts found password in data table
String PW = dtacc.ToString(); // converts data table to string for validation

This doesn't actually get the password; read the documentation for DataTable.ToString to see what actually happens.

Take some time to read the MSDN section on DataAdapters and DataReaders.

[//Gonz]

Thank you for your help gusano but i just took a completely different approach:

OleDbConnection AccConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\\C#Project-VespaMod\\Database\\Accounts.accdb");
            AccConn.Open();

            String AllQuery = "SELECT * FROM Accounts";
            OleDbCommand Allcommand = new OleDbCommand(AllQuery, AccConn);
            OleDbDataAdapter AllAcc = new OleDbDataAdapter(AllQuery, AccConn);
            AdAcc.SelectCommand = Allcommand;
            DataSet aSet = new DataSet();
            AdAcc.Fill(aSet, "Accounts");

            DataTable AccTable = aSet.Tables["Accounts"];
            string email = tbemail.Text;
            string ex;
            ex = "Email='" + email + "'";
            DataRow[] frows = AccTable.Select(ex);
            frows = AccTable.Select(ex);
            if (frows.Length > 0)
            {
                GlobString.GlobUN = tbemail.Text; // Adds the email address to the global variable

                string password = tbpassword.Text;
                string pfrows;
                for (int p = 0; p < frows.Length; p++)
                {
                    pfrows = (frows[p][4]).ToString();
                    if (password == pfrows)
                    {
                        this.Hide();                    // opens
                        Main MainForm = new Main();     // main
                        MainForm.Show();                // form
                    }
                    else // Incorrect password diag box
                    {
                        dbIP incpwform = new dbIP(); 
                        incpwform.Show();
                    }
                }
            }
            else // Incorrect email diag box
            {
                GlobString.GlobUN = tbemail.Text; // Adds the email address to the global variable
                dbIEA incemform = new dbIEA(); // Incorrect Email diag box
                incemform.Show();
            }

well not completely different :)