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 can't 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

login.zip

Edited September 3, 2005 by PlausiblyDamp

[PlausiblyDamp]

In your FillTextBox method you aren't 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 won't occur anymore

 

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

 

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