Member Avatar for CodingSource

Dear fellow programmers,

I am trying to build a form that has two datetimepickers (for date range) and one button. When a user clicks the button the datagridview will show results according on the date range (by the two datetimepickers).

This is my code so far:

Dim dbconnection As New OleDbConnection(Get_Constring) 'database path from a module
        Dim connectionstring As String = "SELECT * From EncodingComplete where (DATEVALUE(Date_Bill) >= DATEVALUE('" & DateTimePicker1.Text & "') and DATEVALUE(Date_Bill) <= DATEVALUE('" & DateTimePicker2.Text & "')) order by Date_Bill asc "
        Dim dbadapter As New OleDbDataAdapter(connectionstring, dbconnection)
        Dim clientdataset As New DataSet()
        dbadapter.Fill(clientdataset, "date1")
        Dim clientview As New DataView(clientdataset.Tables("date1"))
        DataGridView1.DataSource = clientview
        DataGridView1.DataBind() 'this is where the error is.. its purpose is to bind results to the datagridview

The error is on DataGridView1.DataBind(). Can somebody help me??

  • 3 Contributors
  • 2 Replies
  • 819 Views
  • 33 Minutes Discussion Span
  • Latest Post Latest Post by Santanu.Das

Dani AI

Generated

Short answer: WinForms DataGridView has no DataBind() method — that method belongs to ASP.NET controls. Setting DataGridView1.DataSource = ... is sufficient to bind. The DataBind() line can be removed; call DataGridView1.Refresh() or bindingSource.ResetBindings(False) only when an explicit refresh is needed.

Clarifying and improving the current approach: the posted code concatenates DateTimePicker.Text into SQL (format and injection risk). A safer pattern is a parameterized query, Using/Dispose for connections, fill a DataTable, then assign a BindingSource to the grid. Example (VB.NET):

Dim sql As String = "SELECT * FROM EncodingComplete WHERE Date_Bill BETWEEN ? AND ? ORDER BY Date_Bill"
Using cn As New OleDbConnection(Get_Constring)
    Using cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("?", OleDbType.Date).Value = DateTimePicker1.Value.Date
        cmd.Parameters.Add("?", OleDbType.Date).Value = DateTimePicker2.Value.Date
        Dim da As New OleDbDataAdapter(cmd)
        Dim dt As New DataTable()
        da.Fill(dt)
        Dim bs As New BindingSource()
        bs.DataSource = dt
        DataGridView1.DataSource = bs
    End Using
End Using

Notes tied to the thread: was correct to mention using a BindingSource for easier sorting/filtering. was right that DataBindings is a read-only property and that SQL needs fixing, but the concern about an explicitly opened connection is unnecessary here because OleDbDataAdapter.Fill will open/close a closed connection automatically; still, explicit Using is recommended.

Troubleshooting tips: verify Date_Bill is a Date/DateTime column (string dates will not compare correctly), check dt.Rows.Count or bs.Count to confirm returned rows, and format the grid column for readability (e.g., DataGridView1.Columns("Date_Bill").DefaultCellStyle.Format = "yyyy-MM-dd").

Recommended Answers

All 2 Replies

Member Avatar for ddanbe

You got to have a DataSource and a BindingSource. See this article.

Member Avatar for Santanu.Das

: DataBindings is a Readonly property of DataGridView object, used to get the databindings of the object. So, remove the line 8.
And also there is a need to modify your SQl Statement. It should be

"SELECT * From EncodingComplete where Date_Bill Between " & DateTimePicker1.Value & " And " & DateTimePicker2.Value & " Order by Date_Bill"
'By default order by cause always producess result in ascending order.
' use of 'asc' is your choice.

Get_Constring should be the connection string variable.
You say that your error arised on the line 8, but you do not post the exception what it showed.
I am surprised, How can your codes run the lines 3 to 7 with a entirely closed connection. I am sure you got the error for the line 3

Dim dbadapter As New OleDbDataAdapter(connectionstring, dbconnection)

because dbconnection was still a closed connection. You didn't try to open it. Open it before the line no 3.

Edited by Santanu.Das
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.