Insert record into Access Database


this is so simple, but i keep getting "System Error".  I have a simple multi-column table and i want to insert some file information into it.

      temp = "INSERT INTO Versions ([Date],FileID,StorageLocation,ArchiveSize,LocalVersion,LastWriteTick)" +
                        " VALUES ('" + fi.LastWriteTime.ToString() +
                        "','" + fileID.ToString() +
                        "',@filename" + //filename +
                        ",'" + fi.Length.ToString() +
                        "','" + localVersion.ToString() +
                        "','" + fi2.LastWriteTime.Ticks.ToString() +
                  cmd = new OdbcCommand(temp,cn);
                  System.Data.Odbc.OdbcParameter param = new OdbcParameter("@filename",OdbcType.Text,filename.Length);
                  param.Value = filename;

I wanted to use a parameter because I filename could potentially have single quote in it, which would mess up my commandtext.

I am also using the odbc because in the future i want to allow MSDE as well as Access.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
In order to get a better exception, you need to add structure exception handling:

using System.Windows.Forms;


  // Put your code here.
catch (Exception ex)

You don't need to use ODBC.  There is a much better way of creating generic data access layers, than using ODBC.

So you're converting everything to a string before you write it to the database... are all the types in the DB defined as strings as well?l? In most situations that is not a best practice and may be a part of this issue. I would make sure that the types in the DB are correct (ie: store dates as datetimes and such) and I would paramaterize everything without converting everything to a string.

I would definitely do what Bob says and at least throw that code into some sort of exception handling routine so you can get a better idea of the nature of this error, perhaps you could post what exception is being thrown and we might be able to figure out the problem.

- Steve
Oops, didn't finish before I accidently submitted...

Since you are using single quotes around everything in your query, the DBMS is assuming that everything will be stored as a string. Usually something like FileID, ArchiveSize...etc would be some sort of numeric type and Date, LastWriteTick...etc would usually be a DateTime type, but your attempting to store everything as a string, is this your intention?
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Access does not work with named parameters. You need to replace @filename in your command with question mark: ?. If you have multiple parameters be sure to add them to the Parameters collection in the correct order
I've used named parameters with Access before. I believe it's possible.
bowser17Author Commented:
Thanks for the comments:

1st off, this command works when I have everything as a string, and dont try using parameters.. My concern was when a string has a single quote in it, then my command string gets messed up, however I did make 2 changes at once, and found that the System Error was generated from me trying to add too large a number into a field.  I got that straightened out, but now i get:
 System.Data.Odbc.OdbcException: ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

So how exactly does that ? thing work?  My other workaround is that I will make sure that the filename does not contain single quote, but this question is now more for informational purposes and best practices.

Thelearnedone:  Ok, so there are better ways of generic data access... fill me in please...

Bob LearnedCommented:
Here is a previously answered question that talks about generic data access layers:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bowser17Author Commented:

thanks, ALOT of data there.  I want to give you some points for that... but I would still like to see the way to do the named parameters in Access, since thats what i am using for my first release.
Bob LearnedCommented:
If you want to use named parameters, then try using the OleDb data provider, and not the brain-dead ODBC provider.

bowser17Author Commented:
Using the OleDb provider allowed me to use parameters.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.