datagridview problem

hello every one!!
i am running a program in which i am loading data set in datagridview by selecting date range, the thing that goes the other way is this that no data is coming out on the datagridview as per selection where as if i apply
"select * from 'table name'" every thing shows up.
i am forwarding the code, some one please help.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim str As String
cn = New OleDbConnection(con_str)
cn.Open()
Try
str = "select Transaction,Debit,Credit,Transaction_Date,Transaction_Details from AM_Table where Transaction_Date BETWEEN " & DateTimePicker1.Value.Date & " AND " & DateTimePicker2.Value.Date
cmd = New OleDbCommand(str, cn)
Dim dataadapter As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim ds As DataSet = New DataSet()
dataadapter.Fill(ds, "AM_Table")
cn.Close()
DataGridView1.DataSource = ds.Tables(0)
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub


can some help me soon.
thanx

Comments

  • In your select query you can try by calling the date values between single quotes....Without using sinqle quotes it must be considering the value 01/01/1900

    For e.g.
    SELECT * FROM table_name WHERE date1 between between '12/02/2008' and '24/02/2009'
  • i've tried it but it gives 'datatype mismatch criteria' error/exception..
    where as when i am using the query that i mentioned in the code, it atleast brings column names but not data, where as when i am using single quptes it provides the above error..
  • in database it is storing in mdy format that means it is storing date as '2/16/2009' for today's date...
    can any one provide me its solution...
    thanx..
  • seancampbellseancampbell Pennsylvania, USA
    I would try using this SQL statement instead:

    [code]
    str = "SELECT Transaction, Debit, Credit, Transaction_Date, Transaction_Details FROM AM_Table WHERE Transaction_Date >= '" & DateTimePicker1.Value.Date & "' AND Transaction_Date <= '" & DateTimePicker2.Value.Date & "'"
    [/code]

    This assumes that DateTimePicker1 is the begin date, and DateTimePicker2 is the end date.

    Happy coding
  • i've tried, but is there any way to access the date in correct format of date, coz i am using MS ACCESS 2003 and it stores the date in mdy format..
    the same error appears, that is of 'data type mismatch criteria'..
  • Ok......You can try by splitting the date and then calling the date in query.....As per my knowledge you must be entering the date in the format of e.g.24/05/2009 which is not acceptable in MS Access

    So you split the date like below:

    lbldate.Text = Date.Now.Date
    If lbldate.Text.Trim <> String.Empty Then
    lbldate.Text = lbldate.Text.Split("/")(1) & "/" & lbldate.Text.Split("/")(0) & "/" & lbldate.Text.Split("/")(2)
    End If

    Note: 0 indicates date, 1 indicates Month, 2 indicates year

    and now call in your sqlquery like
    SELECT * from table_name where date between '05/24/2009' and '05/30/2009'

    This should definitely solve your problem.

  • Ok......You can try by splitting the date and then calling the date in query.....As per my knowledge you must be entering the date in the format of e.g.24/05/2009 which is not acceptable in MS Access

    So you split the date like below:

    lbldate.Text = Date.Now.Date
    If lbldate.Text.Trim <> String.Empty Then
    lbldate.Text = lbldate.Text.Split("/")(1) & "/" & lbldate.Text.Split("/")(0) & "/" & lbldate.Text.Split("/")(2)
    End If

    Note: 0 indicates date, 1 indicates Month, 2 indicates year

    and now call in your code like
    SELECT * from table_name where date between '05/24/2009' and '05/30/2009'

    This should definitely solve your problem.

  • seancampbellseancampbell Pennsylvania, USA
    Firstly, the main problem was being missed here, and that is that we are writing SQL for MS Access, not for SQL Server (as I arogantly didn't ask, dumb me). Secondly, Shushma your code would work, but there is a much more practical way to do this. The Format command will work nicely here.

    In MS Access you must declare date ranges enclosed in hash marks (#). So I would say "Table.Date >= #01/01/2009# AND Table.Date <= #01/01/2009#" To acheive this, we should use the format command. Also, I beleive you can send the DateTimePicker1.Value.ToString in hash marks to MS Access 2003 and it 'should' work, but since I am not sure if it would or not....

    Here is the code broken out and explained:
    [code]
    Dim D1 as String
    Dim D2 as String

    D1 = Format(DateTimePicker1.Value, "MM/dd/yyyy")
    D2 = Format(DateTimePicker2.Value, "MM/dd/yyyy")

    'D1 now will be in this format "01/01/2009"
    'D2 now will be in this format "01/02/2009"
    'MM is used to designate two numbers for month
    'if you used mm it would put in the minutes part of the time
    'because VB date objects are Date and Time, it might return 00
    'because it normally selects 12:00:00 AM when you select a date
    'with the DateTimePicker
    'If you do this: M/d/yy
    'Then the format: 1/1/09
    'If you do this: MM/yy
    'Then the format 01/09
    'Lookup the Format command in the MSDN, it has some awesome
    'applications for formatting numbers in strings.

    'Now inside the SQL Statement, I am going to add in the hash marks
    'instead of the single quotes (') I used before
    str = "SELECT Transaction, Debit, Credit, Transaction_Date, Transaction_Details FROM AM_Table WHERE Transaction_Date >= #" & D1 & "# AND Transaction_Date <= #" & D2 & "#"
    [/code]
    Now you can use the following code for a 1 liner, because format can be used inline.
    [code]
    str = "SELECT Transaction, Debit, Credit, Transaction_Date, Transaction_Details FROM AM_Table WHERE Transaction_Date >= #" & Format(DateTimePicker1.Value, "MM/dd/yyyy") & "# AND Transaction_Date <= #" & Format(DateTimePicker2.Value, "MM/dd/yyyy") & "#"
    [/code]
  • seancampbellseancampbell Pennsylvania, USA
    data type mismatch is because of not using hash marks, my reply to sushma sums it up.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories