Updating a database column while reading rows
I need to change the value of a column in my database table every time I read a row. In particular, after reading a row, I have to set the column SENT = 1 (the default is 0 before reading).
My code:
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 I ran into is:
unknown column 'ident' in where where ...
source to share
You are not using ident
as a variable in your request. You have defined it as ID=ident
why your ISP is confused and it thinks that you are actually trying to filter where the column ID
value is equal to the column value ident
.
What if it ident
is a symbol? Then it will be defined as ID = 'ident'
, right?
Define a parameter for it and then add it.
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 place your connections, commands and readers.
source to share