过滤两个日期并将日期添加到数据库 [英] Filtering betwen two dates and adding date to database
本文介绍了过滤两个日期并将日期添加到数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我使用了数据库的示例文件。
I used example file of database.
在访问文件中，我在此列的数据类型中添加了一个Date列，更改为日期/时间
In access file I added a Date column in data type of this column changed to Date/Time
我使用DateTimePicker添加日期字段，当我尝试保存数据时，没有任何反应...
I add a date field using DateTimePicker and when I trying to save data, nothing happens...
在2个日期之间进行第二次过滤（ Private Sub Search_Click ）：
And second filtering between 2 dates (Private Sub Search_Click) :
Public Class frmMainForm
WithEvents bsCustomers As New BindingSource
Dim datatable As New DataTable
Private mTitleList As List(Of String)
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
bsCustomers.DataSource = ops.LoadCustomers()
mTitleList = ops.LoadContactTitles()
BindingNavigator1.BindingSource = bsCustomers
DataGridView1.AllowUserToAddRows = False
DataGridView1.DataSource = bsCustomers
DataGridView1.Columns("Identifier").HeaderText = "ID"
DataGridView1.Columns("CompanyName").HeaderText = "Company"
DataGridView1.Columns("Date").HeaderText = "Date"
DataGridView1.Columns("ContactName").HeaderText = "Contact"
DataGridView1.Columns("ContactTitle").HeaderText = "Contact Title"
DataGridView1.Columns("Address").HeaderText = "Address"
DataGridView1.Columns("City").HeaderText = "City"
DataGridView1.Columns("PostalCode").HeaderText = "PostalCode"
DataGridView1.Columns("Country").HeaderText = "Country"
DataGridView1.ExpandColumns()
bsCustomers.Sort = "Date"
End Sub
Private Sub DataGridView1SelectAll_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
If TypeOf DataGridView1.CurrentCell Is DataGridViewCheckBoxCell Then
DataGridView1.EndEdit()
End If
End Sub
''' <summary>
''' Add new row, do only two fields, add more as desired
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
Dim f As New frmEditor
Try
f.cboTitle.DataSource = mTitleList
If f.ShowDialog = DialogResult.OK Then
Dim Identifier As Int32 = 0
' if the row was added, insert it to the current DataTable
If ops.AddNewRow(f.txtDate.Text, f.txtCompanyName.Text, f.txtContactName.Text, f.cboTitle.Text, f.txtAddress.Text, f.txtCity.Text, f.txtCode.Text, f.txtCountry.Text, Identifier) Then
bsCustomers.DataTable.Rows.Add(New Object() {Identifier, f.txtData.Text, f.txtCompanyName.Text, f.txtContactName.Text, f.cboTitle.Text, f.txtAddress.Text, f.txtCity.Text, f.txtCode.Text, f.txtCountry.Text})
bsCustomers.Locate("Identifier", Identifier.ToString)
End If
End If
Finally
f.Dispose()
End Try
End Sub
Private Sub Search_Click(sender As Object, e As EventArgs) Handles Search.Click
Me.TblUserBindingSource.Filter = "Date >= '" & DateTimePicker1.Value & "' and Date <= '" & DateTimePicker2.Value & "'"
End Sub
End Class
Imports System.Data.OleDb
Public Class DatabaseOperations
Private Builder As New OleDbConnectionStringBuilder With
{
.Provider = "Microsoft.ACE.OLEDB.12.0",
.DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
}
''' <summary>
''' Read USA customers from database into a DataTable
''' </summary>
''' <returns></returns>
''' <remarks>
''' Database is assumed to be in the Bin\Debug folder.
''' </remarks>
Public Function LoadCustomers() As DataTable
Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
SELECT
Identifier,
Date,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
Country
FROM Customer ORDER BY CompanyName;
</SQL>.Value
Dim dt As New DataTable With {.TableName = "Customer"}
Try
cn.Open()
dt.Load(cmd.ExecuteReader)
' dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
Catch ex As Exception
MessageBox.Show("Failed to load customer data. See error message below" & Environment.NewLine & ex.Message)
End Try
dt.AcceptChanges()
Return dt
End Using
End Using
End Function
Public Function LoadContactTitles() As List(Of String)
Dim titleList As New List(Of String)
Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
SELECT DISTINCT ContactTitle
FROM Customer
</SQL>.Value
Try
cn.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader
While reader.Read
titleList.Add(reader.GetString(0))
End While
Catch ex As Exception
MessageBox.Show("Failed to load customer data. See error message below" & Environment.NewLine & ex.Message)
End Try
End Using
End Using
Return titleList
End Function
Public Function RemoveCurrentCustomer(ByVal pIdentfier As Integer) As Boolean
Try
Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = "DELETE FROM Customer WHERE Identifier = ?"
Dim IdentifierParameter As New OleDbParameter With
{
.DbType = DbType.Int32,
.ParameterName = "P1",
.Value = pIdentfier
}
cmd.Parameters.Add(IdentifierParameter)
Try
cn.Open()
Dim Affected = cmd.ExecuteNonQuery
Return Affected = 1
Catch ex As Exception
Return False
End Try
End Using
End Using
Catch ex As Exception
'
' Handle or not handle exceptions for failed save operation
'
Return False
End Try
End Function
Public Function AddNewRow(ByVal pName As String, ByVal pDate As String, ByVal pContact As String, ByVal pContactTitle As String, ByVal pAddress As String, ByVal pCity As String, ByVal pPostalCode As String, ByVal pCountry As String, ByRef pIdentfier As Integer) As Boolean
Dim Success As Boolean = True
Try
Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
INSERT INTO Customer
(
Date,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
PostalCode,
Country
)
Values
(
@Date,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@PostalCode,
@Country
)
</SQL>.Value
cmd.Parameters.AddWithValue("@Date", pDate)
cmd.Parameters.AddWithValue("@CompanyName", pName)
cmd.Parameters.AddWithValue("@ContactName", pContact)
cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
cmd.Parameters.AddWithValue("@Address", pAddress)
cmd.Parameters.AddWithValue("@City", pCity)
cmd.Parameters.AddWithValue("@PostalCode", pPostalCode)
cmd.Parameters.AddWithValue("@Country", pCountry)
cn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
End Using
End Using
Catch ex As Exception
Success = False
End Try
Return Success
End Function
Public Function SaveChanges(ByVal sender As DataRow) As Boolean
Try
Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
UPDATE
Customer
SET
Date=?,
CompanyName=?,
ContactName=?,
ContactTitle=?,
Address=?,
City=?,
PostalCode=?,
Country=?
WHERE Identifier = ?
</SQL>.Value
Dim DataParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "P1",
.Value = sender.Field(Of String)("Date")
}
cmd.Parameters.Add(DataParameter)
Dim CompanyNameParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "P2",
.Value = sender.Field(Of String)("CompanyName")
}
cmd.Parameters.Add(CompanyNameParameter)
Dim ContactNameParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "P3",
.Value = sender.Field(Of String)("ContactName")
}
cmd.Parameters.Add(ContactNameParameter)
Dim ContactTitleParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "P4",
.Value = sender.Field(Of String)("ContactTitle")
}
cmd.Parameters.Add(ContactTitleParameter)
Dim AddressParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "P5",
.Value = sender.Field(Of String)("Address")
}
cmd.Parameters.Add(AddressParameter)
Dim CityParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "P6",
.Value = sender.Field(Of String)("City")
}
cmd.Parameters.Add(CityParameter)
Dim PostalCodeParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "P7",
.Value = sender.Field(Of String)("PostalCode")
}
cmd.Parameters.Add(PostalCodeParameter)
Dim CountryParameter As New OleDbParameter With
{
.DbType = DbType.String,
.ParameterName = "P8",
.Value = sender.Field(Of String)("Country")
}
cmd.Parameters.Add(CountryParameter)
Dim IdentifierParameter As New OleDbParameter With
{
.DbType = DbType.Int32,
.ParameterName = "P9",
.Value = sender.Field(Of Int32)("Identifier")
}
cmd.Parameters.Add(IdentifierParameter)
Try
cn.Open()
Dim Affected = cmd.ExecuteNonQuery
Return Affected = 1
Catch ex As Exception
Return False
End Try
End Using
End Using
Catch ex As Exception
'
' Handle or not handle exceptions for failed save operation
'
Return False
End Try
End Function
End Class
推荐答案
您好，
我正在出门工作（大约一个小时左右），会看看它并且对代码进行评论，因为我看到了一些改变代码和断言数据操作的方法。
I'm heading out the door for work (in about one hour or so), will take a look at it and comment on the code as I'm seeing some ways to alter the code and also for asserting the data operations.
这篇关于过滤两个日期并将日期添加到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
