Weird issue with casting substring to integer

Question

I'm getting a weird issue with substringing. Apparently the string I get can't be cast into an Int32 for some odd reason. The error message I get when I try doing that is "input string is not in correct format". Because of this, I can't insert these values into the Database either.

Here's the code...

string width = GetMetadata(filename, 162); //returns "1280 pixels"
string height = GetMetadata(filename, 164); //returns "700 pixels"
width = width.Substring(0, width.IndexOf(' ')); //returns "1280"
height = height.Substring(0, height.IndexOf(' ')); //returns "700"

//test: "System.Convert.ToInt32(width)" will fail, giving error "input string was not in correct format"
//doing the above on "width" yields the same result

//fails, giving error "no such column: 1280" (underlying database is sqlite)            
Database.NonQuery("INSERT INTO image VALUES (" + fileid + ", " + width + ", " + height + ")"); 

Answer 1

For all the normal reasons - primarily avoiding leaving data conversions to the database, and preventing SQL injection attacks - I would suggest that you perform the parsing to a number in C#, and then use a parameterized query to talk to SQLite.

In this case, that will make it a lot easier to debug - either .NET will fail to parse the string as well (in which case it's likely to be a problem with your data) or it will work, and you won't need to worry about what conversions database was performing.

EDIT: I've just seen your comment saying that Convert.ToInt32 fails as well. That's a pretty clear indication that it's the data which is causing a problem.

I'd expect your code to look something like this:

string widthText = GetMetadata(filename, 162);
string heightText = GetMetadata(filename, 164);
widthText = width.Substring(0, width.IndexOf(' ')).Trim();
heightText = height.Substring(0, height.IndexOf(' ')).Trim();

int width = int.Parse(widthText, CulutureInfo.InvariantCulture);
int height = int.Parse(widthText, CulutureInfo.InvariantCulture);

using (SQLiteCommand cmd = Database.CreateCommand())
{
    cmd.CommandText = "INSERT INTO image VALUES (?, ?, ?)";
    cmd.Parameters.Add(fileid);
    cmd.Parameters.Add(width);
    cmd.Parameters.Add(height);
    cmd.ExecuteNonQuery();
}

Note that the Trim call will remove any leading spaces, which it seems was the cause of the problem.