Table Query from an Access MS DB

Question

1.00/5 (1 vote)

See more:

I am trying to import the records from an Access database for the current month and need the records to be displayed in my datagridview on form load not a button click. My reason for this is if a end user opens the program it could take some time to load all the records, so I want to only retrieve the records entered for the current month and obviously year when the form loads. I am new to coding hence the reason I'm asking the people on here with loads of experience. I have no idea of who to do this.

VB

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

Posted 21-Jul-15 1:25am

Jaybo007

Updated 21-Jul-15 1:44am

Wendelius

v2

Add a Solution

Comments

Maciej Los 21-Jul-15 16:48pm

Does IDDay is numeric value or string?

Jaybo007 22-Jul-15 0:34am

It is saved on the DB as Text(string). I did change it to number(Integer) but it didn't change anything.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2015-07-21T01:50:00

Solution 1

First of all instead of concatenating a value from textbox to your SQL query always use parameters, see OleDbParameter[^]

What comes to fetching the you can use Year and Month functions. For example if the IDday column is the one you want to compare then something like

VB

Dim rs As New OleDb.OleDbDataAdapter("SELECT * FROM tbLMPH WHERE Year(IDday) = Year(Now()) AND Month(IDday) = Month(Now())", con)

Posted 21-Jul-15 1:50am

Wendelius

Comments

Jaybo007 21-Jul-15 8:29am

Will this work if I have the month and year in 2 seperate columns? & do I need to format these colums to a date format? or can I leave these columns as numbers?

Wendelius 22-Jul-15 1:28am

Do not store the date parts in separate columns. In the long run it will make your life much harder. Use date data type.

Jaybo007 21-Jul-15 8:30am

If so I obviously need to change IDDay to the appropriate columns.

Jaybo007 21-Jul-15 8:40am

Just wondering if this is correct and that I'm understanding what you asking me to change within the code.

Public Sub GetData()
con.Open()
Dim dt As New DataTable("tbLMPH")
Dim rs As New OleDb.OleDbDataAdapter("SELECT * FROM tbLMPH WHERE Year(IDday) = Year(Now()) AND Month(IDday) = Month(Now())", 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

Wendelius 22-Jul-15 1:30am

If IDday is the column containing the date you want to use in comparison, looks good. Just a minor thing: If you want to fetch records starting from this month, use >= in the comparison instead of =