如何仅使用acess作为数据库在Dgv On Form Load中显示当前月份记录 [英] How Can I Display Only The Current Month records In Dgv On Form Load using acess as the database

问题描述

大家好，

我有一个视觉工作室2010年的问题，我希望有人可以帮助我，哦，我是新手，所以请耐心等待我缺乏知识。

我在visual studio 2010（winforms vb.net）上有一个连接到DVG的2003访问数据库。我需要表单来加载DGV（只有当前月份，并在下个月到来时更改），因为我认为数据太大而无法有效加载表单。我有一个基本的搜索功能，我可以使用，如果我需要动态查看其他几个月，但我需要的是DGV在视图中加载数据现在一个月，它应该在表单加载时自动添加。 />
我的答案看起来很高很低但是无济于事。任何帮助都会非常感激。

亲切的问候

Jason

Hi All,
I have a visual studio 2010 question which I hope someone can help me with, oh, and I am a newbie so please be patient with my lack of knowledge.
I have an 2003 access database connected to DVG in visual studio 2010 (winforms vb.net). I need the form to load the DGV (ONLY the current month and change when the next month arrives), as i think in time the data will be too large to load on form load efficiently. I have a basic search function I can use if I need to view other months on the fly, but what i need is the DGV to load the data in the view for the month now and it should automatically added when the form loads.
I have looked high and low for an answer but to no avail. Any help would really be appreciated.
Kind Regards
Jason

推荐答案

在查询中添加where子句检索表单加载上的数据。您需要像在搜索功能中一样通过代码动态控制where子句。



不确定访问但是sql会像

Add a where clause on the query that retrieves the data on the form load. You will need to dynamically control the where clause via code as you would during your search function.

Not sure about access but sql would be something like

where Year(datadate) = year(getdate()) and month(datadate) = month(getdate())

Public Class MainPage



Public Sub Add_Data（）





con.Open（）



Dim rs As New OleDb.OleDbCommand（插入tbLMPH（IDday，ThisDay，ThisMonth， ThisYear，StartTime，FinishTime，Notes）值（'&& TextBox1.Text&'，'&TextBox2.Text&'，'&TextBox3.Text&'，'&TextBox4.Text&'， '&TextBox5.Text&'，'&TextBox6.Text&'，'&TextBox7.Text&'），con）

rs.ExecuteNonQuery（）



con.Close（）

Display_Data（）





结束次级





Public Sub Displa y_Data（）



con.Open（）'打开连接



Dim dt As New DataTable（ tbLMPH）

Dim rs As New OleDb.OleDbDataAdapter（select * from tbLMPH，con）

rs.Fill（dt）

DataGridView1.DataSource = dt

DataGridView1.Refresh（）



Label1.Text = dt.Rows.Count



rs.Dispose（）



con.Close（）









End Sub



Public Sub Search（）



con.Open（）



Dim dt作为新DataTable（tbLMPH）

Dim rs As New OleDb.OleDbDataAdapter（选择*来自tbLMPH，其中ThisYear ='&TextBox8.Text&'或ThisMonth ='&TextBox9.Text&'，con）

rs.Fill（dt）



DataGridView1.DataSource = dt

DataGridView1.Refresh（）



Label1.Text = dt.Rows.Count



rs.Dispose（ ）



con.Close（）



'Display_Data（）













End Sub



Public Sub GetData（）



con.Open（）



Dim dt As New DataTable（tbLMPH）

Dim rs As New OleDb.OleDbDataAdapter（select * from tbLMPH where IDday ='&TextBox1.Text& ''，con）

rs.Fill（dt）



DataGridView1.DataSource = dt

DataGridView1。刷新（）



Label1.Text = dt.Rows.Count



rs.Dispose（）< br $> b $ b

con.Close（）



如果Val（Label1.Text）= 1那么



Dim i As Integer

i = DataGridView1.CurrentRow.Index



TextBox2.Text = DataGridView1.Item（1，i）.Value

TextBox3.Text = DataGridView1 .Item（2，i）.Value

TextBox4.Text = DataGridView1.Item（3，i）.Value

TextBox5.Text = DataGridView1.Item（4，i ）.Value

TextBox6.Text = DataGridView1.Item（5，i）.Value

TextBox7.Text = DataGridView1.Item（6，i）.Value





结束如果

'Display_Data（）



b / b








End Sub







Private Sub Form1_Load（ByVal sender As System.Object，ByVal e As System.EventArgs）处理MyBase.Load

'TODO：这行代码将数据加载到'MaindbDataSet.tbLMPH'表中。您可以根据需要移动或删除它。

Me.TbLMPHTableAdapter.Fill（Me.MaindbDataSet.tbLMPH）

My.Computer.Audio.Play（My.Resources .jimmy2，AudioPlayMode.Background）

Display_Data（）

Timer1.Start（）

Label2.ForeColor = Color.White

Display_Data（）





End Sub



公开Sub Update_Data（）





con.Open（）



Dim rs作为新的OleDb.OleDbCommand（更新tbLMPH设置ThisDay ='&TextBox2.Text&'，ThisMonth ='&TextBox3.Text&'，ThisYear ='&TextBox4.Text&'，StartTime =' &TextBox5.Text&'，FinishTime ='&TextBox6.Text&'，Notes ='&TextBox7.Text&'where IDday ='&TextBox1.Text&'，con）

rs.ExecuteNonQuery（）



con.Close（）



Display_Data（ ）









End Sub



Public Sub Delete_Data（）



con.Open（）



Dim rs As New OleDb。 OleDbCommand（删除*来自tbLMPH，IDday ='&TextBox1.Text&'，con）

rs.ExecuteNonQuery（）



con.Close（）



Display_Data（）



End Sub



Private Sub Timer1_Tick（ByVal sender As System.Object，ByVal e As System.EventArgs）处理Timer1.Tick

Label2.Text =格式（TimeOfDay）

End Sub



Private Sub Timer2_Tick（ByVal sender As System.Object，ByVal e As System.EventArgs）处理Timer2.Tick

如果Label2.ForeColor = Color.White那么

Label2.ForeColor = Color.White

Else

Label2.ForeColor = Color 。白色

Label2.ForeColor = Color.White

结束如果

结束子



Private Sub WindowsUIButtonPanel1_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel1.Click

Add_Data（）



End Sub



Private Sub WindowsUIButtonPanel2_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel2.Click

GetData（）



End Sub



Private Sub WindowsUIButtonPanel3_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel3.Click

选择Case MsgBox（你确定要更新这条记录吗？，MsgBoxStyle.YesNo，更新确认）

Case MsgBoxResult.Yes

Update_Data（）

案例MsgBoxResult.No

'做些什么如果没有

结束选择

结束子



私有子WindowsUIButtonPanel4_Click（ByVal sender As System.Object， ByVal e As System.EventArgs）处理WindowsUIButtonPanel4.Click

选择Case MsgBox（你确定要删除这条记录吗？，MsgBoxStyle.YesNo，删除确认）

案例MsgBoxResult.Yes

Delete_Data（）

案例MsgBoxResult.No

'做一些事情，如果没有

结束选择

End Sub



Private Sub WindowsUIButtonPanel5_Click_1（ByVal sender As System.Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel5。点击

搜索（）



结束子



私有子WindowsUIButtonPanel6_Click（ ByVal sender As System.Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel6.Click

Met .Show（）

End Sub



Private Sub WindowsUIButtonPanel7_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel7 .Click

MP3Player.Show（）

End Sub



Private Sub WindowsUIButtonPanel8_Click（ByVal sender As System。 Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel8.Click

StopWatch.Show（）

End Sub



Private Sub WindowsUIButtonPanel9_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel9.Click

MPReport.Show（）

End Sub



Private Sub WindowsUIButtonPanel10_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理WindowsUIButtonPanel10.Click

SongList.Show（）

Me.Hide（）

End Sub



Private Sub WindowsUIButt onPanel12_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）

WarmUp.Show（）

Me.Hide（）

End Sub



Private Sub WarmUpToolStripMenuItem_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理WarmUpToolStripMenuItem.Click

WarmUp。显示（）

Me.Hide（）



End Sub



Private Sub ScalesToolStripMenuItem_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）Handles ScalesToolStripMenuItem.Click

Scales.Show（）

Me.Hide（）



End Sub



Private Sub ExercisesToolStripMenuItem_Click（ByVal sender As System.Object，ByVal e As System.EventArgs） Handles ExercisesToolStripMenuItem.Click

Exercises.Show（）

Me.Hide（）



End Sub



Private Sub ArpeggiosToolStripMenuItem_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理ArpeggiosToolStripMenuItem.Click

Arpeggios.Show（）

Me.Hide（）



End Sub



Private Sub EarTrainingToolStripMenuItem_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理EarTrainingToolStripMenuItem 。点击

EarTraining.Show（）

Me.Hide（）



End Sub



Private Sub ImpovisationToolStripMenuItem_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）Handles ImpovisationToolStripMenuItem.Click

Improvisation.Show（）

Me.Hide（）

End Sub



Private Sub SongListToolStripMenuItem_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）处理SongListToolStripMenuItem.Click

SongList.Show（）

Me.Hide（）



End Sub



Private Sub ReportToolStripMenuItem_Click（ByVal sender As System.Object ，ByVal e As System.EventArgs）处理ReportToolStripMenuItem.Click

MPReport.Show（）



End Sub



Private Sub AboutToolStripMenuItem_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）Handles AboutToolStripMenuItem.Click

AboutBox1.Show（）



End Sub



Private Sub Button1_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）

MPHPlanner.Show（）

结束子

结束班

Public Class MainPage

Public Sub Add_Data()


con.Open()

Dim rs As New OleDb.OleDbCommand("Insert into tbLMPH(IDday,ThisDay,ThisMonth,ThisYear,StartTime,FinishTime,Notes) values ('" & TextBox1.Text & "' , '" & TextBox2.Text & "' , '" & TextBox3.Text & "' , '" & TextBox4.Text & "' , '" & TextBox5.Text & "', '" & TextBox6.Text & "', '" & TextBox7.Text & "')", con)
rs.ExecuteNonQuery()

con.Close()
Display_Data()


End Sub


Public Sub Display_Data()

con.Open() 'opening the connection

Dim dt As New DataTable("tbLMPH")
Dim rs As New OleDb.OleDbDataAdapter("Select * from tbLMPH", con)
rs.Fill(dt)
DataGridView1.DataSource = dt
DataGridView1.Refresh()

Label1.Text = dt.Rows.Count

rs.Dispose()

con.Close()




End Sub

Public Sub Search()

con.Open()

Dim dt As New DataTable("tbLMPH")
Dim rs As New OleDb.OleDbDataAdapter("Select * from tbLMPH where ThisYear='" & TextBox8.Text & "' or ThisMonth='" & TextBox9.Text & "' ", con)
rs.Fill(dt)

DataGridView1.DataSource = dt
DataGridView1.Refresh()

Label1.Text = dt.Rows.Count

rs.Dispose()

con.Close()

' Display_Data()






End Sub

Public Sub GetData()

con.Open()

Dim dt As New DataTable("tbLMPH")
Dim rs As New OleDb.OleDbDataAdapter("Select * from tbLMPH where IDday='" & TextBox1.Text & "' ", con)
rs.Fill(dt)

DataGridView1.DataSource = dt
DataGridView1.Refresh()

Label1.Text = dt.Rows.Count

rs.Dispose()

con.Close()

If Val(Label1.Text) = 1 Then

Dim i As Integer
i = DataGridView1.CurrentRow.Index

TextBox2.Text = DataGridView1.Item(1, i).Value
TextBox3.Text = DataGridView1.Item(2, i).Value
TextBox4.Text = DataGridView1.Item(3, i).Value
TextBox5.Text = DataGridView1.Item(4, i).Value
TextBox6.Text = DataGridView1.Item(5, i).Value
TextBox7.Text = DataGridView1.Item(6, i).Value


End If
' Display_Data()






End Sub



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'MaindbDataSet.tbLMPH' table. You can move, or remove it, as needed.
Me.TbLMPHTableAdapter.Fill(Me.MaindbDataSet.tbLMPH)
My.Computer.Audio.Play(My.Resources.jimmy2, AudioPlayMode.Background)
Display_Data()
Timer1.Start()
Label2.ForeColor = Color.White
Display_Data()


End Sub

Public Sub Update_Data()


con.Open()

Dim rs As New OleDb.OleDbCommand("Update tbLMPH set ThisDay='" & TextBox2.Text & "', ThisMonth='" & TextBox3.Text & "', ThisYear='" & TextBox4.Text & "', StartTime='" & TextBox5.Text & "', FinishTime='" & TextBox6.Text & "', Notes='" & TextBox7.Text & "' where IDday='" & TextBox1.Text & "' ", con)
rs.ExecuteNonQuery()

con.Close()

Display_Data()




End Sub

Public Sub Delete_Data()

con.Open()

Dim rs As New OleDb.OleDbCommand("Delete * from tbLMPH where IDday='" & TextBox1.Text & "' ", con)
rs.ExecuteNonQuery()

con.Close()

Display_Data()

End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Label2.Text = Format(TimeOfDay)
End Sub

Private Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick
If Label2.ForeColor = Color.White Then
Label2.ForeColor = Color.White
Else
Label2.ForeColor = Color.White
Label2.ForeColor = Color.White
End If
End Sub

Private Sub WindowsUIButtonPanel1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel1.Click
Add_Data()

End Sub

Private Sub WindowsUIButtonPanel2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel2.Click
GetData()

End Sub

Private Sub WindowsUIButtonPanel3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel3.Click
Select Case MsgBox("Are you sure you want to update this record?", MsgBoxStyle.YesNo, "Update Confirmation")
Case MsgBoxResult.Yes
Update_Data()
Case MsgBoxResult.No
' Do something if no
End Select
End Sub

Private Sub WindowsUIButtonPanel4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel4.Click
Select Case MsgBox("Are you sure you want to delete this record?", MsgBoxStyle.YesNo, "Delete Confirmation")
Case MsgBoxResult.Yes
Delete_Data()
Case MsgBoxResult.No
' Do something if no
End Select
End Sub

Private Sub WindowsUIButtonPanel5_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel5.Click
Search()

End Sub

Private Sub WindowsUIButtonPanel6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel6.Click
Met.Show()
End Sub

Private Sub WindowsUIButtonPanel7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel7.Click
MP3Player.Show()
End Sub

Private Sub WindowsUIButtonPanel8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel8.Click
StopWatch.Show()
End Sub

Private Sub WindowsUIButtonPanel9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel9.Click
MPReport.Show()
End Sub

Private Sub WindowsUIButtonPanel10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WindowsUIButtonPanel10.Click
SongList.Show()
Me.Hide()
End Sub

Private Sub WindowsUIButtonPanel12_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
WarmUp.Show()
Me.Hide()
End Sub

Private Sub WarmUpToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WarmUpToolStripMenuItem.Click
WarmUp.Show()
Me.Hide()

End Sub

Private Sub ScalesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ScalesToolStripMenuItem.Click
Scales.Show()
Me.Hide()

End Sub

Private Sub ExercisesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExercisesToolStripMenuItem.Click
Exercises.Show()
Me.Hide()

End Sub

Private Sub ArpeggiosToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ArpeggiosToolStripMenuItem.Click
Arpeggios.Show()
Me.Hide()

End Sub

Private Sub EarTrainingToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EarTrainingToolStripMenuItem.Click
EarTraining.Show()
Me.Hide()

End Sub

Private Sub ImpovisationToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImpovisationToolStripMenuItem.Click
Improvisation.Show()
Me.Hide()
End Sub

Private Sub SongListToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SongListToolStripMenuItem.Click
SongList.Show()
Me.Hide()

End Sub

Private Sub ReportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReportToolStripMenuItem.Click
MPReport.Show()

End Sub

Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem.Click
AboutBox1.Show()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
MPHPlanner.Show()
End Sub
End Class

这篇关于如何仅使用acess作为数据库在Dgv On Form Load中显示当前月份记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！