[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

c# and Access

Posted on 2015-02-20
10
Medium Priority
?
116 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
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 35

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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

612 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