Error while executing MySQL query

Question

Im trying to run this method but sometimes, it errors, sometimes it runs smoothly. I can't figure out what's going wrong. Any ideas?

public bool NewArchive(string appId, string fileUrl, long length, string thumbUrl)
{
    ConnectIfClosed();

    string query = "INSERT INTO archive (appid, file_url, thumb_url, length) " +
        "VALUES ('" + appId + "', '" + fileUrl + "', '" + thumbUrl + "', '" + length + "');";
    bool result;

    using (MySqlCommand cmd = new MySqlCommand(query, sqlConnector))
    {
        try
        {
            cmd.ExecuteNonQuery();
            result = true;
        }
        catch (Exception ex)
        {
            mysqlerror = ex.ToString();
            result = false;
        }
    }

    return result;
}

This is the exception I get sometimes First I thought it was to do with not disposing cmd incorrectly.. but im not sure.

MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed. ---> System.IO.EndOfStreamException: Attempted to read past the end of the stream.
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at ScrSnap.Sql.GetRowValueFromArchive(String row, String where, String wherematch) in C:\Users\Ben\Documents\Visual Studio 2010\Projects\Image software\ScrSnap 4\ScrSnap 4\Sql.cs:line 156

try debugging to see which values are being passed when it gets an error. — Euclides Mulémbwè
– Euclides Mulémbwè, Commented Nov 16, 2012 at 7:31

Manuel Zelenka · Accepted Answer · 2012-11-16 08:07:23Z

4

I think the problem here is that you share a connection object with multiple command objects (I assume that you have more sqls in your program than just this one ;) ). Unless they are all involved in a single transaction please avoid doing this.

answered Nov 16, 2012 at 8:07

Manuel Zelenka

1,6342 gold badges12 silver badges26 bronze badges

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

Comments

DRapp · Accepted Answer · 2012-11-16 08:12:11Z

couple things... "ID" columns, and probably length too are numeric fields and should NOT be enclosed in quote as you are building the string. Additionally, by building a string for the SQL would be prone to SQL-Injection if you are ever web-based (or otherwise if malicious internally). Anyhow, I would strongly suggest getting used to parameterized variables to your MySQLCommand.

You have

 string query = "INSERT INTO archive (appid, file_url, thumb_url, length) " +
        "VALUES ('" + appId + "', '" + fileUrl + "', '" + thumbUrl + "', '" + length + "');";

Change to

 string query = "INSERT INTO archive (appid, file_url, thumb_url, length) " +
        "VALUES (" + appId + ", '" + fileUrl + "', '" + thumbUrl + "', " + length + ");";

Beau Grantham · Accepted Answer · 2013-10-24 01:10:21Z

0

I changed + with & on below statment , then it work fine.

string query = "INSERT INTO archive (appid, file_url, thumb_url, length) " +
        "VALUES ('" + appId + "', '" + fileUrl + "', '" + thumbUrl + "', '" + length + "');";

edited Oct 24, 2013 at 1:10

Beau Grantham

3,4565 gold badges36 silver badges43 bronze badges

answered Oct 24, 2013 at 0:45

user2913689

1

Collectives™ on Stack Overflow

Error while executing MySQL query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related