ms-Access is removing leading zeros from a string

Question

In the below code, the value of skuSet.Fields(0), which is a text field in the database, is "000000002".

If skuSet.RecordCount = 0 Then
   sql = "SELECT Stock_Code FROM Stock WHERE Stock_Code = '" & stkLine.StockCode & "'"
   Set skuSet = dbStock.OpenRecordset(sql, dbOpenDynaset)
End If

sql = "SELECT * FROM PickedByPicco WHERE OrderID = " & stkLine.OrderId & " AND StockCode = '" & skuSet.Fields(0) & "'"
Set SOPSLineSet = dbSOPS.OpenRecordset(sql, dbOpenDynaset)

If SOPSLineSet.RecordCount = 0 Then
    sql = "INSERT INTO PickedByPicco(OrderID, StockCode, Qty, ScannedBy, ProcessedBy, processed) VALUES(" & stkLine.OrderId & _
          ", " & skuSet.Fields(0) & ", " & stkLine.Quantity & ", '" & stkLine.UserId & "', '', False)"

dbSOPS.Execute (sql)

When I step through the code, the value in skuSet.Fields(0) is still the same, as expected.

When the query has been executed, I check the PickedByPicco table, and the StockCode field shows just a value of 2 instead, and it's removed all of the leading 0's.

Why would it be doing this and how can I stop it?

Answer 1

That's because your INSERT statement looks like this:

INSERT INTO PickedByPicco (...) VALUES (..., 000000002, ...)

when it should look like this:

INSERT INTO PickedByPicco (...) VALUES (..., '000000002', ...)

(Note the quotes around your value.)

---

Normally, I'd tell you to use parameterized SQL, because SQL creation by string concatenation is evil, but, unfortunately, DAO's Database.Execute does not support that. So, the next best thing is to properly escape strings. To do that:

• put single quotes around your value and
• escape any single quotes occurring in your value:

I.e. this line

", " & skuSet.Fields(0) & ", " & ...

becomes

", '" & Replace(skuSet.Fields(0), "'", "''") & "', " & ...

While you are at it, be sure to replace single quotes in your other strings as well! Currently, a UserId of O'Brien would break your SQL, and other values might do worse.