CODEWITHSUNDEEP
c#databasesqlite
mkautzm
mkautzm

Reputation: 1146

SQLite Not preserving my leading zeroes in a prepared statement

I'm having a hard time getting leading zeroes to stick.

I have a prepared statement that looks like this:

SQLiteCommand insertSQL = new SQLiteCommand(@"INSERT INTO Inventory(
            AssetTag, Vendor, Device, Attribute, DeviceType, System, Location, OnLoan, Notes, LastModifiedTime, LastModifiedPerson, IsDeleted)
            VALUES (@AssetTagParam, @VendorParam, @DeviceParam, @AttributeParam, @DeviceTypeParam, @SystemParam, @LocationParam, @OnLoanParam, @NotesParam, @LastModifiedTimeParam, @LastModifiedPersonParam, @IsDeletedParam)", conn);

The field in question that often has leading zeroes is 'System'. It's built with this line:

var SystemParam = new SQLiteParameter("@SystemParam", System.Data.DbType.String) { Value = item.System };

If I enter '0159' into the textbox that Item.System pulls from, At runtime I did confirm that Item.System does indeed hold the value of '0159' at the time that the SQL statement is being prepared. This makes me think that type, I cast it to in the DB, System.Data.DbType.String, is at fault here. Entering data directly into the DB with SQLiteStudio preserves the leading zeros.

I'm not sure where to go from here. There isn't another type in the System.Data.DbType that is a higher abstraction of text for me to try. I'm not really sure where to go from here. How can I preserve my leading zeroes here?

Edit: Type Clarification

var AssetTagParam = new SQLiteParameter("@AssetTagParam", System.Data.DbType.Int32) { Value = item.AssetTag };
var VendorParam = new SQLiteParameter("@VendorParam", System.Data.DbType.String) { Value = item.Vendor };
var DeviceParam = new SQLiteParameter("@DeviceParam", System.Data.DbType.String) { Value = item.Device };
var AttributeParam = new SQLiteParameter("@AttributeParam", System.Data.DbType.String) { Value = item.Attribute };
var DeviceTypeParam = new SQLiteParameter("@DeviceTypeParam", System.Data.DbType.String) { Value = item.DeviceType };
var SystemParam = new SQLiteParameter("@SystemParam", System.Data.DbType.String) { Value = item.System };
var LocationParam = new SQLiteParameter("@LocationParam", System.Data.DbType.String) { Value = item.Location };
var OnLoanParam = new SQLiteParameter("@OnLoanParam", System.Data.DbType.Boolean) { Value = item.OnLoan };
var NotesParam = new SQLiteParameter("@NotesParam", System.Data.DbType.String) { Value = item.Notes };
var LastModifiedTimeParam = new SQLiteParameter("@LastModifiedTimeParam", System.Data.DbType.String) { Value = item.LastModifiedTime };
var LastModifiedPersonParam = new SQLiteParameter("@LastModifiedPersonParam", System.Data.DbType.String) { Value = item.LastModifiedPerson };
var IsDeletedParam = new SQLiteParameter("@IsDeletedParam", System.Data.DbType.Boolean) { Value = item.IsDeleted };

On the DB side, all fields are type STRING except AssetTag and AssetID, which are INTS. OnLoan and IsDeleted are BOOLEAN.

Upvotes: 2

Views: 1211

Answers (1)

leonbloy
leonbloy

Reputation: 75926

Probably you are falling into the same trap described in this thread.

Briefly, SQLite does not understand the "STRING" datatype, and in that case it assumes a default NUMERIC "affinity" (remember that, contrarily to most databases, SQLite does not have column types, only a handful of 'affinities').

Try recreating the table with the correct ("TEXT") specifier.

Upvotes: 3

Related Questions