• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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