title: How to update database date/time field in vb.net?
body: <p>I have an update button that will update my database table, It seems that my datepicker is the problem, I tried parsing it in but didn't work my recent attempt is this ....(below) .... but instead of parsing in into date ... It says error in converting string into type integer. Well, the problem is clear but the parsing method I applied is not working. Thanks</p>
  
  <pre><code>Try
  
          Dim RowsAffected As Integer = 0
  
          con = New OleDbConnection(cs)
  
          con.Open()
  
  
          Dim cq As String = "UPDATE StockItem SET StockedDate=@val1, ItemName=@val2, ItemSize=@val3, Quantity=@val4 WHERE StockedDate=@find1 AND ItemName=@find2 AND ItemSize=@find3 AND Quantity=@find4;"
  
  
          cmd = New OleDbCommand(cq)
  
          cmd.Connection = con
  
          ''SQL Where
          'type
          cmd.Parameters.Add(New OleDbParameter("@find1", System.Data.OleDb.OleDbType.Date, "StockedDate"))
          cmd.Parameters.Add(New OleDbParameter("@find2", System.Data.OleDb.OleDbType.VarChar, 50, "ItemName"))
          cmd.Parameters.Add(New OleDbParameter("@find3", System.Data.OleDb.OleDbType.VarChar, 50, "ItemSize"))
          cmd.Parameters.Add(New OleDbParameter("@find4", System.Data.OleDb.OleDbType.Integer, 10, "Quantity"))
          'value
  
          ''Dim iString As String = "2005-05-05 22:12 PM"
          ''Dim oDate As DateTime = DateTime.ParseExact(lbltemInDate.Text, "MM-dd-yyyy", Nothing)
          ''MsgBox(oDate.ToString())
          cmd.Parameters("@find1").Value = lbltemInDate.Value.Date
          'CDate(Format(lbltemInDate.Text, "MM/dd/yyyy"))
          cmd.Parameters("@find2").Value = Trim(lbltemInItem.Text)
          cmd.Parameters("@find3").Value = Trim(lbltemInItemSize.Text)
          cmd.Parameters("@find4").Value = Val(lbltemInQuantity.Text)
  
  
  
          ''SQL Set
          'type
          cmd.Parameters.Add(New OleDbParameter("@val1", System.Data.OleDb.OleDbType.Date, "StockedDate"))
          cmd.Parameters.Add(New OleDbParameter("@val2", System.Data.OleDb.OleDbType.VarChar, 50, "ItemName"))
          cmd.Parameters.Add(New OleDbParameter("@val3", System.Data.OleDb.OleDbType.VarChar, 50, "ItemSize"))
          cmd.Parameters.Add(New OleDbParameter("@val4", System.Data.OleDb.OleDbType.Integer, 10, "Quantity"))
          'value
          'cmd.Parameters("@val1").Value = CDate(Format(dtptemInDate.Value, "MM/dd/yyyy"))
          cmd.Parameters("@val1").Value = dtptemInDate.Value.Date
          cmd.Parameters("@val2").Value = Trim(cbxtemInNewItem.Text)
          cmd.Parameters("@val3").Value = Trim(cbxtemInNewItemSize.Text)
          cmd.Parameters("@val4").Value = Val(txttemInNewQuantity.Text)
  
          'cmd.Parameters.AddWithValue("val1", dtptemInDate.Value.Date)
          'cmd.Parameters.AddWithValue("val2", cbxtemInNewItem.Text)
          'cmd.Parameters.AddWithValue("val3", cbxtemInNewItemSize.Text)
          'cmd.Parameters.AddWithValue("val3", Val(txttemInNewQuantity.Text))
  
          RowsAffected = cmd.ExecuteNonQuery()
          If RowsAffected &gt; 0 Then
  
              MessageBox.Show("Successfully Updated!", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
              iteminClearDetails()
  
          Else
              MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
  
              If con.State = ConnectionState.Open Then
  
                  con.Close()
              End If
  
              con.Close()
          End If
  
      Catch ex As Exception
          MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      End Try
  </code></pre>
  
  <p>This is the Reordered part</p>
  
  <pre><code>''SQL Set
          'type
          cmd.Parameters.Add(New OleDbParameter("@val1", System.Data.OleDb.OleDbType.DBDate, "StockedDate"))
          cmd.Parameters.Add(New OleDbParameter("@val2", System.Data.OleDb.OleDbType.VarChar, 50, "ItemName"))
          cmd.Parameters.Add(New OleDbParameter("@val3", System.Data.OleDb.OleDbType.VarChar, 50, "ItemSize"))
          cmd.Parameters.Add(New OleDbParameter("@val4", System.Data.OleDb.OleDbType.Integer, 10, "Quantity"))
  
          cmd.Parameters.Add(New OleDbParameter("@find1", System.Data.OleDb.OleDbType.DBDate, "StockedDate"))
          cmd.Parameters.Add(New OleDbParameter("@find2", System.Data.OleDb.OleDbType.VarChar, 50, "ItemName"))
          cmd.Parameters.Add(New OleDbParameter("@find3", System.Data.OleDb.OleDbType.VarChar, 50, "ItemSize"))
          cmd.Parameters.Add(New OleDbParameter("@find4", System.Data.OleDb.OleDbType.Integer, 10, "Quantity"))
  
          'value
          'cmd.Parameters("@val1").Value = CDate(Format(dtptemInDate.Value, "MM/dd/yyyy"))
          cmd.Parameters("@val1").Value = dtptemInDate.Value.Date
          cmd.Parameters("@val2").Value = Trim(cbxtemInNewItem.Text)
          cmd.Parameters("@val3").Value = Trim(cbxtemInNewItemSize.Text)
          cmd.Parameters("@val4").Value = Val(txttemInNewQuantity.Text)
  
  
          ''SQL Where
          'type
                     'value
  
          ''Dim iString As String = "2005-05-05 22:12 PM"
          ''Dim oDate As DateTime = DateTime.ParseExact(lbltemInDate.Text, "MM-dd-yyyy", Nothing)
          ''MsgBox(oDate.ToString())
          Dim date1 As Date
          date1 = Date.Parse(Format("MM/dd/yyyy", lbltemInDate.Value))
          cmd.Parameters("@find1").Value = lbltemInDate.Value
          'CDate(Format(lbltemInDate.Text, "MM/dd/yyyy"))
          cmd.Parameters("@find2").Value = Trim(lbltemInItem.Text)
          cmd.Parameters("@find3").Value = Trim(lbltemInItemSize.Text)
          cmd.Parameters("@find4").Value = Val(lbltemInQuantity.Text)
  
  
          'cmd.Parameters.AddWithValue("val1", dtptemInDate.Value.Date)
          'cmd.Parameters.AddWithValue("val2", cbxtemInNewItem.Text)
          'cmd.Parameters.AddWithValue("val3", cbxtemInNewItemSize.Text)
          'cmd.Parameters.AddWithValue("val3", Val(txttemInNewQuantity.Text))
  </code></pre>
  
  <p>Does System.Data.OleDb.OleDbType.DBDate, "MM/dd/yyyy", "StockedDate" ... work on .net?</p>
  
  <p>The working part</p>
  
  <pre><code>''SQL Set
          'type
          cmd.Parameters.Add(New OleDbParameter("@val1", System.Data.OleDb.OleDbType.Date))
          cmd.Parameters.Add(New OleDbParameter("@val2", System.Data.OleDb.OleDbType.VarChar, 50, "ItemName"))
          cmd.Parameters.Add(New OleDbParameter("@val3", System.Data.OleDb.OleDbType.VarChar, 50, "ItemSize"))
          cmd.Parameters.Add(New OleDbParameter("@val4", System.Data.OleDb.OleDbType.Integer, 10, "Quantity"))
  
          cmd.Parameters.Add(New OleDbParameter("@find1", System.Data.OleDb.OleDbType.Date))
          cmd.Parameters.Add(New OleDbParameter("@find2", System.Data.OleDb.OleDbType.VarChar, 50, "ItemName"))
          cmd.Parameters.Add(New OleDbParameter("@find3", System.Data.OleDb.OleDbType.VarChar, 50, "ItemSize"))
          cmd.Parameters.Add(New OleDbParameter("@find4", System.Data.OleDb.OleDbType.Integer, 10, "Quantity"))
  
          'value
          'cmd.Parameters("@val1").Value = CDate(Format(dtptemInDate.Value, "MM/dd/yyyy"))
          cmd.Parameters("@val1").Value = dtptemInDate.Value.Date
          cmd.Parameters("@val2").Value = Trim(cbxtemInNewItem.Text)
          cmd.Parameters("@val3").Value = Trim(cbxtemInNewItemSize.Text)
          cmd.Parameters("@val4").Value = Val(txttemInNewQuantity.Text)
  
  
          ''SQL Where
          'type
                     'value
  
          ''Dim iString As String = "2005-05-05 22:12 PM"
          ''Dim oDate As DateTime = DateTime.ParseExact(lbltemInDate.Text, "MM-dd-yyyy", Nothing)
          ''MsgBox(oDate.ToString())
          Dim date1 As Date
          date1 = Date.Parse(Format("MM/dd/yyyy", lbltemInDate.Value))
          cmd.Parameters("@find1").Value = lbltemInDate.Value
          'CDate(Format(lbltemInDate.Text, "MM/dd/yyyy"))
          cmd.Parameters("@find2").Value = Trim(lbltemInItem.Text)
          cmd.Parameters("@find3").Value = Trim(lbltemInItemSize.Text)
          cmd.Parameters("@find4").Value = Val(lbltemInQuantity.Text)
  </code></pre>
  
  <p>Thanks LarsTech and Plutonix for the help</p>