Working with "Date/Time" field of "Access Database" in C#

[Sourie]

This is my code: I reach an error: Syntax error in INSERT INTO statement. I know this error is because of line : dr["Date"] = Convert.ToDateTime(DateTime.Now.ToShortDateString()); But I donno how to solve it. I have a field named "Date", with datatype "DateTime", and with format "ShortDate". OleDbDataAdapter adp = new OleDbDataAdapter(); OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=f:\Wire1\WireDb.mdb"); conn.Close(); OleDbCommand cmd = new OleDbCommand("SELECT * FROM tbl1 ORDER BY Code", conn); adp.SelectCommand = cmd; OleDbCommandBuilder cb = new OleDbCommandBuilder(adp); DataSet ds = new DataSet("tbl1"); DataTable dt = new DataTable(); conn.Open(); adp.Fill(ds, "tbl1"); dt = ds.Tables[0]; DataRow dr = ds.Tables[0].NewRow(); int x = dt.Rows.Count; dr["Code"] = ++x; dr["Street"] = "Molavi"; dr["Alley"] = "Molavi 34"; dr["Status"] = true; dr["Date"] = Convert.ToDateTime(DateTime.Now.ToShortDateString()); ds.Tables[0].Rows.Add(dr); adp.Update(dt); ds.Clear(); adp.Fill(ds.Tables[0]); conn.Close();

Sourie

[Wendelius]

The INSERT statement wasn't included in your post (only SELECT), but I believe that there are two possible reasons - Access or OleDbConnection doesn't like if the field name is Date (may be a reserved word). Tru using angle brackets (for example INSERT INTO TableName (Field1, Field2, ..., [Date]) VALUES (...)) or chcange the name of the field in the db. - dr["Date"] has a bad value. Use debugger to view it's value and to see if it looks like what you would suspect Hope this helps, Mika

The need to optimize rises from a bad design

[Sourie]

Oh my God, It is working now. Thanks alot Mika, You are right "Date is a keyword for MS Access, I changed it to DateVal, and my code finally worked. :) Tnx alot

Sourie

[Wendelius]

You're welcome :) Happy coding!

The need to optimize rises from a bad design

[Sourie]

I got another problem. I created similar table in my database and I named it as tbl2. I tryed the code for this table, but it is not working.

Sourie

[Wendelius]

If it's working for the first table, double check the code for second one. If still having problems I think you should open a new thread and provide info about the problem (error message, relevant code etc..) especially if the problem is somehow different. If I'm still awake, I'll try to check it also. Also I think you would get more answers if you would post on .Net Framework forum (not sure if that's the correct one though). The problem in this question was related to a class defined in framework but also to Access database so perhaps SQL forum would do just as fine. Mika

The need to optimize rises from a bad design

[Sourie]

I am sorry it was like the previous problem. Because in the second table of my database, I defined a field named: "No". It was also a keyword for MS ACCESS. Thank you so much for helping me. Kiss your child

Sourie

[Wendelius]

You're welcome :) There are plenty of reserved words so you'll encounter this problem once in a while. If you want, you can test using angle brackets. At least in SQL Server they make it possible to use reserved words and even spaces. When defining or using a field you can surround it with brackets, for example: - [No] - [Yes] - [Field name with space] and then for example "SELECT [No], [Field name with space] FROM [TableName] WHERE [Yes] = ..." Mika

The need to optimize rises from a bad design

[Sourie]

Thanks for your suggestion :)

Sourie

[meenana]

Dears, I'm having a serious problem making me MAD! I'm searching for days over google but no use I'm trying to get data from access where date column in access = datetime selected from textbox in C# CODE: it is one line code which is not working DateTime d = DateTime.Parse("1/1/1");//while debugging : d=1/1/2010 12:00:00 AM sql="select * from MYTABLE where COLUMNDATE = d"; COLUMNDATE is defined as general date in MYTABLE IN access. and then when I execute the sql (i.e: datareader.executeReader) command with C#, it is giving mismatch data type. PLZ PLZ PLZ PLZ try to help me!!! THANKS IN ADVANCE!!

[RaviRanjanKr]

Hey you need to write

DateTime d = DateTime.Parse("1/1/1");//while debugging : d=1/1/2001 12:00:00 AM
sql="select * from MYTABLE where COLUMNDATE = '"+d.ToShortDateString()+"'";

because of d.ToShortDateString() value of d will be only 1/1/2001 which is same as general date as MyTable in Access.