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
You're storing the .Text
properties of the textboxes directly into the database, this doesn't work. The .Text
properties are String
s (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.
Source: Stackoverflow.com