How to add a record if the result of a SQL query is empty
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

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

  1. #1
    Join Date
    Jan 2007
    Posts
    28

    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";
            }

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,861

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

    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.
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Jan 2007
    Posts
    28

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

    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.

  4. #4
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,861

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

    Where are you opening the connection? I do not see code for that in what you provided.
    Always use [code][/code] tags when posting code.

  5. #5
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,861

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

    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
    Always use [code][/code] tags when posting code.

  6. #6
    Join Date
    Jan 2007
    Posts
    491

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

    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.

  7. #7
    Join Date
    May 2002
    Posts
    487

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

    Change the SQL Statement:

    Code:
    Select * from v2007 
    
    
    Select
    coalesce (Name, "none"),
    coalesce (SomeTextField, "none"),
    coalesce (SomeNumberField, 0)
    From v2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center