[RESOLVED] Problems with the DataGridView!!!

[gautamshaw]

I want to fetch some data from the database to the DataGridView,so i did this code:

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Etech.mdb"
        Dim myConnection As OleDbConnection = New OleDbConnection
        myConnection.ConnectionString = connString
        Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select LAcNo,IssuerName,Creditor,Amount,ROI,EMIAmount,BankAcNo,CreditCardNo,Notes from MLoan", myConnection)
        Dim ds As DataSet = New DataSet
        DataGridView1.Columns(0).DataPropertyName = "LAcNo"
        DataGridView1.Columns(1).DataPropertyName = "IssuerName"
        DataGridView1.Columns(2).DataPropertyName = "Creditor"
        DataGridView1.Columns(3).DataPropertyName = "Amount"
        DataGridView1.Columns(4).DataPropertyName = "ROI"
        DataGridView1.Columns(5).DataPropertyName = "EMIAmount"
        DataGridView1.Columns(6).DataPropertyName = "BankAcNo"
        DataGridView1.Columns(7).DataPropertyName = "CreditCardNo"
        DataGridView1.Columns(8).DataPropertyName = "Notes"
        da.Fill(ds, "MLoan")
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt

Now in the select statement i want to apply a condition that select only those rows from the database where ReminderDate=TodayDate

The date format in my access database is like this:
Attachment 73017
but i cant build up the select statement.....the select statement that i gave above,what will be the where portion then?
ReminderDate=Today.Date will wont work i wish;then what will be the where portion in my select query?

[gautamshaw]

ReminderDate is a column present in my database......
I dont want to show that column in the DataGridView......with the help of that ReminderDate of the database i want to fetch only those columns where the ReminderDate matches the today date.......
How to modify the select query?

[Edneeis]

Select LAcNo,IssuerName,Creditor,Amount,ROI,EMIAmount,BankAcNo,CreditCardNo,Notes from MLoan WHERE ReminderDate BETWEEN CAST(CONVERT(GetDate(), VARCHAR(24),101) + '00:00:00' AS DATETIME) AND CAST(CONVERT(GetDate(), VARCHAR(24),101) + '23:59:59' AS DATETIME)

That should do it. The only funny business is so that the entire day is covered (12am - 11:59 am).

[gautamshaw]

I get this exception:
Attachment 73020

[Edneeis]

Maybe Access doesn't have GetDate() I was thinking SQL.

Try switching GetDate to Now. Actually I'm not sure it has CAST or CONVERT either.

Maybe try this (Sorry my Access is way rusty)
Select LAcNo,IssuerName,Creditor,Amount,ROI,EMIAmount,BankAcNo,CreditCardNo,Notes from MLoan WHERE ReminderDate = DATEVALUE(Now())

[gautamshaw]

This time no rows are selected.....

Any alternative solution?

[Edneeis]

I'm too rusty on Access. The problem is most likely time. You should use a BETWEEN but I don't know if you can concat time onto a date in Access. You'll have to google around for the answer or wait for someone else to answer. It will be something like this:

Select LAcNo,IssuerName,Creditor,Amount,ROI,EMIAmount,BankAcNo,CreditCardNo,Notes from MLoan WHERE ReminderDate BETWEEN DATEVALUE(Now()) + '00:00:00' AND DATEVALUE(Now()) + '23:59:59'

[gautamshaw]

I tried the query that you gave;i get this exception:
Attachment 73021
The datatype of ReminderDate is set to text in the access Database.......
Do i need to change this select query:

Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select LAcNo,IssuerName,Creditor,Amount,ROI,EMIAmount,BankAcNo,CreditCardNo,
Notes from MLoan WHERE ReminderDate BETWEEN DATEVALUE(Now()) + '00:00:00' AND DATEVALUE(Now()) + '23:59:59' ", myConnection)

[gautamshaw]

i just googled through a few pages and i got this:
Attachment 73027
So i modified my query to this:

Dim da As OleDbDataAdapter = New OleDbDataAdapter
("Select LAcNo,IssuerName,Creditor,Amount,ROI,EMIAmount,BankAcNo,
CreditCardNo,Notes from MLoan where ReminderDate=Today ", myConnection)

But when i execute the application,i am getting this exception:
Attachment 73028
Any hints?

[gautamshaw]

I tried this:

 Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Etech.mdb"
        Dim myConnection As OleDbConnection = New OleDbConnection
        myConnection.ConnectionString = connString
        Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select LAcNo,IssuerName,Creditor,Amount,ROI,EMIAmount,BankAcNo,CreditCardNo,Notes from MLoan where ReminderDate=@reminderdate ", myConnection)
        Dim ds As DataSet = New DataSet
        DataGridView1.Columns(0).DataPropertyName = "LAcNo"
        DataGridView1.Columns(1).DataPropertyName = "IssuerName"
        DataGridView1.Columns(2).DataPropertyName = "Creditor"
        DataGridView1.Columns(3).DataPropertyName = "Amount"
        DataGridView1.Columns(4).DataPropertyName = "ROI"
        DataGridView1.Columns(5).DataPropertyName = "EMIAmount"
        DataGridView1.Columns(6).DataPropertyName = "BankAcNo"
        DataGridView1.Columns(7).DataPropertyName = "CreditCardNo"
        DataGridView1.Columns(8).DataPropertyName = "Notes"
        cmd.Parameters.AddWithValue("@reminderdate", Today.ToString)
         da.Fill(ds, "MLoan")
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt

This time i am getting this error:
Attachment 73031
Any suggestion please.....

[gautamshaw]

I gave another try with the code and i came up with this but in vain:

 Try
            Using con As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=Etech.mdb")
                con.Open()
                cmd = New OleDbCommand("Select LAcNo,IssuerName,Creditor,Amount,ROI,EMIAmount,BankAcNo,CreditCardNo,Notes from MLoan where ReminderDate=@RemDate ", con)
                cmd.Parameters.AddWithValue("@RemDate", Date.Now.Date)
                Dim da As New OleDbDataAdapter(cmd)
                Dim ds As DataSet = New DataSet
                da.Fill(ds, "MLoan")
                Dim dt As New DataTable
                Dim row As DataRow
                da.Fill(dt)
                DataGridView1.DataSource = dt
                For Each row In dt.Rows
                    DataGridView1.Columns(0).DataPropertyName = "LAcNo"
                    DataGridView1.Columns(1).DataPropertyName = "IssuerName"
                    DataGridView1.Columns(2).DataPropertyName = "Creditor"
                    DataGridView1.Columns(3).DataPropertyName = "Amount"
                    DataGridView1.Columns(4).DataPropertyName = "ROI"
                    DataGridView1.Columns(5).DataPropertyName = "EMIAmount"
                    DataGridView1.Columns(6).DataPropertyName = "BankAcNo"
                    DataGridView1.Columns(7).DataPropertyName = "CreditCardNo"
                    DataGridView1.Columns(8).DataPropertyName = "Notes"
                    Next
            End Using
        Catch ex As Exception
        End Try

No data is selected from the database......
Need some help
thank you

[gautamshaw]

need some help!!!

[Tealmad]

Do you have your .mdb file? I would like to take a stab at it for you. If so, send it to my email address...

[gautamshaw]

I just gave a snapshot of my mdb file in my very first post.......
Have a look at it

[Edneeis]

First if ReminderDate is set as a Text field then change it. It should be a date not text.
Most likely the problem is that the dates are not exact. In your original screen print the dates have times also, which is normal for a database. So unless the dates have a time of midnight then they wont be an exact match to Date.Now.Date. This is why I suggested the BETWEEN statement in my code earlier.