NielsStenden NielsStenden - 3 years ago 189
MySQL Question

Insert into query with where clause

Case: I'm trying execute a query in C# with MySql.Data.MySqlClient, which containts INSERT INTO, i'm getting an error(see below):


Database table:

Database table

What im trying to achieve: I want to insert into a new record with: "value_id(Auto Increment)", "machine_id" "tag_name", "int_value", "real_value" and "bool_value";

I have the following code:

*Retrieving machine ID

private void getMachineID()
string connStr = "";
string ipAdressID = "'" + machineIP + "'";
string basicQueryID = "SELECT machine_id FROM machine WHERE machine.machine_ip LIKE ";
string totalQueryID = basicQueryID + ipAdressID;

//Create connection
MySqlConnection conn = new MySqlConnection(connStr);
//Query command
MySqlCommand cmd = conn.CreateCommand();
//Assign string to query
cmd.CommandText = totalQueryID;
//Open connection
//Get result ID from machine where IP adress = machineIP and write to machineID variable.
machineID = (int)(cmd.ExecuteScalar());

Code to insert into the record:

string connStr = "";
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "INSERT INTO waardes(machine_id, tag_name, int_value, real_value, bool_value) VALUES(@machineID, @tagName, @intValue, @doubleValue, @boolValue)";

cmd.Parameters.AddWithValue("@tagName", tagName);
cmd.Parameters.AddWithValue("@intValue", intValue);
cmd.Parameters.AddWithValue("@doubleValue", doubleValue);
cmd.Parameters.AddWithValue("@boolValue", boolValue);
cmd.Parameters.AddWithValue("@machineID", machineID);
catch (Exception ex)

Hope you guys can help!

Answer Source

There's no INSERT with where clause.

Take a look at this question: How to insert with where clause

Maybe, you can create an IF inside your app to verify your parameters before executing the INSERT statement.

Based on your query, I believe you need an UPDATE:

UPDATE waardes SET tag_name = @tagName,
  int_value = @intValue, real_value = @doubleValue,
  bool_value = @boolValue WHERE machine.machine_id LIKE %1%

Is this what you need?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download