CODEWITHSUNDEEP
c#mysqlsqlpost
Rodrigo Zimmermann
Rodrigo Zimmermann

Reputation: 157

C# MySqlDataAdapter not update data in Database

My procedure to update data in Mysql database using c# is this

        cQuoteData quoteData = new cQuoteData();
        DateTime? startDate = Convert.ToDateTime("01/01/1900");
        if (pQuoteData.Length > 0)
        {
            quoteData = pQuoteData[0];
            startDate = quoteData.datetime;
        }
        string sTableName = "Quotes" + pTimeFrame;
        DataTable vDataTable = new DataTable();

        MySqlCommand sqlCommand = new MySqlCommand("select symbol,date,openprice from " + sTableName + " where date >= @date and symbol = @symbol order by date",
                    rzSqlConnection.connection());
        sqlCommand.Parameters.AddWithValue("@date", startDate);
        sqlCommand.Parameters.AddWithValue("@symbol", pSymbol);


        MySqlDataAdapter cdataAdapter = new MySqlDataAdapter(sqlCommand);
        DataColumn[] keys = new DataColumn[0];
        keys = new DataColumn[1];
        vDataTable.PrimaryKey = keys;
        cdataAdapter.Fill(vDataTable);

        foreach (DataRow row in vDataTable.Rows)
        {
            row["openPrice"] = Convert.ToDouble(row["openPrice"]) * -1;
        }


        MySqlCommandBuilder vSqlCommand = new MySqlCommandBuilder(cdataAdapter);

        cdataAdapter.UpdateCommand = vSqlCommand.GetUpdateCommand();


        cdataAdapter.Update(vDataTable);

        return true;

the struct of table is this

> CREATE TABLE `quotesd1` (
  `symbol` VARCHAR(40) COLLATE latin1_swedish_ci NOT NULL,
  `date` DATETIME NOT NULL,
  `openPrice` FLOAT NOT NULL,
  `highPrice` FLOAT DEFAULT NULL,
  `lowPrice` FLOAT DEFAULT NULL,
  `close` FLOAT DEFAULT NULL,
  `volume` FLOAT DEFAULT NULL,
  `m4` FLOAT DEFAULT NULL,
  `m5` FLOAT DEFAULT NULL,
  `m17` FLOAT DEFAULT NULL,
  `m21` FLOAT DEFAULT NULL,
  `m50` FLOAT DEFAULT NULL,
  `m72` FLOAT DEFAULT NULL,
  `m305` FLOAT DEFAULT NULL,
  **PRIMARY KEY USING BTREE (`symbol`, `date`),**
  KEY `symbol` USING BTREE (`symbol`),
  KEY `quotesd1_idx1` USING BTREE (`date`),
  CONSTRAINT `quotesd1_fk1` FOREIGN KEY (`symbol`) REFERENCES `symbols` (`symbol`)
) ENGINE=InnoDB
CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';

the update command generate by MySqlCommandBuilder 

"UPDATE `quotesd1` SET `symbol` = @p1, `date` = @p2, `openPrice` = @p3 WHERE ((`symbol` = @p4) AND (`date` = @p5) AND (`openPrice` = @p6))"

and above is the problem where clause say

WHERE ((`symbol` = @p4) AND (`date` = @p5) **AND (`openPrice` = @p6))"**

but the update clause just need select update values by primary keys

This is MysqlAdapter Bug or I make some think wrong ??

Detail if I specify an update parameter manually this update works fine but I need do this to every update ??

Upvotes: 0

Views: 602

Answers (1)

Mad Myche
Mad Myche

Reputation: 1075

Your fields and tablename are incorrect, no need to have them back-ticked References: MySql Developer, Tutorials Point

"UPDATE quotesd1   SET symbol = @p1, date = @p2, openPrice = @p3   WHERE (symbol = @p4) AND (date = @p5) AND (openPrice = @p6)"

Upvotes: 1

Related Questions