Thursday, August 16, 2012

Passing a string as a parameter in dbcommands

One of the final steps in building my first major WPF application was of course the database updates. Yes this was the final part. I prefer keeping the data retrieval User Experience designing at the start of a project itself. Makes a lot of internal programming issues so much easier. But that's a muse for later.

The more important thing is that I was seeing a bit of an odd issue with the data insertion for my system where the text based columns weren't getting updated. The code that I was using seemed fairly straight forward too.

sa.UpdateCommand.CommandText = "UPDATE Deal_Category SET CategoryName = ? , High_Cutoff = ? , Low_Cutoff = ? WHERE ROWID = ?";
sa.UpdateCommand.Parameters.Add(new SQLiteParameter(DbType.String,  category.categoryName));
sa.UpdateCommand.Parameters.Add(new SQLiteParameter(DbType.Double, category.high_cutOff));
sa.UpdateCommand.Parameters.Add(new SQLiteParameter(DbType.Double, category.low_cutOff));
sa.UpdateCommand.Parameters.Add(new SQLiteParameter(DbType.Int64, category.rowid));
sa.UpdateCommand.ExecuteNonQuery();

When the update was complete I discovered that the column had become empty. How did that happen? Turns out that if you pass a string as the parameter, it calls the overloaded method that specifies a string input to be the column name. Or at least that’s what I think it was doing since I didn’t have much time to read the documentation completely there. But the important thing was that my value was being passed in as null. After looking at the method I wanted to be using I noticed that it expected an object as an input. Shot in the dark; I cast the categoryName to an object and it worked.

Not entirely sure if this is the canonical way of doing this but thought I should share since it’s a rather unexpected error that popped up.

No comments:

Post a Comment