Inserting Date into Access Date/Time Field

Everytime i capture a date from an asp:dropdownlist and try to insert it into an access date/time field i keep getting a Data type mismatch in criteria expression error (0x80040e07). is there a way i need to format the data before inserting?

Any help would be ace!

1    using System;
2    using System.Data;
3    using System.Data.OleDb;
4    using System.Configuration;
5    using System.Web;
6    using System.Web.Security;
7    using System.Web.UI;
8    using System.Web.UI.WebControls;
9    using System.Web.UI.WebControls.WebParts;
10   using System.Web.UI.HtmlControls;
11   
12   public partial class _Default : System.Web.UI.Page
13   {
14   	protected void Page_Load(object sender, System.EventArgs e)
15   	{
16   		if (!IsPostBack)
17   		{		
18   
19   			string validUser = (string)Session["ValidAdminUser"];
20   		
21   			if (validUser != "true")
22   				{
23   					Response.Redirect("/admin/adminlogin.aspx");
24   				}
25   			
26   			else
27   				{
28   				}
29   		}
30   	}
31   	
32   	protected void Submit_Click(object sender, System.EventArgs e)
33   	{
34   		string User_Job_Category = Job_Category.SelectedItem.ToString();
35   		string User_Job_Title = Job_Title.Text.ToString();
36   		string User_Job_Reference = Job_Reference.Text.ToString();
37   		string User_Closing_Date = start_month.SelectedItem.Value + "/" + start_day.SelectedItem.Value + "/" + start_year.SelectedItem.Value;
38   		string User_Details = Job_Details.Text.ToString();
39   		string User_Salary = Job_Salary.Text.ToString();
40   		string User_PDF = "/recruitment/app/" + PDF_Upload.FileName;
41   		string User_DOC = "/recruitment/app/" + DOC_Upload.FileName;
42   		
43   		string Save_Location = Server.MapPath("/recruitment/app/");
44   		
45   		if (PDF_Upload.HasFile)
46   			try
47   				{
48   					PDF_Upload.SaveAs(Save_Location + PDF_Upload.FileName);
49   				}
50   			
51   			catch (Exception ex)
52   				{
53   				
54   				}
55   		
56   		if (DOC_Upload.HasFile)
57   			try
58   				{
59   					DOC_Upload.SaveAs(Save_Location + DOC_Upload.FileName);
60   				}
61   			
62   			catch (Exception ex)
63   				{
64   				
65   				}
66   				
67   		string SQL = "INSERT INTO recruitment ([cat_id], [title], [reference], [closing_date], [details], [salary], [app_form_doc], [app_form_pdf]) VALUES (@User_Job_Category, @User_Job_Title, @User_Job_Reference, @User_Closing_Date, @User_Details, @User_Salary, @User_PDF, @User_DOC)";
68   		
69   		OleDbConnection conn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + Server.MapPath("/db/9487671d2f.mdb"));
70   		OleDbCommand command = new OleDbCommand(SQL, conn);
71   		
72   		command.Parameters.Add("@User_Job_Category", OleDbType.Char).Value = User_Job_Category;
73   		command.Parameters.Add("@User_Job_Title", OleDbType.Char).Value = User_Job_Title;
74   		command.Parameters.Add("@User_Job_Reference", OleDbType.Char).Value = User_Job_Reference; 
75   		command.Parameters.Add("@User_Closing_Date", OleDbType.Char).Value = User_Closing_Date;
76   		command.Parameters.Add("@User_Details", OleDbType.Char).Value = User_Details; 
77   		command.Parameters.Add("@User_Salary", OleDbType.Char).Value = User_Salary; 
78   		command.Parameters.Add("@User_PDF", OleDbType.Char).Value = User_PDF; 
79   		command.Parameters.Add("@User_DOC", OleDbType.Char).Value = User_DOC; 
80   
81   
82           conn.Open();
83   
84           command.ExecuteNonQuery();
85   
86           conn.Close();
87   		
88   		Response.Redirect("/admin/");
89   		
90   	}
91   }
92   
 
0
DrewHinde2008
6/20/2008 7:56:13 AM
asp.net.access-datasource 4679 articles. 0 followers. Follow

8 Replies
954 Views

Similar Articles

[PageSpeed] 16

Either change the data type for the User_Closing_Date to a DateTime (and update the OleDbType for the parameter), or use AddWithValue for your parameters and don't specify the parameter datatype.  The second option is easiest and more flexible, because Jet will examine the datatype of the column in Access and infer the parameter type from that.  It's a little slower (not that you would notice), but if you were really interested in Enterprise level performance, you wouldn't be using Access.

 

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
6/20/2008 10:45:03 AM

i used AddWithValue and removed the parameters datatype like this

command.Parameters.AddWithValue("@User_Job_Category").Value = User_Job_Category;

and now i get a:

CS1501: No overload for method 'AddWithValue' takes '1' arguments.

 Any ideas? Thanks in advance.

 

0
DrewHinde2008
6/20/2008 11:43:00 AM

Yep.  Read the article I linked to a little more closely... Wink

command.Parameters.AddWithValue("@User_Job_Category", User_Job_Category);

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
6/20/2008 12:04:07 PM

Thanks for your reply. Unless missing something really obvious, i cant see where you liked to an article?

0
DrewHinde2008
6/20/2008 12:42:49 PM

DrewHinde2008:
Unless missing something really obvious, i cant see where you liked to an article?
 

My mistake.  I've linked to it in 3 other posts already today.  Thought I had done so in this thread too.  I'm a buffoon.  What can I say?

http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
6/20/2008 12:54:16 PM

Hi

I have read through the link and am still getting the same error.

Could it be the way that i am capturing the date from the dropdown fields? 

Revised code is attached. Any help would, again, be greatly appreciated!

Drew

 

1    using System;
2    using System.Data;
3    using System.Data.OleDb;
4    using System.Configuration;
5    using System.Web;
6    using System.Web.Security;
7    using System.Web.UI;
8    using System.Web.UI.WebControls;
9    using System.Web.UI.WebControls.WebParts;
10   using System.Web.UI.HtmlControls;
11   
12   public partial class _Default : System.Web.UI.Page
13   {
14   	protected void Page_Load(object sender, System.EventArgs e)
15   	{
16   		if (!IsPostBack)
17   		{		
18   
19   			string validUser = (string)Session["ValidAdminUser"];
20   		
21   			if (validUser != "true")
22   				{
23   					Response.Redirect("/admin/adminlogin.aspx");
24   				}
25   			
26   			else
27   				{
28   				}
29   		}
30   	}
31   	
32   	protected void Submit_Click(object sender, System.EventArgs e)
33   	{
34   		int User_Closing_Date = Int32.Parse(start_month.SelectedItem.Value) + Int32.Parse(start_day.SelectedItem.Value) + Int32.Parse(start_year.SelectedItem.Value);
35   		
36   		string User_PDF = "/recruitment/app/" + PDF_Upload.FileName;
37   		string User_DOC = "/recruitment/app/" + DOC_Upload.FileName;
38   		
39   		string Save_Location = Server.MapPath("/recruitment/app/");
40   		
41   		if (PDF_Upload.HasFile)
42   			try
43   				{
44   					PDF_Upload.SaveAs(Save_Location + PDF_Upload.FileName);
45   				}
46   			
47   			catch (Exception ex)
48   				{
49   				
50   				}
51   		
52   		if (DOC_Upload.HasFile)
53   			try
54   				{
55   					DOC_Upload.SaveAs(Save_Location + DOC_Upload.FileName);
56   				}
57   			
58   			catch (Exception ex)
59   				{
60   				
61   				}
62   				
63   		string SQL = "INSERT INTO recruitment ([cat_id], [title], [reference], [closing_date], [details], [salary], [app_form_doc], [app_form_pdf]) VALUES (User_Job_Category, User_Job_Title, User_Job_Reference, User_Closing_Date, User_Details, User_Salary, User_PDF, User_DOC)";
64   		
65   		OleDbConnection conn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + Server.MapPath("/db/9487671d2f.mdb"));
66   		OleDbCommand command = new OleDbCommand(SQL, conn);
67   		
68   		command.CommandType = CommandType.Text;
69   		
70   		command.Parameters.AddWithValue("User_Job_Category", Job_Category.SelectedItem.ToString());
71   		command.Parameters.AddWithValue("User_Job_Title", Job_Title.Text.ToString());
72   		command.Parameters.AddWithValue("User_Job_Reference", Job_Reference.Text.ToString()); 
73   		command.Parameters.AddWithValue("User_Closing_Date", User_Closing_Date);
74   		command.Parameters.AddWithValue("User_Details", Job_Details.Text.ToString()); 
75   		command.Parameters.AddWithValue("User_Salary", Job_Salary.Text.ToString()); 
76   		command.Parameters.AddWithValue("User_PDF", User_PDF); 
77   		command.Parameters.AddWithValue("User_DOC", User_DOC); 
78   
79           conn.Open();
80   
81           command.ExecuteNonQuery();
82   
83           conn.Close();
84   		
85   		Response.Redirect("/admin/");
86   		
87   	}
88   }
89   
 
0
DrewHinde2008
6/23/2008 9:46:38 AM

Try

string User_Closing_Date = start_month.SelectedItem.Value + "/" + start_day.SelectedItem.Value + "/" + start_year.SelectedItem.Value;
 

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
6/23/2008 12:25:09 PM

Sorted. That works!

Ace, thanks for your help!

0
DrewHinde2008
6/23/2008 12:51:56 PM
Reply:

Similar Artilces:

DBI, MS Access, inserting/updating a Access Date/Time value
Hi, Does anyone have any idea what is up with Microsoft Access and its ridiculous Date/Time formatting options? It doesn't seem to matter what format I attempt to insert with. The directory and db file permissions are all Full Control. I'm using the "#" around the Date/Time format as required. The database has nothing as the Format in Design view on the General tab. The insert below has never worked once. On the up side if I run an insert that has no mention of the Date/Time field then it will just insert the record using the =Now() function as the default va...

How to insert date & time into access data field using data adapters?
If I use regular INSERT SQL query I succeed to put both date & time in Access date time field. I must use data adapters for million reasons, but for some strange reason, time in database is ignored. I saw in this forum that there is strange solution to convert date time variable to string, but that didn't work in my case. This is how .NET framework declared parameter: Me.OleDbInsertCommand17.Parameters.Add(New System.Data.OleDb.OleDbParameter("SubmissionDate", System.Data.OleDb.OleDbType.DBDate, 0, "SubmissionDate")) I tried to change parameter to DBTimeStamp, but that didn...

Retrieve Only Date from Date/Time in Ms Access
Hello,When retrieving data from an ACCESS database table, I want the generated ASP page to display only the date inputted by the user. It currently displays the date and then the time is always 12:00. Anyone have any suggestions as how I can have it display only the time, but leave the access format as Date/Time? Format the output - use {0:d} Something like:label1.text=String.Format("{0:d}",YourDate)David WierMCP/ASPInsiderASPNet101.com - where to look first!Please Vote for ASPNet101 - 'Best Community Resource'!Control Grouper - easily control properties for multipl...

Insert Date and Time Into Access...
Hello, I have an access database that contans a field called 'My_Date'.  Since I want the column to store the date and time, I made the datatype of the the column 'Date/Time'.  My insert statement looks like this: Insert Into Meeting (My_Date) Values ('01/01/01') This statement displays as 01/01/01 12:00:00AM where I query it.  How do I specify a date within the statement? I have tried something like: Insert Into Meeting (My_Date) Values ('01/01/01 10:32:23PM') But I get a datatype mismatch error.  All help is appreciated. Ok, I figured out how to insert the dat...

How to remove the date part of a Date/Time value in MS Access
I am inserting a time value into a MS Access Date/Time field and notice that MS Access does not display the date portion of the value but when I look at the same data in my programming using a TADOTable component I am seeing the date of 12/30/1899 in conjunction with my time value. I read that Access is handling this within MS Access so that when a 0.sometime value is inserted it does not display the date. How do I handle this within my Delphi program so that I can display the time only in a Date/Time type field? Jeff Howard wrote: > I am inserting a time value into a MS Access Date/T...

i can't insert date into date record with access
I can insert name to name record but i can't insert date . ther is message error : "can not INSERT INTO Table ........ " needing help urgently >> Without seeing your code, I'm just guessing, but you need to surround Access dates with "#" signs (i.e. #02/02/2004#).David...

DATE field to DATE/TIME
Hello, If the field is defined as a DATE and you do: select datefield+1 from mytable Does the ODBC API (actually ASA 9.02 backend) return a field that is DATETIME or a DATE field? It is messing up my compiled application and seems to be coming back as DATETIME. Thank you. -Robert You are correct, the value is being returned as a TIMESTAMP. You can verify this through using the EXPRTYPE() builtin function, as in select exprtype('select datefield+1 from mytable', 1) from dummy which will return TIMESTAMP. Exprtype is documented in the help. You alm...

Hidden Date Field Showing Current Date Time in Insert FormView
I have a formview insert that I need one of the fields to be todays date and to be hidden so the person doesn't see it.  I need to record the time the form was filled out and inserted. If you want to get the date and insert it into a DB record, just use GETDATE() founction in your sql statement, no need to do this in asp.net page 你好! Just FYI o_O Kewl, thanks!!!  I did this per your suggestion and it worked perfectly!INSERT INTO request(emp_id, request_submit_date, request_start_date, request_end_date, request_duration, request_notes, time_off_id) VALUES (@emp_id, GETD...

Dates, Dates, Dates!
Hello all... I am in the process of building an appointment system, and I am trying to see if I can get some pointers on which way to go on this. The first version of this appointment system was very lo-tech, and it stored all of the possible dates/times in the database. Once a user booked an appointment, their information was logged to that date/time, and then that date/time would no longer show in the list of possible openings. This was great for a temporary fix, but with having to pre-populate all of the dates/times, the system would eventually have to be updated to continue on pa...

just Time or date portion of a date/time field
Good Day, From a DateTime data column how do I get just a Time portin or just a date portion of data to be displayed. i.e from 11/6/2003 10:03:20 AM I need to display date and time seperately in my data display page. Thanks Regards, In code you could use Dim dt as System.Date Dim day, month, year as integer day = dt.Day month= dt.Month year = dt.Year This also works with DateTime In SQL you could use DATEPART(<datepart>,<datetime>) where <datepart> is the abbreviations mentioned in post 385301 and <datetime> is a...

date/time or date and time
I am setting up an SQL database and I will need to get differences in dates. For example I have a start date, start time, completion date and completion time and I want to get the difference between the start and completion. Would it be better to have one field with both date and time in it, or better to have a date field and a time field? Even though I have already started setting up the tables with seperate fields for date and time I am now leaning toward one field with date/time in it. (Only because that is the way I had to do it when setting up an Excel spreadsheet for a similar ta...

Insert a null value for a date field in a MS access dDB
I have to update a record where the date field must be changed in a null value. I use this method : Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("../web_mdb/utenti.mdb") & ";" Dim SQL as string = "Update DBTable Set DITTA = @DT, DATA_REG = @REG Where utente = '" & UT & "' and password = '" & PSWR & "'" Dim Conn as New OleDBConnection(strConn) Dim Cmd as New OleDBCommand(SQL, Conn) ...

date time in Access
I'm having trouble with date time in Access with DBI/ODBC I'm trying to do this: SELECT * FROM some_table WHERE date_time_field > 11/30/99 That's giving me all dates, where: SELECT * FROM some_table WHERE date_time_field < 11/30/99 Is giving me none. I must have done something completely wrong! It really is a date time field and looks like this: 1998-06-25 00:00:00 Jeff Hi Jeff, the correct syntax in access itself would be: >#9/30/1999# the writing of the data (mm.dd or dd.mm or mm/dd) belongs to the system variables set on the system ace...

Date entered...add time value to make date/time field
Windows XP Professional InfoMaker v 9.0.2 Build 7509 Our database contains a date/time stamp for when a specific activity happens to each shipment. I would like the users to be able to simply enter a date for a specific work day. I need to get a list of shipments where the time of one activity occurred on the entered date prior to 9:20. How can I create a computed field that will basically concatenate/amend the entered date with 9:20 that will later be used for criteria in returning rows? Thanks in advance for any help you can provide! Rita Palazzi Senior Engineer /...

Web resources about - Inserting Date into Access Date/Time Field - asp.net.access-datasource

Inserting Kate
jurvetson posted a photo: the speaking GPS avatar that we came to love.. more prep photos below... You can hear her voice in the video compilation ...

Inserting the Galaxy Note 5’s S Pen backwards can permanently damage the device
One of the features Samsung highlighted when it unveiled the Galaxy Note 5 was the device's new S Pen slot design. The Note 5 features a spring-loaded ...


Inserting Slavery Into The Climate Debate
Chris Hayes compares the fight against fossil fuels to the abolitionist movement. He states plainly that “there is absolutely no conceivable ...

Samsung smart TVs inserting ads into third-party apps
... playback have pissed off a number of consumers. People with smart TVs from Samsung have been complaining that the electronics maker is inserting ...

Inserting images into Gmail could be so much better
When Google first launched the new compact compose window in Gmail, many of you might have noticed something annoying about inserting images. ...

Gawker tells Steven Crowder to "stop whining, take your licks, and accept that getting hit in the face ...
"... in the middle of an argument between billionaire-funded know-nothing ideologues and people whose livelihoods and stability are being threatened ...

Gmail's New Interface for Inserting Photos
Gmail has a new interface for adding images to a message. When you click the "insert images" icon, Gmail now shows all the photos highlighted ...

Now Samsung SmartTVs are interrupting users' videos by inserting random ads
... room. Now Samsung is dealing with a new headache: Gigaom reports that Samsung SmartTVs are interrupting users' own video content by inserting ...

Samsung SmartTVs are inserting ads into movies - Business Insider
Random Pepsi pop-up ads have been appearing when SmartTV users watch content through the device's third-party apps.

Resources last updated: 12/9/2015 11:03:40 AM