[vb.net] How to get parameter value for date/time column from empty MaskedTextBox

I'm having a problem adding a parameter of date/time type to my insert statement. For textual data you can leave the textbox empty and the record will be added but with date/time data I can't. I get the following error:

Data type mismatch in criteria expression.

I want my date/time MaskedTextBox (__/__/____) to be added even if it is empty. Here's my code.

Private Sub btnBookADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBookADD.Click     Dim sqlinsert As String = "INSERT INTO tblBookIssue([Accession Number],[Book Title],[Student Name],[Years],[Section],[Date Issue],[Date to be Return],[Date Returned])  VALUES ('" & txtBookAccessNumber.Text & "' , '" & txtBookBookTitle.Text & "' , '" & txtBookStudentName.Text & "' , '" & txtBookYears.Text & "' , '" & txtBookSection.Text & "' , '" & txtBookDateIssue.Text & "' , '" & txtBookDatetobeReturn.Text & "' , '" & txtBookDateReturned.Text & "')"     Dim cmd As New OleDbCommand(sqlinsert, con)      cmd.Parameters.Add(New OleDbParameter("@Accession_Number", txtBookAccessNumber.Text))     cmd.Parameters.Add(New OleDbParameter("@Book_Title", txtBookBookTitle.Text))     cmd.Parameters.Add(New OleDbParameter("@Student_Name", txtBookStudentName.Text))     cmd.Parameters.Add(New OleDbParameter("@Years", txtBookYears.Text))     cmd.Parameters.Add(New OleDbParameter("@Section", txtBookSection.Text))     cmd.Parameters.Add(New OleDbParameter("@Date_Issue", txtBookDateIssue.Text))     cmd.Parameters.Add(New OleDbParameter("@Date_to_be_Return", txtBookDatetobeReturn.Text))     cmd.Parameters.Add(New OleDbParameter("@Date_Returned", txtBookDateReturned.Text))      con.Open()     cmd.ExecuteNonQuery()     con.Close()     MsgBox("One Record Added")     RefreshDataGridview3() End Sub 

This question is related to vb.net ms-access

The answer is


You're storing the .Text properties of the textboxes directly into the database, this doesn't work. The .Text properties are Strings (i.e. simple text) and not typed as DateTime instances. Do the conversion first, then it will work.

Do this for each date parameter:

Dim bookIssueDate As DateTime = DateTime.ParseExact( txtBookDateIssue.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture ) cmd.Parameters.Add( New OleDbParameter("@Date_Issue", bookIssueDate ) ) 

Note that this code will crash/fail if a user enters an invalid date, e.g. "64/48/9999", I suggest using DateTime.TryParse or DateTime.TryParseExact, but implementing that is an exercise for the reader.