Input string was not in a correct format error in MySQL query

Question

Hi I am working on an application which works with MySQL database and C# 4.0.

I have below method to update a MySQL table. But while executing the below method I am getting error that Input string was not in a correct format.

Method call:

UpdateOnline(mcon, convertedXml, fileName, stmId);

Method definition:

private void UpdateOnline(MySqlConnection mcon, string convertedXml, string fileName,int stmtId)
    {
        var cmd = new MySqlCommand("update user_account_statement set statement_xml=@xml,"+
            " file_name=@fName, status='closed' where statement_id=@stmtId",mcon);
        cmd.Parameters.AddWithValue("@xml", MySqlDbType.LongText).Value = convertedXml;
        cmd.Parameters.AddWithValue("@fName", MySqlDbType.VarChar).Value = fileName;
        cmd.Parameters.AddWithValue("@stmtId", MySqlDbType.Int32).Value=stmtId;
        var query = cmd.ToString();
        cmd.ExecuteNonQuery();
    }

I checked many times for data types and all but not able to figure it out that where I am missing.

UPDATE

Table schema is below:

CREATE TABLE `user_account_statement` (
`statement_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`statement_xml` longtext NOT NULL,
`status` varchar(10) NOT NULL,
`file_name` varchar(60) NOT NULL,
PRIMARY KEY (`statement_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1084 DEFAULT CHARSET=latin1;

Are you sure your column types are right? Did you debug your code? What are the value of your parameters? You didn't use convertedStmt and query anywhere in your code. And change your @fName to @fileName — Soner Gönül
– Soner Gönül, Commented Nov 3, 2014 at 12:31
@SonerGönül I have updated the table schema and code. please advise — Ashok
– Ashok, Commented Nov 4, 2014 at 11:51

Jan Lyndon Jasa · Accepted Answer · 2014-11-03 09:02:57Z

1

take a look at your parameters bud . . in your query string it says, @xml, @fName, @stmtId

then the lines with AddWithValue, your code says @xml, @fileName, @stmtId

answered Nov 3, 2014 at 9:02

Jan Lyndon Jasa

1621 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jan Lyndon Jasa Over a year ago

how about trying this instead cmd.Parameters.AddWithValue("@xml", convertedXml); just do the same with the other 2

Ashok · Accepted Answer · 2014-12-26 06:13:50Z

Below code worked for me. :

 private void UpdateOnline(MySqlConnection mcon, string convertedXml, MemoryStream statment, string fileName, int stmtId)
    {
        var convertedStmt = statment.ToArray();
        var cmd = new MySqlCommand("update user_account_statement set" +
         " file_name=@fName,statement_file=@stmt, statement_xml=@xml, status='closed' where statement_id=@stmtId", mcon);
        cmd.Parameters.Add("@fName", MySqlDbType.VarChar);
        cmd.Parameters.Add("@stmtId", MySqlDbType.UInt32);
        cmd.Parameters.Add("@xml", MySqlDbType.LongText);
        cmd.Parameters.Add("@stmt", MySqlDbType.LongBlob);
        cmd.Parameters["@fName"].Value = fileName;
        cmd.Parameters["@stmtId"].Value = stmtId;
        cmd.Parameters["@xml"].Value = convertedXml;
        cmd.Parameters["@stmt"].Value = convertedStmt;
        cmd.CommandTimeout = 1000;
        cmd.ExecuteNonQuery();

    }

Narender Kumar · Accepted Answer · 2025-01-01 10:22:46Z

0

I was also facing the same issue. It has solved with DataType I was passing was incorrect.

Instaed of MySqlDbType.. I was using - SqlDbType.

var SqlParameters = new List() { new MySqlParameter("@TableName", MySqlDbType.VarChar){ Value = sqlTable.TableName }, new MySqlParameter("@ColumnName", MySqlDbType.VarChar){ Value = string.Join(",", sqlTable.ColumnsName) }, new MySqlParameter("@ColumnValue", MySqlDbType.VarChar){ Value = string.Join(",", sqlTable.ColumnsValue.Select(x=> $"'{x}'")) }, };

answered Jan 1 at 10:22

Narender Kumar

1291 silver badge10 bronze badges

Collectives™ on Stack Overflow

Input string was not in a correct format error in MySQL query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related