Data doesn't retrieve!!!

Question

Ashveen96 0 Newbie Poster

10 Years Ago

Hey!
I'm trying to connect my backend (Ms Access) to the front end (VB.NET 2010). I'm trying to retrieve data and I get these errors.
I have two problems:
1. For i = 0 To dt.Rows.Count - 1

when typed the above code in the load form I get the letter i underlined.

this is the error - Warning 1 The type for variable 'i' will not be inferred because it is bound to a field in an enclosing scope. Either change the name of 'i', or use the fully qualified name (for example, 'Me.i' or 'MyBase.i').

When I debugged/run the code, the code doesn't work and I get this error
This code is higlighted --------
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
TextBox1.Text = dt.Rows(i)(0)
End Sub

Error name - IndexOutOfRangeException was unhandled
There is no row at position 0.

Can someone please help me!
Thank You!

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Byte
Imports System.String
Public Class frmBirthdayCake

Private sql = "SELECT CakeID,Cake_Name,Cake_Description,Weight,Price,Image,ShelfLife,Size,NoOfServings FROM Birthday_Cake"
Private dt As New DataTable
Dim i As Integer
Private adapter As New OleDbDataAdapter(sql, con)
Dim ms As New IO.MemoryStream
Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CakeAlbum.accdb")

Private Sub frmBirthdayCake_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
adapter.Fill(dt)
DataGridView1.DataSource = dt
DirectCast(DataGridView1.Columns("Image"), DataGridViewImageColumn).ImageLayout = DataGridViewImageCellLayout.Stretch
For i = 0 To dt.Rows.Count - 1
Dim row As DataGridViewRow = DataGridView1.Rows(i)
row.Height = 60
Next
DataGridView1.Columns("Image").Width = 150
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
'If i < dt.Rows.Count - 1 Then
If TextBox1.Text = "" Then
i = 0
TextBox1.Text = dt.Rows(i)(0)
TextBox2.Text = dt.Rows(i)(1)

Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
Using ms As New IO.MemoryStream(imagebytes)
PictureBox1.Image = Image.FromStream(ms)
PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
End Using
Else
Try
i += 1
TextBox1.Text = dt.Rows(i)(0)
TextBox2.Text = dt.Rows(i)(1)

Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
Using ms As New IO.MemoryStream(imagebytes)
PictureBox1.Image = Image.FromStream(ms)
PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
End Using
Catch
i -= 1
MsgBox("End Of Records")
End Try
End If
End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
If TextBox1.Text = "" Then
Else

If i = dt.Rows.Count - 1 OrElse i <> 0 Then
i -= 1
TextBox1.Text = dt.Rows(i)(0)
TextBox2.Text = dt.Rows(i)(1)

Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
Using ms As New IO.MemoryStream(imagebytes)
PictureBox1.Image = Image.FromStream(ms)
PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
End Using
Else
MsgBox("This is the first record")
End If
End If
End Sub
Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
con.Open()
Dim sql = "SELECT Image FROM Birthday_Cake where CakeID='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
adapter = New OleDbDataAdapter(sql, con)
Dim d As New DataTable
adapter.Fill(d)
TextBox1.Text = d.Rows(i)(0)
con.Close()
End Sub
End Class

vb.net

6 Contributors
14 Replies
338 Views
1 Day Discussion Span
Latest Post 10 Years Ago Latest Post by Ashveen96

cgeier 187 Junior Poster

10 Years Ago

In "frmBirthdayCake_Load", the connection is not open. Where is con.Open?

Edited 10 Years Ago by cgeier

Ashveen96 commented: i tried it, still same error +0

rubberman 1,355 Nearly a Posting Virtuoso

10 Years Ago

Try initializing the variable 'i' first before using it.

Ashveen96 commented: I already have initialized it as Dim i as Integer. but still i get the same error. +0

cgeier 187 Junior Poster

10 Years Ago

Change lines 40-42

from:

i += 1
TextBox1.Text = dt.Rows(i)(0)
TextBox2.Text = dt.Rows(i)(1)

to:

'last index is dt.Rows.Count - 1
'only increment if doing so
'would not exceed the index
If i < dt.Rows.Count - 2 Then
    i += 1
Else
    'set to the last row
    i = dt.Rows.Count - 1
End If

If dt IsNot Nothing Then
   TextBox1.Text = dt.Rows(i)(0)
   TextBox2.Text = dt.Rows(i)(1)
End If

Ashveen96 commented: i tried it, still same error +0

Santanu.Das 125 Santanu Das

10 Years Ago

Your codes are too some correct. but you did some mistakes to initialize the variable "i" and should be open the connection.

In DataGridView1_CellMouseClick event you did not do that.

 i = e.RowIndex
        TextBox1.Text = dt.Rows(i)(0)
        TextBox2.Text = dt.Rows(i)(1)

and also in form_Load event you did not do

 con.Open()
        adapter.Fill(dt)
        DataGridView1.DataSource = dt
        'DirectCast(DataGridView1.Columns("Image"), DataGridViewImageColumn).ImageLayout = DataGridViewImageCellLayout.Stretch
        For i = 0 To dt.Rows.Count - 1
            Dim row As DataGridViewRow = DataGridView1.Rows(i)
            'row.Height = 60
        Next
        i = 0

Here I do some modifications in your code. Please change the database name in Connection String, Table Name and also Fields in SQL Statement.
I also inactivates of some code lines. Please do that as per your requirment. mainly for pictures.

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Byte
Imports System.String
Public Class Form1
    Private sql = "SELECT UserFirstName, UserLastName FROM Table1"
    Private dt As New DataTable
    Dim i As Integer

    Dim ms As New IO.MemoryStream
    Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\xxxx.accdb")
    Private adapter As New OleDbDataAdapter(sql, con)

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.Open()
        adapter.Fill(dt)
        DataGridView1.DataSource = dt
        'DirectCast(DataGridView1.Columns("Image"), DataGridViewImageColumn).ImageLayout = DataGridViewImageCellLayout.Stretch
        For i = 0 To dt.Rows.Count - 1
            Dim row As DataGridViewRow = DataGridView1.Rows(i)
            'row.Height = 60
        Next
        i = 0
        'DataGridView1.Columns("Image").Width = 150
    End Sub

     Private Sub btnNext_Click(sender As System.Object, e As System.EventArgs) Handles btnNext.Click
        If TextBox1.Text = "" Then
            i = 0
            TextBox1.Text = dt.Rows(i)(0)
            TextBox2.Text = dt.Rows(i)(1)
            'Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
            'Using ms As New IO.MemoryStream(imagebytes)
            'PictureBox1.Image = Image.FromStream(ms)
            'PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            'End Using
        Else
            Try
                i += 1
                TextBox1.Text = dt.Rows(i)(0)
                TextBox2.Text = dt.Rows(i)(1)
                'Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
                'Using ms As New IO.MemoryStream(imagebytes)
                'PictureBox1.Image = Image.FromStream(ms)
                'PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
                'End Using
            Catch
                i -= 1
                MsgBox("End Of Records")
            End Try
        End If
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If TextBox1.Text = "" Then
        Else
            If i = dt.Rows.Count - 1 OrElse i <> 0 Then
                i -= 1
                TextBox1.Text = dt.Rows(i)(0)
                TextBox2.Text = dt.Rows(i)(1)
                'Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
                'Using ms As New IO.MemoryStream(imagebytes)
                'ureBox1.Image = Image.FromStream(ms)
                'PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
                'End Using
            Else
                MsgBox("This is the first record")
            End If
        End If
    End Sub
    Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        'con.Open()
        'Dim sql = "SELECT Image FROM Birthday_Cake where CakeID='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
        'adapter = New OleDbDataAdapter(sql, con)
        'Dim d As New DataTable
        'adapter.Fill(d)
        i = e.RowIndex
        TextBox1.Text = dt.Rows(i)(0)
        TextBox2.Text = dt.Rows(i)(1)
        'con.Close()
    End Sub


End Class

Ashveen96 commented: i tried it, still same error +0

Reply to this topic

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.

I already have initialized it as Dim i as Integer. but still i get the same error.

Ashveen96 0 Newbie Poster · Answer 1 · 2014-10-12T17:45:04+00:00

I tried everything you all said but I still get the same error.

oussama_1 39 Posting Whiz in Training · Answer 2 · 2014-10-12T18:49:06+00:00

You need to target the table inside the datatable:
If i < dt.Tables(0).Rows.Count - 1 Then

Ashveen96 0 Newbie Poster · Answer 3 · 2014-10-13T05:58:20+00:00

I tired everything above and still I get the same error, the variable i is still underlined and the when I debug the program, the data doesn't retrieve.
This is the a snapshot of my database (table).
3264dbeb6c1a5168233d4544bb7747b3

Can someone please give me a clear solution to this.
Thank You!

Here is my edited code:-

Imports System.Data
    Imports System.Data.OleDb
    Imports System.IO
    Imports System.Byte
    Imports System.String
    Public Class frmBirthdayCake
        Private sql = "SELECT CakeID,Cake_Name,Cake_Description,Weight,Price,Image,ShelfLife,Size,NoOfServings FROM Birthday_Cake"
        Private dt As New DataTable
        Dim i As Integer
        Dim ms As New IO.MemoryStream
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CakeAlbum.accdb")
        Private adapter As New OleDbDataAdapter(sql, con)
         Private Sub frmBirthdayCake_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            con.Open()
            adapter.Fill(dt)
            DataGridView1.DataSource = dt
            DirectCast(DataGridView1.Columns("Image"), DataGridViewImageColumn).ImageLayout = DataGridViewImageCellLayout.Stretch
            For i = 0 To dt.Rows.Count - 1
                Dim row As DataGridViewRow = DataGridView1.Rows(i)
                row.Height = 60
            Next
            i = 0
            DataGridView1.Columns("Image").Width = 150
        End Sub

        Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
            If TextBox1.Text = "" Then
                i = 0
                TextBox1.Text = dt.Rows(i)(0)
                TextBox2.Text = dt.Rows(i)(1)

                Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
                Using ms As New IO.MemoryStream(imagebytes)
                    PictureBox1.Image = Image.FromStream(ms)
                    PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
                End Using
            Else
                Try

                    TextBox1.Text = dt.Rows(i)(0)
                    TextBox2.Text = dt.Rows(i)(1)

                    Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
                    Using ms As New IO.MemoryStream(imagebytes)
                        PictureBox1.Image = Image.FromStream(ms)
                        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
                    End Using
                Catch
                    i -= 1
                    MsgBox("End Of Records")
                End Try
            End If
        End Sub

        Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
            If TextBox1.Text = "" Then
            Else

                If i = dt.Rows.Count - 1 OrElse i <> 0 Then
                    i -= 1
                    TextBox1.Text = dt.Rows(i)(0)
                    TextBox2.Text = dt.Rows(i)(1)

                    Dim imagebytes As Byte() = CType(dt.Rows(i)(2), Byte())
                    Using ms As New IO.MemoryStream(imagebytes)
                        PictureBox1.Image = Image.FromStream(ms)
                        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
                    End Using
                Else
                    MsgBox("This is the first record")
                End If
            End If
        End Sub
        Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
            con.Open()
            Dim sql = "SELECT Image FROM Birthday_Cake where CakeID='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
            adapter = New OleDbDataAdapter(sql, con)
            Dim d As New DataTable
            adapter.Fill(d)
            i = e.RowIndex
            TextBox1.Text = dt.Rows(i)(0)
            TextBox2.Text = dt.Rows(i)(1)
            con.Close()
        End Sub
End Class

Santanu.Das 125 Santanu Das · Answer 4 · 2014-10-13T11:07:02+00:00

Ohoooo! I forgot to tell you that don't open connection in DataGridView1_CellMouseClick event. At the load event Connection is already open and adapter, Data Tabel already filled. So, why are you trying to open a opened Connection. This is your main Error. Remove the following lines from DataGridView1_CellMouseClick event.

**********************REMOVE THESE LINES**********************
con.Open()
            Dim sql = "SELECT Image FROM Birthday_Cake where CakeID='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
            adapter = New OleDbDataAdapter(sql, con)
            Dim d As New DataTable
            adapter.Fill(d)
*****************************************************************

You Want to Show the Pictures. You already loaded the pictures into the DataGridView Object, Pick it and show it. You have no need to open table to do it. If you try to collect the picture from the Table look to the following.

    Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
    i = e.RowIndex
    TextBox1.Text = dt.Rows(i)(0)
    TextBox2.Text = dt.Rows(i)(1)


    Dim sql = "SELECT Image FROM Birthday_Cake where CakeID='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
    adapter = New OleDbDataAdapter(sql, con)
    Dim d As New DataTable
    adapter.Fill(d)

    'Write your codes to show pictures
    'DataTable Row and Column Address both would be 0
    'i.e. d.Rows(0)(0)


    d.Clear()
    d.Dispose()

End Sub

Connection is already open. Close it at FormClosing Event.

    Private Sub Form1_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Me.Dispose()
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        con.Close()
        con.Dispose()
    End Sub

Hope you should get the success.

Ashveen96 0 Newbie Poster · Answer 5 · 2014-10-13T13:26:21+00:00

I tried removed that code and debugged the program but still I get the same error.
Can you please send me a sample code to retrieve data from the database to the form. My form contains a picture box and 7 textboxes that needed to be filled and some navigation buttons.
Thank You!!

Santanu.Das 125 Santanu Das · Answer 6 · 2014-10-13T14:51:38+00:00

The codes I posted above run correctly in my mechine.
Now I post a simple code to retrive the data. Here I create a Database (xxxx.accdb) Table Name is Table1, Fields are UserFirstName and UserLastName.
The code is follows.

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Public Class Form1
    Private sql = "SELECT UserFirstName, UserLastName FROM Table1"
    Private dt As New DataTable
    Dim i As Integer = 0

    Dim ms As New IO.MemoryStream
    Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\xxxx.accdb")
    Private adapter As New OleDbDataAdapter(sql, con)


    Private Sub Form1_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Me.Dispose()
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        con.Close()
        con.Dispose()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.Open()
        adapter.Fill(dt)
        DataGridView1.DataSource = dt
        i = 0
    End Sub

     Private Sub btnNext_Click(sender As System.Object, e As System.EventArgs) Handles btnNext.Click
        If TextBox1.Text = "" Then
            i = 0
            TextBox1.Text = dt.Rows(i)(0)
            TextBox2.Text = dt.Rows(i)(1)
        Else
            Try
                i += 1
                TextBox1.Text = dt.Rows(i)(0)
                TextBox2.Text = dt.Rows(i)(1)
            Catch
                i -= 1
                MsgBox("End Of Records")
            End Try

        End If
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If TextBox1.Text = "" Then
        Else
            If i = dt.Rows.Count - 1 OrElse i <> 0 Then
                i -= 1
                TextBox1.Text = dt.Rows(i)(0)
                TextBox2.Text = dt.Rows(i)(1)
            Else
                MsgBox("This is the first record")
            End If
        End If
    End Sub
    Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick

        i = e.RowIndex
        TextBox1.Text = dt.Rows(i)(0)
        TextBox2.Text = dt.Rows(i)(1)


        Dim sql = "SELECT UserLastName FROM Table1 where UserFirstName='" & DataGridView1.CurrentRow.Cells(0).Value & "'"
        adapter = New OleDbDataAdapter(sql, con)
        Dim d As New DataTable
        adapter.Fill(d)

        If d.Rows.Count > 0 Then
            MsgBox("Your Name is " & StrConv(DataGridView1.CurrentRow.Cells(0).Value & " " & d.Rows(0)(0), VbStrConv.ProperCase) & ".")
        End If

        d.Clear()
        d.Dispose()

    End Sub

End Class

Ashveen96 0 Newbie Poster · Answer 7 · 2014-10-13T15:15:37+00:00

Ashveen96 0 Newbie Poster

10 Years Ago

does this code retrieve images too??

Santanu.Das 125 Santanu Das · Answer 8 · 2014-10-13T15:18:12+00:00

Santanu.Das 125 Santanu Das

10 Years Ago

Are you satisfied about retriving data?

Ashveen96 commented: yes but I want text and image. Thank You +0

Oxiegen 88 Basically an Occasional Poster Featured Poster · Answer 9 · 2014-10-13T16:13:46+00:00

This is a fully working code.
I noticed one thing that caused problems. The column name Size is a reserved word, so you need to put brackets [] around it.

Imports System
Imports System.IO
Imports System.Text
Imports System.Data.OleDb

Public Class frmBirthdayCake
    Private sql As String = "SELECT CakeID,Cake_Name,Cake_Description,Weight,Price,Image,ShelfLife,[Size],NoOfServings FROM Birthday_Cake"
    Private dt As New DataTable
    Private con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CakeAlbum.accdb")
    Private adapter As New OleDbDataAdapter(sql, con)
    Dim index As Integer

    Private Sub frmBirthdayCake_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.Open()
        adapter.Fill(dt)

        DataGridView1.DataSource = dt
        DirectCast(DataGridView1.Columns("Image"), DataGridViewImageColumn).ImageLayout = DataGridViewImageCellLayout.Stretch

        For i As Integer = 0 To dt.Rows.Count - 1
            Dim row As DataGridViewRow = DataGridView1.Rows(i)
            row.Height = 60
        Next

        DataGridView1.Columns("Image").Width = 150
        con.Close()
    End Sub

    Private Sub btnNext_Click(sender As System.Object, e As System.EventArgs) Handles btnNext.Click
        If TextBox1.Text = "" Then
            index = 0
        ElseIf index = dt.Rows.Count - 1 Then
            Exit Sub
        Else
            index += 1
        End If

        TextBox1.Text = dt.Rows(index)(0)
        TextBox2.Text = dt.Rows(index)(1)
        ReadImage()
    End Sub

    Private Sub btnPrevious_Click(sender As System.Object, e As System.EventArgs) Handles btnPrevious.Click
        If TextBox1.Text = "" OrElse index = 0 Then
            index = 0
        ElseIf index = dt.Rows.Count - 1 OrElse index <> 0 Then
            index -= 1
        End If

        TextBox1.Text = dt.Rows(index)(0)
        TextBox2.Text = dt.Rows(index)(1)
        ReadImage()
    End Sub

    Private Sub ReadImage()
        Try
            Dim imageBytes() As Byte = CType(dt.Rows(index)(2), Byte())
            Using ms As New MemoryStream(imageBytes)
                PictureBox1.Image = Image.FromStream(ms)
                PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            End Using
        Catch ex As Exception

        End Try
    End Sub

    Private Sub DataGridView1_CellMouseClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        TextBox1.Text = dt.Rows(e.RowIndex)(0)
        TextBox2.Text = dt.Rows(e.RowIndex)(1)
    End Sub
End Class

Ashveen96 0 Newbie Poster · Answer 10 · 2014-10-13T23:18:45+00:00

Finally it works!!!!

Thank You Oxiegen your code works.
thanks a lot to everyone who helped me.