Ali Muhammad Ali Muhammad - 6 months ago 22
Vb.net Question

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

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!

Answer

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)