CODEWITHSUNDEEP
c#mysqlsql-updatesqldatareader
andrea pila
andrea pila

Reputation: 31

Update database column during the reading of rows

I need to change a column value in the table of my database every time I read a row. Specifically, after the reading of the row I must set the column SENT=1 (by default, before the reading, the value is 0).

My code is:

var sqlCommand = "SELECT * FROM detector_output WHERE SENT=0";
var Command = new MySqlCommand(sqlCommand, connection);
MySqlDataReader reader = Command.ExecuteReader();
while (reader.Read())
{
    var ident = reader.GetString(0);
    var SENSOR_TYPE = reader.GetString(1);
    var MSG_NAME = reader.GetString(2);
    var DATA_NAME = reader.GetString(3);
    var VALUE = reader.GetString(4);
    var TIMESTAMP = reader.GetString(5);

    var connectionUP = new MySqlConnection(cs);
    connectionUP.Open();
    var sqlCommandUPDATE = "UPDATE detector_output SET SENT=1 WHERE ID=ident";
    var CommandUpdate = new MySqlCommand(sqlCommandUPDATE, connectionUP);
    CommandUpdate.ExecuteReader();

The error that I have encountered is:

unknown column 'ident' in where clause ...

Upvotes: 3

Views: 1690

Answers (1)

Soner Gönül
Soner Gönül

Reputation: 98750

You don't use ident as a variable in your query. You defined it as ID=ident that's why your provider confuse and it thinks you actually try to filter where your ID column value is equal to ident column value.

What if ident is a character? Then, it would be defined as ID = 'ident', isn't it?

Define a parameter for it and later, add it's value.

var sqlCommandUPDATE = "UPDATE detector_output SET SENT=1 WHERE ID = @ident";
var CommandUpdate = new MySqlCommand(sqlCommandUPDATE, connectionUP);
CommandUpdate.Parameters.AddWithValue("@ident", ident);

Also use using statement to dispose your connections, commands and readers.

Upvotes: 2

Related Questions