Insert into query with where clause

Question

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:

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
    conn.Open();
    //Get result ID from machine where IP adress = machineIP and write to machineID variable.
    machineID = (int)(cmd.ExecuteScalar());
}

Code to insert into the record:

try
{
    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);
    conn.Open();
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

Hope you guys can help!

So what is the problem? And why need a `Where` for an `insert`? Do you mean update? — Gilad Green, Oct 02 '17 at 10:49
Possible duplicate of [MySQL Insert Where query](https://stackoverflow.com/questions/485039/mysql-insert-where-query) — Equalsk, Oct 02 '17 at 10:49
AFAIK you need to use `UPDATE` query if you want to update existing records with condition i.e `WHERE` clause. — Rahul Hendawe, Oct 02 '17 at 10:52
If updating a record, the `where` clause should use an `AddWithValue` as well, not a concatinated string for the machine ID. — Alex, Oct 02 '17 at 10:54
I dont know what you mean by that @rmjoia, all my data comes from my WPF application. That means i dont want to compare other tables with eachother, i want to compare my C# variable with a row in my sql table in this case. Also i dont want to update a record, i want to store the variables(records every 10 seconds) i've got in C# in my sql database, — NielsStenden, Oct 02 '17 at 10:54
@NielsStenden I thought you wanted to insert something on one table, from other table. Apparently you want to update a record. ignore my question.. I was questioning your intent because wasn't clear to me. — rmjoia, Oct 02 '17 at 10:56
I'm sorry im totally wrong, i see the problem now, thank you guys anyway! — NielsStenden, Oct 02 '17 at 10:57

MiguelKVidal · Accepted Answer · 2017-10-02T10:54:22.813

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?

Tayyab · Answer 2 · 2017-10-02T10:59:54.707

There can be no Where clause used with Insert. However you can use Where when you are using Insert Into. In your statement I suppose you are missing the Select From before your Where clause.

Consider the example:

INSERT INTO tableNameDestination
SELECT feild(s),... 
FROM tableNameSource
WHERE Condition

In your case:

INSERT INTO waardes(tag_name, int_value, real_value, bool_value) VALUES(@tagName, @intValue, @doubleValue, @boolValue) Select field1, field2, field3, field4 from machine WHERE machine.machine_id LIKE " + "'" + machineID + "'";

score 0 · Answer 3 · answered Oct 02 '17 at 10:55

0

you cannot use where in insert statement, because insert statement is used for adding new rows to table. you better use update statement.

answered Oct 02 '17 at 10:55

sambath999

153
1
7

Insert into query with where clause

3 Answers3