Date insert in access

Question

I am working on access but stuck in insert queries having date time. like these queries doesn't work for me.

INSERT INTO tbl_fuel_levels([genset_id], [rec_time], [fuel_level], 
[grid_electricity], [genset_electricity], [genset_number]) 
VALUES('001', '#12/12/23 18:46:38+20#', '0', 'T', 'F', '+923468280124');

INSERT INTO tbl_fuel_levels([genset_id], [rec_time], [fuel_level], 
[grid_electricity], [genset_electricity], [genset_number]) 
VALUES('001', #'12/12/23 18:46:38+20'#, '0', 'T', 'F', '+923468280124');

INSERT INTO tbl_fuel_levels([genset_id], [rec_time], [fuel_level], 
[grid_electricity], [genset_electricity], [genset_number]) 
VALUES('001', '12/12/23 18:46:38+20', '0', 'T', 'F', '+923468280124');

enter image description here

Any idea? what could be wrong.

Here is my code in c#:

con = new OleDbConnection(ConnStr);
con.Open();

cmd = con.CreateCommand();
cmd.CommandText = "INSERT INTO tbl_fuel_levels([genset_id], [rec_time], 
[fuel_level], [grid_electricity], [genset_electricity], [genset_number]) 
VALUES('" + genset_id + "', '" + rec_time + "', '" + fuel_level + "', '" +
grid_electricity + "', '" + genset_electricity + "', '" + genset_number + "');";
cmd.ExecuteNonQuery();

I have also tried this.

cmd.CommandText = "INSERT INTO tbl_fuel_levels([genset_id], [rec_time], 
[fuel_level], [grid_electricity], [genset_electricity], [genset_number]) 
VALUES('" + genset_id + "', @rec_t, '" + fuel_level + "', '" + grid_electricity 
+ "','" + genset_electricity + "', '" + genset_number + "');";
cmd.Parameters.Add("@rec_t", OleDbType.DBTimeStamp).Value 
                              = DateTime.Parse(rec_time);

All other insert and select queries are working fine

can you please show what your OLEDB Command code looks like also check the database and let us know what the datatype are setup as ' — MethodMan, Dec 23 '12 at 15:22
Is the Date field data type DateTIme or Text also does the time field matter..? — MethodMan, Dec 23 '12 at 15:24
Please show more code want to make sure you are using OleDB connection correctly.. — MethodMan, Dec 23 '12 at 15:27
what does the date look like in the table can you show that, want to make sure we are giving you the correct format.. — MethodMan, Dec 23 '12 at 15:42
If you want to store milliseconds in a date/time field you need to pass the value as a double. Presumably that's the problem you're having. — Daniel, Dec 23 '12 at 16:11
I suggest using parametrized commands as Amit Ranjan shows in his answer. If the date/time originates from a DateTime variable or property you won't have to deal with date/time formats at all. BTW, the correct date/time literal in Access queries is without quotes: `... VALUES(5, #2003-2-1 18:08:42#, 'hello')`. And use the yyyy-MM-dd format as the other can be confusing. Is `01/02/2012` `dd/MM/yyyy` Or `MM/dd/yyyy`? — Olivier Jacot-Descombes, Dec 23 '12 at 16:26

score 2 · Answer 1 · edited May 23 '17 at 11:48

2

Try this

try
{

  con = new OleDbConnection(ConnStr);
  con.Open();

  cmd = con.CreateCommand();
  cmd.CommandText = "INSERT INTO tbl_fuel_levels([genset_id], [rec_time], [fuel_level], [grid_electricity], [genset_electricity], [genset_number]) 
VALUES(?,?,?,?,?,?)";
cmd.Parameters.AddWithValue("@id",id);

cmd.Parameters.AddWithValue("@date",DateTime.Parse("12/12/2009 11:34:55"));
  cmd.ExecuteNonQuery();

}
catch (OleDBException ex)
{
  MessageBox.Show(ex.Message);
}

Update:

If you are using Access 2010, then you can also use Stored Procedures on MSAccess. See this article for more How do I make a stored procedure in MS Access?

edited May 23 '17 at 11:48

Community

1
1

answered Dec 23 '12 at 15:45

OleDB does not really use parameter names, it's index based, which is why you can pass the question mark for each one of your parameters as the name. You do have to make sure your parameters are in the same order as your query statement. – MethodMan Dec 23 '12 at 15:52
@DJKRAZE : Yes, parameters must be aligned. You are true. – Dec 24 '12 at 15:29

score 1 · Accepted Answer · answered Dec 23 '12 at 16:52

Your INSERT query has more than one problem. I copied this version from your comment to DJ:

INSERT INTO tbl_fuel_levels (
    [genset_id],
    [rec_time],
    [fuel_level],
    [grid_electricity],
    [genset_electricity],
    [genset_number]
    )
VALUES (
    '001',
    '#12/11/2023 2:46:38 PM#',
    '0',
    'T',
    'F',
    '+923468280124'
    );

The problems are:

You're attempting to insert the string, '#12/11/2023 2:46:38 PM#' into the Date/Time field rec_time. Discard the single quotes to make that value a Date/Time literal instead of a string: #12/11/2023 2:46:38 PM#
You're attempting to insert the string, 'T' into the Yes/No field grid_electricity. Use True without quotes instead.
You're attempting to insert the string, 'F' into the Yes/No field genset_electricity. This is the same issue as problem #2. Use False without quotes.

Use Access' query designer to create a new query. Switch it to SQL View and paste in this statement:

INSERT INTO tbl_fuel_levels (
    [genset_id],
    [rec_time],
    [fuel_level],
    [grid_electricity],
    [genset_electricity],
    [genset_number]
    )
VALUES (
    '001',
    #12/11/2023 2:46:38 PM#,
    '0',
    True,
    False,
    '+923468280124'
    );

If there are any remaining problems with that statement, work them out in Access before bothering with the c# code to create the statement.

Or better, use the parameters query approach. Maybe you can get that working once you resolve those issues with the Yes/No fields.

MethodMan · Answer 3 · 2012-12-23T16:05:45.090

May I suggest the following this way you can trap the Exact Exception first so we can determine what field the Insert is failing on .. I think the Date you are passing is odd try passing "MM/DD/YYY HH:MM:SS"

try
{

  con = new OleDbConnection(ConnStr);
  con.Open();

  cmd = con.CreateCommand();
  cmd.CommandText = "INSERT INTO tbl_fuel_levels([genset_id], [rec_time], [fuel_level], [grid_electricity], [genset_electricity], [genset_number]) VALUES('" + genset_id + "', '" + rec_time + "', '" + fuel_level + "', '" + grid_electricity + "', '" + genset_electricity + "', '" + genset_number + "');";
  cmd.ExecuteNonQuery();

}
catch (OleDBException ex)
{
  MessageBox.Show(ex.Message);
}

if you decide you use the AddWithParameters() method you may want to try something like this

cmd.Parameters.AddWithValue("?", DateTime.Parse("12/11/2023 2:46:38"));

or the correct way to do it would be something like this

"INSERT INTO tbl_fuel_levels[genset_id], [rec_time], [fuel_level], [grid_electricity], [genset_electricity], [genset_number])
 VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", genset_id, rec_time, fuel_level, grid_electricity, genset_electricity, genset_number)";

I would recommend creating variables and assigning the values to the variables

Create and assign variables to these values this way you don't have to have such a nasty looking insert statement with all the commas

genset_id, rec_time, fuel_level, grid_electricity, genset_electricity, genset_number

System.Data.OleDb.OleDbException: Data type mismatch in criteria expression — sharafjaffri, Dec 23 '12 at 15:48
my query INSERT INTO tbl_fuel_levels([genset_id], [rec_time], [fuel_level], [grid_electricity], [genset_electricity], [genset_number]) VALUES('001', '#12/11/2023 2:46:38 PM#', '0', 'T', 'F', '+923468280124'); — sharafjaffri, Dec 23 '12 at 15:48
you normally use # sign when doing Selects.. I don't think you need it for Date — MethodMan, Dec 23 '12 at 15:54

score 0 · Answer 4 · answered Dec 23 '12 at 16:20

0

To format a date in a MSAccess query you use just the # symbols:

#12/12/23 18:46:38+20#

answered Dec 23 '12 at 16:20

Dave Cousineau

12,154
8
64
80

Date insert in access

4 Answers4