Forcing a 0 before each entry

[phoenix_2000]

Hello,

I'm currently working on a database application where the application can show a number of risks and counter-measures.
Now i'm updating the application so it is possible to upload new risks without having to open the database.
However, each risk has an riskID, a number that was being put in a string, because it had to be exactly 4 characters long.
so there's riskID 0001, 0002,...,0020 etc.
Those risks were put in by hand, but now everytime i try to insert a risk into the database, it cuts away the zeroes.
(even if it has them in the SQL string).

Is there any way i can force the database to force the database to put the zeroes there?
i'm willing to do some digging to see if converting it to an int hurts, if that's necessary

[pritaeas]

If the string you're inserting has the zeroes, but the column doesn't, then perhaps there is a trigger removing them on insert (assuming your column is of type string/char).

[phoenix_2000]

hmm, good suggestion, but i haven't found any trigger that does that (or actually, any trigger at all)
it DOES automatically remove the zero's when i convert the row to integers, so that's not an option either, i guess.

Some extra info: it's an MSSQL database managed using microsoft access 2010. the row risicoID is an text field (litteraly translated, this version of access is in dutch)
My SQL string is

INSERT INTO HSE_TRA_risico_nieuw (risico, maatregelen, risicoID, formtype) VALUES ('111', '222', 0097, 'entree')

It even cuts them away when i execute the SQL from access, so i'm starting to suspect access itself is the culprit.
Unfortunatly, my database-ing skill is... lacking :)

[adam_k]

put single quotes (') around 0097 and you'll be fine.
By using 0097 like this you are not handling it as a string, but as a number - so the 0s don't belong there and get truncated

[phoenix_2000]

Yup, that did it.
Thanks guys, i don't know how i could've missed that stupid part, i feel like hitting myself now :)