?
Solved

c# and Access

Posted on 2015-02-20
10
Medium Priority
?
109 Views
Last Modified: 2015-02-20
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
Comment
Question by:MrMay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 1000 total points
ID: 40621588
Access uses # symbol as date identifier.
('"+label9.Text+"','"+label13.Text+"','"+label10.Text+"','"+label11.Text+"',#"+label1.Text+"#  )";

Try that.
OM Gang
0
 

Author Comment

by:MrMay
ID: 40621592
i tried that omgang... still no good.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40621595
You removed the apostrophes and replaced with pound signs, correct?
OM Gang
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:MrMay
ID: 40621600
yes
0
 

Author Comment

by:MrMay
ID: 40621601
command.CommandText = "Insert into GlassDefectData (Line, Shift, AreaofConcern, GlassDefect, DateofDefect) values ('"+label9.Text+"','"+label13.Text+"','"+label10.Text+"','"+label11.Text+"',#"+label1.Text+"#  )";
0
 

Author Comment

by:MrMay
ID: 40621611
see attached for error msg.
Capture.JPG
0
 
LVL 28

Expert Comment

by:omgang
ID: 40621634
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
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 1000 total points
ID: 40621637
Can you format this ... label1.Text = DateTime.Today.ToLongDateString();

To

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


ET
0
 

Author Comment

by:MrMay
ID: 40621656
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
 
LVL 34

Expert Comment

by:it_saige
ID: 40621663
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question