Insert a Date into DateTime Column using ADO.NET in VB.Net

In this article, we will show how to insert a date into a date column using ADO.NET and VB.Net.The article shows how you can convert a date into a proper data which database accepts.
  • 5914
 

In this article, we will show how to insert a date into a date column using ADO.NET and VB.Net.

Here we have an Access database having 2 columns - ID [type - number] and date [type - date\time]. Now problem is date column does not accept date as string direct unless we do some changes to it. The following code shows you how you can convert a date into a proper data which database accepts just fine.

Imports
System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb
Imports System.Globalization

Namespace MSaccessDateInsert

Public Class MSaccessDateInsert

Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End
Sub
'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If
Not (components Is Nothing) Then
components.Dispose()
End If
End
If
MyBase
.Dispose(disposing)
End
Sub

'Required by the Windows Form Designer
' Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.

Friend WithEvents dateLabel As System.Windows.Forms.Label
Friend WithEvents intLabel As System.Windows.Forms.Label
Friend WithEvents DateInsert As System.Windows.Forms.Button
Friend WithEvents t_Dates As System.Windows.Forms.TextBox
Friend WithEvents t_ID As System.Windows.Forms.TextBox
Private components As System.ComponentModel.Container = Nothing
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me
.dateLabel = New System.Windows.Forms.Label
Me.intLabel = New System.Windows.Forms.Label
Me.DateInsert = New System.Windows.Forms.Button
Me.t_Dates = New System.Windows.Forms.TextBox
Me.t_ID = New System.Windows.Forms.TextBox
Me.SuspendLayout()
'
'dateLabel
'
Me.dateLabel.AutoSize = True
Me
.dateLabel.Location = New System.Drawing.Point(49, 77)
Me.dateLabel.Name = "dateLabel"
Me.dateLabel.Size = New System.Drawing.Size(28, 16)
Me.dateLabel.TabIndex = 9
Me.dateLabel.Text = "Date"
'
'intLabel
'
Me.intLabel.AutoSize = True
Me
.intLabel.Location = New System.Drawing.Point(49, 45)
Me.intLabel.Name = "intLabel"
Me.intLabel.Size = New System.Drawing.Size(49, 16)
Me.intLabel.TabIndex = 8
Me.intLabel.Text = "Int Value"
'
'DateInsert
'
Me.DateInsert.Location = New System.Drawing.Point(130, 109)
Me.DateInsert.Name = "DateInsert"
Me.DateInsert.TabIndex = 7
Me.DateInsert.Text = "Insert Date"
'
't_Dates
'
Me.t_Dates.Location = New System.Drawing.Point(105, 77)
Me.t_Dates.Name = "t_Dates"
Me.t_Dates.TabIndex = 6
Me.t_Dates.Text = ""
'
't_ID
'
Me.t_ID.Location = New System.Drawing.Point(105, 45)
Me.t_ID.Name = "t_ID"
Me.t_ID.TabIndex = 5
Me.t_ID.Text = ""
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(256, 174)
Me.Controls.Add(Me.dateLabel)
Me.Controls.Add(Me.intLabel)
Me.Controls.Add(Me.DateInsert)
Me.Controls.Add(Me.t_Dates)
Me.Controls.Add(Me.t_ID)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)
End Sub
#End Region

Shared
Sub Main()
Application.Run(
New MSaccessDateInsert)
End Sub
Private
Sub DateInsert_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateInsert.Click
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=Dates.mdb"
Dim myConn As New OleDbConnection(strConn)
myConn.Open()
'the string to get values from the textboxes and form an "INSERT INTO"
' statement.

Dim strInsert As String = "INSERT INTO fDate (ID, firstDate) VALUES ( "
'reset all the textboxes
Dim i As Integer = Integer.Parse(t_ID.Text)
Dim ddt As System.DateTime = DateTime.Parse(t_Dates.Text, System.Globalization.CultureInfo.CreateSpecificCulture("en-AU").DateTimeFormat)
Dim
sNow As String = ""
sNow = ddt.ToShortDateString()
t_ID.Text = i.ToString()
t_Dates.Text = "#"c + sNow + "#"c
'NOTE for integers do not have apostrophe (') in the string text
strInsert += t_ID.Text + ", "
strInsert += "CDate(" + t_Dates.Text + ")"c + ")"
Dim inst As New OleDbCommand(strInsert, myConn)
'Execute the statement
inst.ExecuteNonQuery()
t_Dates.Text = ""
myConn.Close()
End Sub
End
Class
End
Namespace

NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# CORNER (WWW.C-SHARPCORNER.COM).

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.