CODEWITHSUNDEEP
c#ms-access
Joey Arnanzo
Joey Arnanzo

Reputation: 349

Object reference is not set to an instance Access query C#

I'm trying to make a query into the Clients table, when the user enters a mobile number, the code checks if it matches any record, if it does, it returns the client's Name & Address into text boxes, but I'm getting this error "Object reference is not set to an instance of an object" by the time I enter anything into that textbox

here is the code, what could be the problem?

  private void textBox11_TextChanged(object sender, EventArgs e)
    {
        clientsearch();
        clientsearch2();
    }

    public void clientsearch()
    {
        using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;"))
        {

            conn.Open();
            string query = @"select Cname From Clients where Cmobile = @mobile";
            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn);
            cmd.Parameters.Add("@mobile", System.Data.OleDb.OleDbType.Integer).Value = textBox11.Text;
            cmd.ExecuteNonQuery();
            string result = cmd.ExecuteScalar().ToString();
            textBox12.Text = @result;
        }
    }
    public void clientsearch2()
    {
        using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;"))
        {

            conn.Open();
            string query = @"select Caddress From Clients where Cmobile = @mobile";
            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn);
            cmd.Parameters.Add("@mobile", System.Data.OleDb.OleDbType.Integer).Value = textBox11.Text;
            cmd.ExecuteNonQuery();
            string result = cmd.ExecuteScalar().ToString();
            textBox13.Text = @result;
        }
    }

Upvotes: 0

Views: 120

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416091

string result = cmd.ExecuteScalar().ToString();
textBox12.Text = @result;

@result isn't anything. You just want result. Additionally, sending separate queries to the server for this data is pointlessly inefficient. Do this instead:

public void clientsearch()
{
    string query = @"select Cname, Caddress  From Clients where Cmobile LIKE  @mobile + '*'";
    using (var conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;"))
    using (var cmd = System.Data.OleDb.OleDbCommand(query, conn))
    {
        cmd.Parameters.Add("@mobile", System.Data.OleDb.OleDbType.Integer).Value = textBox11.Text;
        conn.Open();

        using (var rdr = cmd.ExecuteReader())
        {
            if (rdr.Read())
            {
                textBox12.Text = rdr["Cname"].ToString();
                textBox13.Text = rdr["Caddress"].ToString();
            }
            rdr.Close();
        }
    }
}

Finally, it's better style to also abstract your database code away from user interface. Ideally you would return a Client class, but since I don't see one I'll show an example using a tuple instead:

public Tuple<string, string> FindClientByMobile(string mobile)
{
    string query = @"SELECT Cname, Caddress FROM Clients WHERE Cmobile LIKE @mobile + '*'";
    using (var conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;"))
    using (var cmd = System.Data.OleDb.OleDbCommand(query, conn))
    {
        cmd.Parameters.Add("@mobile", System.Data.OleDb.OleDbType.Integer).Value = mobile;
        conn.Open();

        using (var rdr = cmd.ExecuteReader())
        {
            rdr.Read();
            return Tuple<string, string>.Create(rdr["Cname"].ToString(), rdr["Caddress"].ToString());
        }
    }
}

If you're playing with a Visual Studio 2017 release candidate, you can also use the new Tuple shortcuts:

public (string, string) FindClientByMobile(string mobile)
{
    string query = @"SELECT Cname, Caddress FROM Clients WHERE Cmobile LIKE @mobile + '*'";
    using (var conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;"))
    using (var cmd = System.Data.OleDb.OleDbCommand(query, conn))
    {
        cmd.Parameters.Add("@mobile", System.Data.OleDb.OleDbType.Integer).Value = mobile;
        conn.Open();

        using (var rdr = cmd.ExecuteReader())
        {
            rdr.Read();
            return (rdr["Cname"].ToString(), rdr["Caddress"].ToString());
        }
    }
}

And then use them like this:

private void textBox11_TextChanged(object sender, EventArgs e)
{
    var result = FindClientByMobile(textBox11.Text);
    textBox12.Text = result.Item1;
    textBox13.Text = result.Item2;
}

Upvotes: 3

Related Questions