Inserting data after checking if table is empty

Question

I have a server program that will store certain data sent by the client. One data is the client's hostname. The server will check if the hostname exist, if not then it will insert that new data. It should look like this.

hostname_id | hostname
------------------------
      1     | Admin
      2     | Guest_PC
      3     | Bob_PC2

My problem is it won't store the newly inserted data. It keeps on returning zero but not storing anything. Here is my code.(Edited to correct version)

string constring = "Database=chtbuster;Data Source=localhost;User Id=root;Password=''";
    string count1 = "SELECT COUNT(hostName) FROM chtbuster.hostnametable WHERE hostName=@machineName ";
    using (MySqlConnection conDataBase = new MySqlConnection(constring))
    {
            MySqlCommand cmd1Database = new MySqlCommand(count1, conDataBase);
            conDataBase.Open();

             long count = (long)cmd1Database.ExecuteScalar();
             if (count == 0)
             {
                  string insert_ht = "INSERT INTO chtbuster.hostnametable(hostName) VALUES(@machineName);";
                  MySqlCommand cmd5Database = new MySqlCommand(insert_ht, conDataBase);

                  cmd5Database.Parameters.AddWithValue("@machineName", machineName);
                  cmd5Database.ExecuteNonQuery();
                  //*test* output.Text += "\n Empty " + count;
             }
             else
             {
                  //not empty, insert other data
             }

}

I have coded PHP database before and is new to C# database, I'm quite confused. Please help. Thank you.

You need to execute the SQL insert command you created....DOH! — Steve Wellens
– Steve Wellens, Commented Oct 5, 2015 at 16:48
@SteveWellens sir I am new to C# database. Is it the ExecuteNonQuery();? — charlie9495
– charlie9495, Commented Oct 5, 2015 at 16:54

snow_FFFFFF · Accepted Answer · 2015-10-05 16:57:12Z

2

You can do this in one step with EXISTS:

IF NOT EXISTS (SELECT hostName FROM chtbuster.hostnametable WHERE hostName=@machineName)
  INSERT INTO chtbuster.hostnametable(hostName_id) VALUES(@machineName);

As mentioned in the comments, you need to execute the query to get a result.

answered Oct 5, 2015 at 16:57

snow_FFFFFF

3,31923 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

charlie9495 Over a year ago

sir? is it okay to ask if how should i output a selected query? string gethostname_id = "SELECT INTO chtbuster.hostnametable(hostName_id) WHERE hostName=@machineName";

snow_FFFFFF Over a year ago

The query, in my answer, doesn't return a value. When executing, if you want to return a value, you would ExecuteScalar (like you did above), or ExecuteReader to return a data reader that includes any query results.

snow_FFFFFF Over a year ago

If you want to encapsulate your logic and return something meaningful, maybe write a stored procedure in the db. Then, you logic isn't hard-coded in some sql in your application.

charlie9495 Over a year ago

"encapsulate your logic" i do not know much about encapsulation but i will try ExecuteReader. Thanks.

Collectives™ on Stack Overflow

Inserting data after checking if table is empty

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related