asked on

Insert record into Access Database

Hi-

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;
                  cmd.Parameters.Add(param);
                  cmd.ExecuteNonQuery();

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.

Thanks!

Bob Learned

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

using System.Windows.Forms;

...

try
{
// Put your code here.
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

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

Bob

PoeticAudio

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

PoeticAudio

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?

TheAvenger

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

PoeticAudio

I've used named parameters with Access before. I believe it's possible.

bowser17

ASKER

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...

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bowser17

ASKER

Bob-

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 Learned

If you want to use named parameters, then try using the OleDb data provider, and not the brain-dead ODBC provider.

Bob

bowser17

ASKER

Using the OleDb provider allowed me to use parameters.