• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

c# and Access

Can someone please tell me how to save a date into an access database?
Let me explain;
I have a table within an access database.  In one table I have a Field "DateofDefect" with data-type of "Date/Time".
 
In my c# application I have the current date being displayed in a label. How do I save that date into this access database?

here are snips of my code;

  label1.Text = DateTime.Today.ToLongDateString();

  command.CommandText = "Insert into GlassDefectData (Line, Shift, AreaofConcern, GlassDefect, DateofDefect) values ('"+label9.Text+"','"+label13.Text+"','"+label10.Text+"','"+label11.Text+"','"+label1.Text+"'  )";
           
0
MrMay
Asked:
MrMay
2 Solutions
 
omgangIT ManagerCommented:
Access uses # symbol as date identifier.
('"+label9.Text+"','"+label13.Text+"','"+label10.Text+"','"+label11.Text+"',#"+label1.Text+"#  )";

Try that.
OM Gang
0
 
MrMayAuthor Commented:
i tried that omgang... still no good.
0
 
omgangIT ManagerCommented:
You removed the apostrophes and replaced with pound signs, correct?
OM Gang
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
MrMayAuthor Commented:
yes
0
 
MrMayAuthor Commented:
command.CommandText = "Insert into GlassDefectData (Line, Shift, AreaofConcern, GlassDefect, DateofDefect) values ('"+label9.Text+"','"+label13.Text+"','"+label10.Text+"','"+label11.Text+"',#"+label1.Text+"#  )";
0
 
MrMayAuthor Commented:
see attached for error msg.
Capture.JPG
0
 
omgangIT ManagerCommented:
I am setting up a test project in VS 2013 with an Access db connection ... but the error message seems to indicate it's a format issue.  Try converting/casting the value of label1.Text to ShortDate.  Maybe Access doesn't recognize the formatting coming in.
OM Gang
0
 
Eric ShermanAccountant/DeveloperCommented:
Can you format this ... label1.Text = DateTime.Today.ToLongDateString();

To

label1.Text = DateTime.Today.MM/DD/YYYY


ET
0
 
MrMayAuthor Commented:
you are all correct.. looks like a format issue.
if I do
label1.Text = DateTime.Today.ToLongDateString();  
It crashes.
but if i change it to
 label1.Text = DateTime.Today.ToShortDateString();    
it works.
thank you all for your input...    :-)
0
 
it_saigeDeveloperCommented:
I prefer to use string.Format when doing string concatenation operations:
command.CommandText = string.Format("Insert into GlassDefectData (Line, Shift, AreaofConcern, GlassDefect, DateofDefect) values ('{0}','{1}','{2}','{3}',#{4}#)", label9.Text, label13.Text, label10.Text, label11.Text, label1.Text);

Open in new window

Still others would recommend not using a string literal, but rather a parameter based insert:
	command.CommandText = @"Insert into GlassDefectData (Line, Shift, AreaofConcern, GlassDefect, DateofDefect) values (@Line, @Shift, @AreaofConcern, @GlassDefect, @DateofDefect)";
command.Parameters.AddWithValue("@Line", label9.Text);
command.Parameters.AddWithValue("@Shift", label13.Text);
command.Parameters.AddWithValue("@AreaofConcern", label10.Text);
command.Parameters.AddWithValue("@GlassDefect", label11.Text);
command.Parameters.AddWithValue("@DateofDefect", label1.Text);

Open in new window

However, I suspect that your problem could be related to conversion (depending upon if your DateofDefect allows for nulls):
DateTime? temp = new DateTime();
command.CommandText = string.Format("Insert into GlassDefectData (Line, Shift, AreaofConcern, GlassDefect, DateofDefect) values ('{0}','{1}','{2}','{3}',#{4}#)", label9.Text, label13.Text, label10.Text, label11.Text, DateTime.TryParse(label1.Text, temp) ? temp.Value.ToShortDateString() : null);

Open in new window

And for those that recommend parameter based inserts:
DateTime? temp = new DateTime();
command.CommandText = @"Insert into GlassDefectData (Line, Shift, AreaofConcern, GlassDefect, DateofDefect) values (@Line, @Shift, @AreaofConcern, @GlassDefect, @DateofDefect)";
command.Parameters.AddWithValue("@Line", label9.Text);
command.Parameters.AddWithValue("@Shift", label13.Text);
command.Parameters.AddWithValue("@AreaofConcern", label10.Text);
command.Parameters.AddWithValue("@GlassDefect", label11.Text);
command.Parameters.AddWithValue("@DateofDefect", DateTime.TryParse(label1.Text, temp) ? temp.Value.ToShortDateString() : null);

Open in new window


-saige-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now