[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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!
0
bowser17
Asked:
bowser17
  • 3
  • 3
  • 3
  • +1
1 Solution
 
Bob LearnedCommented:
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
0
 
PoeticAudioCommented:
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
0
 
PoeticAudioCommented:
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TheAvengerCommented:
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
0
 
PoeticAudioCommented:
I've used named parameters with Access before. I believe it's possible.
0
 
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...


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

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_21562838.html

Bob
0
 
bowser17Author Commented:
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.
0
 
Bob LearnedCommented:
If you want to use named parameters, then try using the OleDb data provider, and not the brain-dead ODBC provider.

Bob
0
 
bowser17Author Commented:
Using the OleDb provider allowed me to use parameters.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now