C# Unable to log data onto database

Question

I am trying to log everytime a search is conducted on my program. The log is located on an access database. When i try to log the name of the user and computer name i receive an error and the data does not populate on my access database. Below is the code i have any support would be greatly appreciated.

private void logdata()
    {
        string User=""; 
        string PCName="";
        DateTime now = DateTime.Now;

        User = WindowsIdentity.GetCurrent().Name.ToString();
        PCName = SystemInformation.ComputerName.ToString();


        try
        {
            string constr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\\data.accdb;Jet OLEDB:Database Password=test";
            string cmdstr = "Insert into SearchLog(Location,SearchDate,SearchTime,User,PCName)Values(@a,@b,@c,@d,@e)";

            OleDbConnection con = new OleDbConnection(constr);
            OleDbCommand com = new OleDbCommand(cmdstr, con);
            con.Open();

            com.Parameters.AddWithValue("@a", txtLocNo.Text);
            com.Parameters.AddWithValue("@b", now.ToString("d"));  
            com.Parameters.AddWithValue("@c", DateTime.Now.ToString("HH:mm:ss"));     
    com.Parameters.AddWithValue("@d", User);   
            com.Parameters.AddWithValue("@e", PCName); 


            com.ExecuteNonQuery();
            con.Close();

        }
        catch (Exception eX)

        {

            string ErrorPrompt = "Select Ok and your search will continue";
            MessageBox.Show(ErrorPrompt, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return;

        }

    }

The error I get is "Syntax error into INSERT INTO statement". Hope that helps — hex c, Aug 10 '14 at 08:02

Steve · Accepted Answer · 2014-08-10T08:19:15.537

1

USER is a keyword in MS-Access Jet SQL. If you have a field or a table with that name then you should enclose it in square brackets when passing a command text from an application.

string cmdstr = @"Insert into SearchLog(Location,SearchDate,SearchTime,[User],PCName)
                 Values(@a,@b,@c,@d,@e)";

I suggest, if this is possible, to change the name of that field to something different to avoid future errors of this kind.

Also keep in mind that AddWithValue creates the parameter with a datatype taken from the value part.
You have two fields that seems to be dates but you create a parameter of string type (ToString()).

edited Aug 10 '14 at 08:19

answered Aug 10 '14 at 08:05

Steve

213,761
22
232
286

This seemed to have fixed the problem. Would you mind elaborating as to why i need the brackets for "user"? Is user a reserved word? Thank you! – hex c Aug 10 '14 at 08:09
Yes is a reserved keyword.... http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx – Steve Aug 10 '14 at 08:13

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters. MSDN

see also https://stackoverflow.com/a/8124103/1271037

edited May 23 '17 at 12:29

Community

1
1

answered Aug 10 '14 at 08:03

dovid

6,354
3
33
73

1

Acces accepts parameters with the syntax @name (probably to ease the scaling to its big cousin Sql Server), however you are correct that they should be positioned correctly – Steve Aug 10 '14 at 08:17

C# Unable to log data onto database

2 Answers2