Hi Everyone,
I'm facing a problem says
"There is no row at position 0." ... I got this code from this site
Pagination
Really it's so helpful and with some few modifications , i got rid of some problems like 'There is no row at position 0.' but it appears randomly ....
I will have to display all my code to facilitate the sight
Public Function GetData() As DataView
Dim SelectQry = "SELECT BillInfo.InvoiceNo as [رقم الايصال],
ProductSold.SoldDate as [تاريخ البيع],
BillInfo.CustomerName as [اسم العميل],
ProductSold.ProductCode as [كود الصنف],
ProductSold.ProductName as [اسم الصنف],
ProductSold.Price as [سعر التكلفة],
ProductSold.Category as [اسم الباكية],
ProductSold.AVP as [الكمية المتاحة عند البيع],
ProductSold.SellPrice as [سعر البيع],
ProductSold.SoldPackets as [الكمية المباعة],
ProductSold.TotalAmount as [الاجمالى المبيعات من الصنف],
ProductSold.NewAVP as [الكمية المتاحة الجديدة]
FROM BillInfo INNER JOIN ProductSold ON BillInfo.InvoiceNo = ProductSold.InvoiceNo
WHERE SoldDate >= @D1 AND SoldDate <= @D2
ORDER BY SoldDate DESC"
Dim ds As New DataSet
Dim dv As DataView
Try
conn = New OleDbConnection(cs)
Dim cmd As New OleDbCommand()
Dim da = New OleDbDataAdapter()
cmd.CommandText = SelectQry
cmd.Connection = conn
cmd.Parameters.Add("@D1", OleDbType.DBDate).Value = dtpInvoiceDateFrom.Value
cmd.Parameters.Add("@D2", OleDbType.DBDate).Value = dtpInvoiceDateTo.Value
da.SelectCommand = cmd
da.Fill(ds)
dv = ds.Tables(0).DefaultView
Catch ex As Exception
Throw ex
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Return dv
End Function
Private Sub loadDGV(Statement As String, TableName As String)
Using conn As New OleDbConnection(cs)
conn.Open()
Using cmd As New OleDbCommand(Statement, conn)
'cmd.Parameters.Add("@ID", SqlDbType.Int).Value = CInt(Val(txt))
'Set the DataAdapter's query.
da = New OleDbDataAdapter(cmd)
ds = New DataSet()
' Fill the DataSet.
da.Fill(ds, TableName)
'da.Fill(ds, TableName1)
' Set the source table.
dtSource = ds.Tables(TableName)
'dtSource = ds.Tables(TableName1)
'dt = ds1.Tables(TableName1)
End Using
End Using
End Sub
Private Sub LoadPage()
Dim i As Integer
Dim startRec As Integer
Dim endRec As Integer
Dim dtTemp As DataTable
Select Case True
Case DataGridView1.RowCount = Nothing
Exit Sub
End Select
'Duplicate or clone the source table to create the temporary table.
dtTemp = dtSource.Clone
If currentPage = PageCount Then
endRec = maxRec
Else
endRec = pageSize * currentPage
End If
startRec = recNo
'Copy the rows from the source table to fill the temporary table.
For i = startRec To endRec - 1
dtTemp.ImportRow(dtSource.Rows(i))
recNo = recNo + 1
Next
DataGridView1.DataSource = dtTemp
DisplayPageInfo()
End Sub
Private Function CheckFillButton() As Boolean
'Check if the user clicks the "Fill Grid" button.
If pageSize = 0 Then
MessageBox.Show("Set the Page Size, and then click the button!", "Hint")
CheckFillButton = False
Else
CheckFillButton = True
End If
End Function
Private Sub DisplayPageInfo()
txtDisplayPageNo.Text = "Page " & currentPage.ToString & "/ " & PageCount.ToString
End Sub
Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
If Not CheckFillButton() Then Return
btnLast.Enabled = False
btnPrevious.Enabled = True
btnNext.Enabled = False
btnFirst.Enabled = True
' Check if you are already at the last page.
If recNo = maxRec Then
MessageBox.Show("You are at the Last Page!", "Hint")
Return
End If
currentPage = PageCount
recNo = pageSize * (currentPage - 1)
LoadPage()
End Sub
Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
If Not CheckFillButton() Then Return
btnNext.Enabled = True
btnLast.Enabled = True
If currentPage = PageCount Then
recNo = pageSize * (currentPage - 2)
End If
currentPage = currentPage - 1
'Check if you are already at the first page.
If currentPage < 1 Then
MessageBox.Show("You are at the First Page!")
btnPrevious.Enabled = False
btnFirst.Enabled = False
currentPage = 1
Return
Else
recNo = pageSize * (currentPage - 1)
End If
LoadPage()
End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
'If the user did not click the "Fill Grid" button then Return
If Not CheckFillButton() Then Return
btnPrevious.Enabled = True
btnFirst.Enabled = True
'Check if the user clicked the "Fill Grid" button.
If pageSize = 0 Then
MessageBox.Show("Set the Page Size, and then click the button!")
Return
End If
currentPage = currentPage + 1
If currentPage > PageCount Then
currentPage = PageCount
'Check if you are already at the last page.
If recNo = maxRec Then
MessageBox.Show("You are at the Last Page!")
btnNext.Enabled = False
btnLast.Enabled = False
Return
End If
End If
LoadPage()
End Sub
Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
If Not CheckFillButton() Then Return
btnFirst.Enabled = False
btnPrevious.Enabled = False
btnNext.Enabled = True
btnLast.Enabled = True
' Check if you are already at the first page.
If currentPage = 1 Then
MessageBox.Show("You are at the First Page!")
Return
End If
currentPage = 1
recNo = 0
LoadPage()
End Sub
Private Sub btnFill_Click(sender As Object, e As EventArgs) Handles btnFill.Click
Using conn As New OleDbConnection(cs)
conn.Open()
Using cmd As New OleDbCommand("SELECT BillInfo.InvoiceNo as [رقم الايصال],
ProductSold.SoldDate as [تاريخ البيع],
BillInfo.CustomerName as [اسم العميل],
ProductSold.ProductCode as [كود الصنف],
ProductSold.ProductName as [اسم الصنف],
ProductSold.Price as [سعر التكلفة],
ProductSold.Category as [اسم الباكية],
ProductSold.AVP as [الكمية المتاحة عند البيع],
ProductSold.SellPrice as [سعر البيع],
ProductSold.SoldPackets as [الكمية المباعة],
ProductSold.TotalAmount as [الاجمالى المبيعات من الصنف],
ProductSold.NewAVP as [الكمية المتاحة الجديدة]
FROM BillInfo INNER JOIN ProductSold ON BillInfo.InvoiceNo = ProductSold.InvoiceNo
WHERE SoldDate >= @D1 AND SoldDate <= @D2
ORDER BY SoldDate DESC", conn)
cmd.Parameters.Add("@D1", OleDbType.DBDate).Value = dtpInvoiceDateFrom.Value
cmd.Parameters.Add("@D2", OleDbType.DBDate).Value = dtpInvoiceDateTo.Value
'Set the DataAdapter's query.
da = New OleDbDataAdapter(cmd)
ds = New DataSet()
' Fill the DataSet.
da.Fill(ds, "BillInfo")
' Set the source table.
dtSource = ds.Tables("BillInfo")
End Using
End Using
'Set the start and max records.
pageSize = CInt(Val(txtPageSize.Text))
maxRec = dtSource.Rows.Count
PageCount = maxRec \ pageSize
' Adjust the page number if the last page contains a partial page.
If (maxRec Mod pageSize) > 0 Then
PageCount = PageCount + 1
End If
'Initial seeings
currentPage = 1
recNo = 0
' Display the content of the current page.
LoadPage()
End Sub
Private Sub txtPageSize_TextChanged(sender As Object, e As EventArgs) Handles txtPageSize.TextChanged
If Val(txtPageSize.Text) > 100 Then
Exit Sub
End If
Select Case True
Case Val(txtPageSize.Text) = 0 Or txtPageSize.Text = ""
Exit Sub
End Select
btnFill_Click(sender, e)
btnFirst.Enabled = False
btnPrevious.Enabled = False
btnNext.Enabled = True
btnLast.Enabled = True
End Sub
Any suggestions .
Thanks in advance .......................
Regards from Amr Aly
What I have tried:
I tried to avoid the exception by doing this
btnFirst.Enabled = False
btnPrevious.Enabled = False
btnNext.Enabled = True
btnLast.Enabled = True
As the above code says but the exception appears again ..... And now during writing my question an idea came in my mind to avoid the exception but useless
by doing a select case in every button like this
Select Case True
Case DataGridView1.RowCount = Nothing
Exit Sub
Case recNo = maxRec
MessageBox.Show("You are at the Last Page!", "Hint")
Exit Sub
End Select
LoadPage()
Because the error occurred in the LoadPage sub
This is an image of my Exception .. With 'first' button the error is 'There is no row at position 0.'
and with 'next' button the error is 'There is no row at position -50.'
Sorry I forgot to say that the error always occurs in this line
dtTemp.ImportRow(dtSource.Rows(i))