How Can I Display Only The Current Month records In Dgv On Form Load using acess as the database

Question

2.00/5 (1 vote)

See more:

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

Posted 29-Jun-15 15:58pm

Jaybo007

Updated 30-Jun-15 18:28pm

v3

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RossMW · Answer 1 · 2015-06-29T16:35:00

Solution 1

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

SQL

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

Posted 29-Jun-15 16:35pm

RossMW

Updated 29-Jun-15 16:38pm

v2

Comments

Jaybo007 30-Jun-15 1:58am

Call me stupid but I have no idea where I am to put this data you supplied. So sorry, I feel stupid. (told ya i'm a newbie lol)

Jaybo007 30-Jun-15 1:58am

Oh, and thank you for such a prompt reply.

RossMW 30-Jun-15 2:29am

When you are opening the form the datagridview will be retrieving the data. Now it depends on how you have set this up but you will need to change it to add the where clause in the select clause. If you set this by linking direct to the table then you may have to remove it and use code to set the date set. There are plenty of examples on the Internet that show how to do this. The datagridview can be quite confusing for someone new so I suggest you start with something simpler as practice. Like populate a combo box from code (not by linking)

Jaybo007 30-Jun-15 19:00pm

Just wondering if i need to modify the code as i'm getting errors under the
(where, datadate, getdate, dadatdate, getdate. If i upload my code could you look at it for me please and advise where the clause needs to go. I'm not asking you to insert it, I just need your advise so I can learn. I am sorry to be a pain but I've heard asking questions is a sign of intelligence lol.

RossMW 30-Jun-15 19:01pm

No Problem. We're here to help.

Jaybo007 · Answer 2 · 2015-06-30T13:00:00

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

How Can I Display Only The Current Month records In Dgv On Form Load using acess as the database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How Can I Display Only The Current Month records In Dgv On Form Load using acess as the database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us