|
How can I work with Date/Time field of Access Database in C#?
I need to read these feilds of a row and show it in messagebox. Also I need to add new rows to a table in access and fill these fields for it.
Sourie
|
|
|
|
|
A connection string for you is:
Data Source=the complete file path\somefile.mdb;Persist Security Info=False;
Use connectionstrings.com for more.
As for the rest a simple introduction into the world of ADO.NET will get you started. There are plenty of articles on CP that deal with that.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.
|
|
|
|
|
Not sure if your question was about this, but if you're having a problem with date literals, as I recall, it uses format #mm/dd/yyyy#. For example #09/23/2008#.
The need to optimize rises from a bad design
|
|
|
|
|
thanks for your reply.
I need to have to seperate fields : Date and Time, but when I want to define columns in Access, I only see a field : Date/Time.
I do not know how to write from/read into this field in a c# program.
(I want to read time and date from two different textBoxs and save them in related field in a Access table. and also I want to read Date and Time field from access and show them in a MessageBox.)
Sourie
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
You're welcome Happy coding!
The need to optimize rises from a bad design
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Thanks for your suggestion 
Sourie
|
|
|
|
|
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");
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.
|
|
|
|
|
Have a good day all ...
I wonder if there is an easy way to design a printable page by C#
with out writing over 100+ line of code
in the Event Document_PrintPage(object sender, PrintPageEventArgs e)
It's really a very hard way ........ to tidy up a small table , with a few words ..... on the printing page
and if any one , Know a good .Net Component or Library .... I would be happy to hear about it ....
( Yes I try Crystal report ....) but I need more , easy , and flexible , Designer for my page
thank you in advance ....
Kind regards ......
I know nothing , I know nothing ...
|
|
|
|
|
Perhaps this question would be better in .NET Framework [^]-forum.
However, have a look at Reporting Services included in .Net Framework.
The need to optimize rises from a bad design
|
|
|
|
|
My code creates textboxes based on data from a file. I need to be able to retreive the text from each dynamically-created textbox. By the way, each textbox is contained in a splitcontainer control (spc_Questions).
Here is the code that creates each textbox:
for (cntr = 0; cntr <; NumQ; cntr++)<br />
{<br />
input = sr.ReadLine(); <br />
newTextBox = new System.Windows.Forms.TextBox();<br />
<br />
newTextBox.Size = new Size(25, newTextBox.Size.Height);<br />
newTextBox.MaxLength = 1;<br />
newTextBox.CharacterCasing = CharacterCasing.Upper;<br />
newTextBox.TabStop = true;<br />
newTextBox.TabIndex = cntr;<br />
<br />
newTextBox.Location = new Point(spc_Questions.Panel1.Right - newTextBox.Size.Width - 10,<br />
vHeight);<br />
<br />
newTextBox.Tag = cntr.ToString();<br />
<br />
newTextBox.Show();<br />
<br />
spc_Questions.Panel1.Controls.Add(newTextBox);<br />
<br />
<br />
<br />
}
Any help would be appreciated.
|
|
|
|
|
danielhasdibs wrote: Any help would be appreciated.
Im sure it would be, but you forgot to ask a question! 
|
|
|
|
|
|
you need to set the TabIndex Property
Don't be overcome by evil, but overcome evil with good
|
|
|
|
|
It's set after I create the text box.
newTextBox.TabIndex = cntr;
I've been trying to use the SelectNextControl() method to get to each textbox control, but it keeps selecting the splitcontainer instead of the textboxes.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
