DB conn problem after several clicks

[daniel2milan]

    Public Function editpos()
        Me.lblpos.Text = (DataGrid1.CurrentRowIndex + 1) & " dari " & (DataGrid1.VisibleRowCount - 1)
    End Function

  Public Function FillDataGrid(ByVal Sqlstring As String)

        Dim OleDbConn As OleDbConnection = New OleDbConnection(ConnString)
        OleDbConn.Open()

        Dim MyDataSet As DataSet = New DataSet

        Dim MyOleDataAdapter As OleDbDataAdapter = New OleDbDataAdapter
        MyOleDataAdapter.SelectCommand = New OleDbCommand(Sqlstring, OleDbConn)
        MyOleDataAdapter.Fill(MyDataSet)

        Me.DataGrid1.DataSource = MyDataSet.Tables(0)

        For x As Integer = 0 To MyDataSet.Tables(0).Rows.Count - 1
        ListBox1.Items.Add(MyDataSet.Tables(0).Rows(x).Item("Nama"))
        Next

        StatusBar1.Text = " " & MyDataSet.Tables(0).Rows.Count & " Rows."

        MyOleDataAdapter.Dispose()
        MyDataSet.Dispose()
        OleDbConn.Close()
        OleDbConn.Dispose()
    End Function

    Public Function FillTextBox(ByVal Sqlstring As String)


        Dim OleDbConn As OleDbConnection = New OleDbConnection(ConnString)
        OleDbConn.Open()


        Dim MyDataReader As OleDbDataReader
        Dim MyOleDbCommand As OleDbCommand = New OleDbCommand
        MyOleDbCommand.Connection = (OleDbConn)
        MyOleDbCommand.CommandText = Sqlstring

        MyDataReader = MyOleDbCommand.ExecuteReader

        Try
            Do While MyDataReader.Read

                txtID.Text = (MyDataReader.Item(0))
                txtpass.Text = (MyDataReader.Item(1))
                txtnama.Text = (MyDataReader.Item(2))
                txtadd.Text = (MyDataReader.Item(3))
                txtjab.Text = (MyDataReader.Item(4))
                txttgl.Text = (MyDataReader.Item(5))
                txtno.Text = (MyDataReader.Item(6))

            Loop

            StatusBar1.Text = " Record " & TxtId.Text & " selected"

        Catch err As System.Exception
              StatusBar1.Text = " Selected Record Contains Null String"



            MyDataReader.Close()


            OleDbConn.Close()
            OleDbConn.Dispose()
        End Try

    End Function

    Private Sub DataGrid1_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.CurrentCellChanged
        If DataGrid1.CurrentCell.RowNumber >= 0 And DataGrid1.CurrentCell.RowNumber <= DataGrid1.VisibleRowCount - 1 Then
            Dim SqlStr As String
            SqlStr = "Select * from usher where UserID = " & DataGrid1.Item(DataGrid1.CurrentRowIndex, 0) & ""
            FillTextBox(SqlStr)
            editpos()
        Else
        End If
    End Sub

    Private Sub DataGrid1_Click1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGrid1.Click
        Dim SqlStr As String
        SqlStr = "Select * from usher where UserID = " & DataGrid1.Item(DataGrid1.CurrentRowIndex, 0) & ""
        FillTextBox(SqlStr)
        editpos()
    End Sub

i have this code to bind data on datagrid1 to text boxes

this code works but after about 30 clicks or 30 times i press the arrow button the error is about tobe appear n i cant find the problem in my code

can any1 help me???

with this post i attach my file you can download it and give me some clue about it

get focus on the datagrid1 on form2 and then navigate the arrow button several times and the eror will ocurr

user : admin
pass :1234

thx

 

[PlausiblyDamp]

In your FillTextBox method you arent closing your reader if things work only on failures (the close / dispose stuff is in the Catch block). You need to make sure the connection etc. is closed regardless.

Try the following in place of your try / catch block.

Try
    Do While MyDataReader.Read

    txtID.Text = (MyDataReader.Item(0))
    txtpass.Text = (MyDataReader.Item(1))
    txtnama.Text = (MyDataReader.Item(2))
    txtadd.Text = (MyDataReader.Item(3))
    txtjab.Text = (MyDataReader.Item(4))
    txttgl.Text = (MyDataReader.Item(5))
    txtno.Text = (MyDataReader.Item(6))

    Loop

    StatusBar1.Text = " Record " & TxtId.Text & " selected"

    Catch err As System.Exception
              StatusBar1.Text = " Selected Record Contains Null String"

    Finally
        If Not MyDataReader Is Nothing Then
            MyDataReader.Close()
        End If

        If Not OleDbConn Is Nothing Then
            OleDbConn.Close()
            OleDbConn.Dispose()
        End If
End Try

You probably also want to do something similar in your FillDataGrid method just in case any errors occur.

 

[daniel2milan]

thx alot the code really works and the eror wont occur anymore

btw is this a fine way to make next n previous button??

datagrid1.currentrowindex = datagrid1.currentrowindex +1 / -1 ???