VB.NET: Cannot display selected data from access database into datagridview

Question

I have been trying to display selected data from Access database into datagridview on pressing the button but its not displaying any records neither it is showing any error.

Dim third_da As OleDb.OleDbDataAdapter
Dim third_ds As New DataSet


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


    con.Open()
    Dim cb_two As New OleDb.OleDbCommandBuilder(third_da)

    query_three = "SELECT emp_timing.emp_code, emp_timing.day, emp_timing.travel_time, emp_timing.travel_dest,emp_timing.emp_timein,emp_timing.emp_timeout, emp_timing.emp_hours, emp_timing.emp_mins " & _
        "FROM emp_timing WHERE (((emp_timing.emp_code)=" & empcode & ") AND ((emp_timing.day) Like '??/" & ComboBox1.Text & "/20" & TextBox9.Text & "'))"

    ' "WHERE (((emp_timing.emp_code)=22) AND ((emp_timing.day) Like '??/05/2016'))"

    third_da = New OleDb.OleDbDataAdapter(query_three, con)

    third_da.Fill(third_ds, "ets")


    DataGridView1.DataSource = third_ds.Tables("ets")

    con.Close()
    Dim view As New DataView(third_ds.Tables(0))
    source1.DataSource = view
    DataGridView1.DataSource = view

    DataGridView1.ReadOnly = True
    DataGridView1.CancelEdit()

End Sub

Thanks in Advance!

Did you set breakpoints and step through? Also use parameters and turn Option Strict On. — Trevor
– Trevor, Commented May 24, 2016 at 12:02
Your code is vulnerable to SQL Injection check bobby-tables.com for details, use Parameterized queries to prevent this. DId you debug and check what exactly is going wrong? Is the database getting connected? Is the SQL getting generated correctly? Is the generated SQL getting executed in Access properly? — Techie
– Techie, Commented May 24, 2016 at 12:03
my query is working perfect in access and it can get the data. @AbdellahOUMGHAR — Ali Muhammad
– Ali Muhammad, Commented May 24, 2016 at 12:05

Abdellah OUMGHAR · Accepted Answer · 2016-05-24 12:57:34Z

-1

You can change you condition of emp_timing.day field in query like this :

(Month(emp_timing.day) = & ComboBox1.Text & 
                   and Year(emp_timing.day) = "20" & TextBox9.Text & " )

But, I advice you to use the Parameter to avoid SQL injections , like this :

query_three = "SELECT emp_timing.emp_code, emp_timing.day, emp_timing.travel_time, emp_timing.travel_dest,emp_timing.emp_timein,emp_timing.emp_timeout, emp_timing.emp_hours, emp_timing.emp_mins " &
"FROM emp_timing WHERE (((emp_timing.emp_code)= @empcode) AND ((emp_timing.day) BETWEEN @startDate AND @endDate ))"

Dim startDate As New DateTime("20" & TextBox9.Text, ComboBox1.Text, 1)
Dim endDate As DateTime = startDate.AddMonths(1).AddDays(-1)

Dim cmd As New OleDbCommand(query_three, con)
cmd.Parameters.AddWithValue("@empcode", empcode)
cmd.Parameters.AddWithValue("@startDate", startDate.ToString("#yyyy/MM/dd#"))
cmd.Parameters.AddWithValue("@endDate", endDate.ToString("#yyyy/MM/dd#"))
third_da = New OleDb.OleDbDataAdapter(cmd)

edited May 24, 2016 at 12:57

answered May 24, 2016 at 12:40

Abdellah OUMGHAR

3,7751 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ali Muhammad Over a year ago

Thanks a ton brother it worked finally :) here is the query:

"SELECT * FROM emp_timing WHERE (((emp_timing.emp_code)=" & empcode & ") AND (Month(emp_timing.day) =" & ComboBox1.Text & "and Year(emp_timing.day) = 20" & TextBox9.Text & " ))"

Ali Muhammad Over a year ago

Can you please explain why you wrote Month in this: (Month(emp_timing.day) and year in this: Year(emp_timing.day)

Abdellah OUMGHAR Over a year ago

Month to get month and year to get year of date field in database and compare it with value in combobox and textbox, because you want to get all record that have date in month for example 05/2016.

Ali Muhammad Over a year ago

Brother you are awesome :))

Abdellah OUMGHAR Over a year ago

I am glad to hear it! Thank you too :)

Collectives™ on Stack Overflow

VB.NET: Cannot display selected data from access database into datagridview

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related