Working with "Date/Time" field of "Access Database" in C#
-
You don't have separate data types for date and time. When you need just time portion, use date/time field with a fixed date. For example: set date for each time for 1.1.1900. When showing a time field, show only time portion. This way having the date in the field doesn't matter and it isn't shown Mika
The need to optimize rises from a bad design
Like his: //dr indicates to a row of a table in Access Database. DataRow dr = new DataRow(); // I have defined two fields of type Date/Time in a table and now I want to give them value for the current row in the table: dr["Time"] = Convert.ToDateTime(DateTime.Now.ToShortTimeString()); dr["Date"] = Convert.ToDateTime(DateTime.Now.ToShortDateString()); Is it correct?
Sourie
-
Like his: //dr indicates to a row of a table in Access Database. DataRow dr = new DataRow(); // I have defined two fields of type Date/Time in a table and now I want to give them value for the current row in the table: dr["Time"] = Convert.ToDateTime(DateTime.Now.ToShortTimeString()); dr["Date"] = Convert.ToDateTime(DateTime.Now.ToShortDateString()); Is it correct?
Sourie
Something like that. Don't have a compiler near and fon't remember what is the date portion in System.DateTime if it is not defined. If the date is too far away, it can cause problems to Access. In case Access doesn't like the date portion, you can use something like this (syntax is not correct): Convert.ToDateTime("01011900" + currentTime); Mika
The need to optimize rises from a bad design
-
Something like that. Don't have a compiler near and fon't remember what is the date portion in System.DateTime if it is not defined. If the date is too far away, it can cause problems to Access. In case Access doesn't like the date portion, you can use something like this (syntax is not correct): Convert.ToDateTime("01011900" + currentTime); Mika
The need to optimize rises from a bad design
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
-
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
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
-
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
-
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
-
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
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
-
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
-
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
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
-
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
-
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!!
-
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!!
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.