How to add a record if the result of a SQL query is empty

Hi,

I would like to check if some records exist in MySQL database and if they are missing I want to add some alternative information. I tried to use the following code but made a mistake. I am a beginner in c# and not sure how to do that. Please could you tell me what the problem is:

Thanks
telmessos

Code:

---

private void button1_Click(object sender, EventArgs e)
        {
            for (int has = 1; has <= 10; has++)
            {
                for (int ted = 1; ted <= 15; ted++)
                {
                    for (int cin = 1; cin <= 20; cin++)
                    {
                        for (int yas = 1; yas <= 100; yas++)
                        {
                            connection.Open();
                            String testsql = "Select * from v2007 where HASTALIK_GRUBU='" + has + "' and TEDAVI_ILI='" + ted + "' and CINSIYET='" + cin + "' and YAS='" + yas + "'";
                            MySqlCommand testcommand = new MySqlCommand();
                            testcommand.CommandText = testsql;
                            testcommand.ExecuteNonQuery();
                            MySqlDataAdapter adapter2 = new MySqlDataAdapter();
                            adapter2.SelectCommand = testcommand;
                            DataTable dataset = new DataTable();
                            adapter2.Fill(dataset);
                            if (dataset.Rows.Count == 0)
                            {
                                String sql2 = "Insert into v2007(HASTALIK_GRUBU,TEDAVI_ILI,CINSIYET,YAS,AYAKTA,YATARAK) values('" + has + "','" + ted + "','" + cin + "','" + yas + "','0','0')";
                                MySqlCommand command2 = new MySqlCommand();
                                command2.CommandText = sql2;
                                command2.ExecuteNonQuery();
                            }
                            connection.Close();
                        }
                    }
                }
            }
            label2.Text = "İşlem bitti";
        }

---

It would help you you told us what the error was.

Are the last 2 fields on your insert statement text or numeric data types? If numeric you need to loose the single quotes.

The error message is "Connection must be valid and open" but I have no idea why this error message shows. Because the connection is already open.

Where are you opening the connection? I do not see code for that in what you provided.

Also looking at your code above it looks like the fields all contain numeric values. If these are numeric fields in the db then you shjould not use the 's

We shouldn't guess what's wrong with your code... provide some information about the error. Like... what's the Line number?

Also, you're attempting to connect to the database 15*20*100 times. Why not connecting outside the for loops?

I'd also consider to use a SELECT COUNT query, instead of selecting all the data, since all you really need is to check weather or not the Rows.Count is 0.
Alternatively you could select all the relevant data in one query and look for matches from the records placed in the Dataset.

Change the SQL Statement:

Code:

---

Select * from v2007


Select
coalesce (Name, "none"),
coalesce (SomeTextField, "none"),
coalesce (SomeNumberField, 0)
From v2007

---