Retrieving data from access database

[bigzos]

Hey,i am working on a module in vb 10..i have to retrive data from the database between two dates...i have used the following code but not able to get ny output,,nt even an error

Imports System.Data.OleDb
Public Class Form4
    Dim con As OleDbConnection
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            con = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=C:/Users/Space Era/Documents/perf.mdb")
            con.Open()
            Dim dataset1 As New DataSet()
            Dim datagridview1 As New DataGridView
            Dim sqlstring As String = "select * from data where Date >= '" & DateTimePicker2.Text & "' and Date<= ' " & DateTimePicker1.Text & "'"
            cmd = New OleDbCommand(sqlstring, con)
            Dim oledbdataadapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlstring, con)
            oledbdataadapter1.Fill(dataset1, "data")

            datagridview1.DataSource = dataset1.Tables("data")

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        con.Close()
    End Sub
End Class

[Mitja Bonca]

You didnt add the newly created dataGridiView to the form. Do it this way:

Dim dgv As New DataGridView()
Me.Controls.Add(dgv)
'this line you are missing

[debasisdas]

and ensure to pass the date of DateTimePicker2 less than that of DateTimePicker1

also you need to parse the date into proper format.

[bigzos]

still no success....here's my modified code:

Imports System.Data.OleDb
Public Class Form4
    Dim con As OleDbConnection
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            con = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=C:/Users/Space Era/Documents/perf.mdb")
            con.Open()
            Dim dgv As New DataGridView()
            Me.Controls.Add(dgv)
            Dim dataset1 As New DataSet()
            Dim datagridview1 As New DataGridView
            Dim sqlstring As String = "select * from data where Date >= '" & DateTimePicker2.Text & "' and Date<= ' " & DateTimePicker1.Text & "'"
            cmd = New OleDbCommand(sqlstring, con)
            Dim oledbdataadapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlstring, con)
            oledbdataadapter1.Fill(dataset1, "data")

            datagridview1.DataSource = dataset1.Tables("data")

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        con.Close()
    End Sub
End Class

(the database has each data type as text..)

[bigzos]

What i am getting is just a data grid with all the columns but "no data"..

[debasisdas]

try to run the sql query with the dates that you are passing in the back end and check if you are getting any records.

[bigzos]

i dont have a sql manager..i am working on vb10...any alternate help would be seriously appreciated

[debasisdas]

run the query in access.

[bigzos]

Same result...just getting all the column titles but no data

[bigzos]

Smbdy plss suggest smthng..i need to finish this module by tomorrow

[Mitja Bonca]

Why you create a new dgv? The new one is again not created.Try this one:

con = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=C:/Users/Space Era/Documents/perf.mdb")
            con.Open()
            Dim dgv As New DataGridView()            
            'here you can set Size, Location, ... and other properties too (but it would be good to create a new, seperate method of DGV creation
            Me.Controls.Add(dgv)
            Dim dataset1 As New DataSet()
            Dim sqlstring As String = "select * from data where Date >= '" & DateTimePicker2.Value & "' and Date<= ' " & DateTimePicker1.Value & "'"
            cmd = New OleDbCommand(sqlstring, con)
            Dim oledbdataadapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(cmd)
            oledbdataadapter1.Fill(dataset1, "data")
 
            datagridview1.DataSource = dataset1.Tables("data")

[bigzos]

thanx but datagridview1 is not declared

[bigzos]

run the query in access.

i tried to run the query in access but got da same result..just the column names but no data....

[bigzos]

It worked ,,i just modified the sql string to
"SELECT data.[S No], data.Date, data.[Server Name], data.Directory, data.Media, data.[Media id], data.Status, data.[Start Time], data.[End Time], data.[Action taken], data.[Backup taken by], data.[Tape Name] FROM data where Date>= '" & DateTimePicker2.Text & "' and Date <'" & DateTimePicker1.Text & "' "

and it worked

anyways,...thnx all

[pixma]

try commenting the Try-Catch-End Try..so that you'll see where the error is..then reply immediately..post the error