Solved

c# and Access

Posted on 2015-02-20
10
100 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 250 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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 250 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
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…

628 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