Trying to execute a query multiple times using while loop

Question

I'm trying to execute a query for MySql using while loop but it gives me error and I don't have any idea how to fix it. Without the loop, the code works well.

Additional information: Object reference not set to an instance of an object.

Here is what i got:

String copies = txtcopies.Text;
int x = Convert.ToInt32(copies);
int n = 0;

while (n < x)
{
    string loadstring = @"server=localhost;database=librarys;userid=root;password=1234;";

    MySqlConnection conDataBase = new MySqlConnection(loadstring);

    MySqlCommand cmdDataBase = new MySqlCommand("SELECT func_add_book('" + this.lblbnum.Text + "','" + this.txtisbn.Text + "','" + this.txttitle.Text + "','" + this.txtauthor.Text + "','" + this.txtpublisher.Text + "','" + this.txtdate.Text + "','" + this.txtcost.Text + "');", conDataBase);
    string returnedValue = cmdDataBase.ExecuteScalar().ToString();

    n++;

    conDataBase.Open();
    ClearAllText(txtcopies);
    MessageBox.Show(returnedValue);
}

Your error will tell you the exact line where it's happening. See http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it for some good tips on tracking down the error on that line. — Jacob, Oct 19 '15 at 01:02
Why are you opening the connection after you execute the command? — Crowcoder, Oct 19 '15 at 01:12
You must open connection before execute after that I guess this line is the error... `string returnedValue = cmdDataBase.ExecuteScalar().ToString();`. Due to returning `null` and you try to `.ToString()` it — Nic, Oct 19 '15 at 01:18

score 2 · Accepted Answer · edited Oct 19 '15 at 01:38

The problem is that you are opening your connection after executing the query. Also you only need to open SQL connection once in your code. Try the below code and see if it works.

String copies = txtcopies.Text;
int x = Convert.ToInt32(copies);
int n = 0;
string loadstring = @"server=localhost;database=librarys;userid=root;password=1234;";
MySqlConnection conDataBase = new MySqlConnection(loadstring);
try
{
    conDataBase.Open();
    while (n < x)
    {
        MySqlCommand cmdDataBase = new MySqlCommand("SELECT func_add_book('" + this.lblbnum.Text + "','" + this.txtisbn.Text + "','" + this.txttitle.Text + "','" + this.txtauthor.Text + "','" + this.txtpublisher.Text + "','" + this.txtdate.Text + "','" + this.txtcost.Text + "');", conDataBase);
        string returnedValue = cmdDataBase.ExecuteScalar().ToString();
        n++;
        ClearAllText(txtcopies);
        MessageBox.Show(returnedValue);
    }
}
Catch (Exception Ex)
{
}
Finally
{
    conDataBase.Close()
}

Trying to execute a query multiple times using while loop

1 Answers1