-
Oct 12th, 2014, 11:40 AM
#1
Thread Starter
Addicted Member
Data doesn't retrieve!!!
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').
2. 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!
<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
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
</CODE>
-
Oct 12th, 2014, 11:52 AM
#2
Re: Data doesn't retrieve!!!
Why did you declare a variable i at form scope? People often use a variable like that for iterating in a loop, as you do in the For loop in the Load event, but in such a loop, using a good name isn't all that important. When you add a variable at form scope, you really should give it a name that gives some hint as to what it does. Using a name like i tells nothing about what it is for. Worse yet, you appear to be in the habit of using i as an iterator variable. That's quite common, as most people either use i or x for iterators in for loops. However, if you are in the habit of using i as your iterator, then you really shouldn't use it for anything else, especially not for a form level variable, because the variable will conflict with your habits, as they have in the For loop.
Changing the variable i to something with a better name will get rid of the first problem....and I'm pretty sure it will get rid of the second problem, too. What I believe was happening there was that the For loop in the Load event ran as it was supposed to. At the end of that loop, though, i would be incremented one last time, so i would then be one higher than the number of rows in the datatable. You don't check for this in Next (though it looks like you did at one time), so dt.Rows(i) will be out of range, because i is too high to start with.
If you use a variable called i as a loop iterator, then never use that name for any other variable.
My usual boring signature: Nothing
-
Oct 12th, 2014, 12:08 PM
#3
Re: Data doesn't retrieve!!!
Hello,
The following MSDN article with a demonstration (done in a class and forms project) show how to read images from MS-Access to a DataGridView along with adding new images. What is not shown is databinding to controls but that can easily be done too. I will warn you that the code takes a different path than your current methods and the backend database is MS-Access 2007.
-
Oct 12th, 2014, 12:53 PM
#4
Thread Starter
Addicted Member
Re: Data doesn't retrieve!!!
Originally Posted by Shaggy Hiker
Why did you declare a variable i at form scope? People often use a variable like that for iterating in a loop, as you do in the For loop in the Load event, but in such a loop, using a good name isn't all that important. When you add a variable at form scope, you really should give it a name that gives some hint as to what it does. Using a name like i tells nothing about what it is for. Worse yet, you appear to be in the habit of using i as an iterator variable. That's quite common, as most people either use i or x for iterators in for loops. However, if you are in the habit of using i as your iterator, then you really shouldn't use it for anything else, especially not for a form level variable, because the variable will conflict with your habits, as they have in the For loop.
Changing the variable i to something with a better name will get rid of the first problem....and I'm pretty sure it will get rid of the second problem, too. What I believe was happening there was that the For loop in the Load event ran as it was supposed to. At the end of that loop, though, i would be incremented one last time, so i would then be one higher than the number of rows in the datatable. You don't check for this in Next (though it looks like you did at one time), so dt.Rows(i) will be out of range, because i is too high to start with.
If you use a variable called i as a loop iterator, then never use that name for any other variable.
I tried changing the variable name to "int" but still its the same.
-
Oct 12th, 2014, 12:55 PM
#5
Thread Starter
Addicted Member
Re: Data doesn't retrieve!!!
Originally Posted by kevininstructor
Hello,
The following MSDN article with a demonstration (done in a class and forms project) show how to read images from MS-Access to a DataGridView along with adding new images. What is not shown is databinding to controls but that can easily be done too. I will warn you that the code takes a different path than your current methods and the backend database is MS-Access 2007.
I checked that site and tried it but it didn't work too. I just want to retrieve data from my database. I don't want that datagridview, just want a connection made between the database and vb.net 2010.
Thank You!
-
Oct 12th, 2014, 05:10 PM
#6
Re: Data doesn't retrieve!!!
Show the code after you made the change to the variable name.
The problem is clear, as it states that the index is out of range. That means that either your indexing variable is set wrong, or there are no rows in the datatable. You can check that by taking a look at the datatable when the exception happens.
My usual boring signature: Nothing
-
Oct 13th, 2014, 01:03 AM
#7
Thread Starter
Addicted Member
Re: Data doesn't retrieve!!!
I edited my code but i still get the same error. Can someone please help me!
This is the a snapshot of my database (table).
here is the edited code -
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
-
Oct 13th, 2014, 05:54 AM
#8
Re: Data doesn't retrieve!!!
Originally Posted by ashveen
I checked that site and tried it but it didn't work too. I just want to retrieve data from my database. I don't want that datagridview, just want a connection made between the database and vb.net 2010.
Thank You!
Hello,
The DataGridView is purely for verification that the code works, it is not needed. You can data binding i.e. SomeTextBox.DataBinding.Add(...
-
Oct 13th, 2014, 06:04 AM
#9
Thread Starter
Addicted Member
Re: Data doesn't retrieve!!!
Originally Posted by kevininstructor
Hello,
The DataGridView is purely for verification that the code works, it is not needed. You can data binding i.e. SomeTextBox.DataBinding.Add(...
do you have any sample code for that?
thank you very much!
-
Oct 13th, 2014, 11:53 AM
#10
Re: Data doesn't retrieve!!!
1) Wrap the code in the Load event in an exception handler. It doesn't have to do much, but it would be good to have a messagebox in there to show any exceptions that are raised. The reason for doing this is that, if you are using a 64-bit Windows, then any exceptions thrown in the Load event will behave differently from exceptions thrown anywhere else. Normally, you'd see an error if an exception is thrown and not handled. In the case of an exception thrown in the load event, you won't see anything, but the code in the load event will exit at that point.
This may have nothing to do with the problem, or it may have a lot to do with the problem. If you were throwing an exception in either the datatable.fill, or even the .Open call on the connection, then you'd have no data in the datatable at any time, and you'd never see a message about there being a problem.
2) In what way did you edit your code? You are still misusing the i variable just as you were in the original code. You said that you renamed the variable, and that was what I wanted to see, yet you now post code that is only slightly better than what you originally had. I see that you are setting i back to 0, which would solve part of the problem, but the fundamental mistake is the same: You use i as an iterator variable and you use i in a totally different role as a current row index. That's going to cause you trouble all over the place, so stop doing that. Use i ONLY as a local iterator variable, and use a different name for variables at form scope. You are just asking for trouble by declaring your iterator variable at form scope.
My usual boring signature: Nothing
-
Oct 13th, 2014, 05:59 PM
#11
Thread Starter
Addicted Member
Re: Data doesn't retrieve!!!
Originally Posted by Shaggy Hiker
1) Wrap the code in the Load event in an exception handler. It doesn't have to do much, but it would be good to have a messagebox in there to show any exceptions that are raised. The reason for doing this is that, if you are using a 64-bit Windows, then any exceptions thrown in the Load event will behave differently from exceptions thrown anywhere else. Normally, you'd see an error if an exception is thrown and not handled. In the case of an exception thrown in the load event, you won't see anything, but the code in the load event will exit at that point.
This may have nothing to do with the problem, or it may have a lot to do with the problem. If you were throwing an exception in either the datatable.fill, or even the .Open call on the connection, then you'd have no data in the datatable at any time, and you'd never see a message about there being a problem.
2) In what way did you edit your code? You are still misusing the i variable just as you were in the original code. You said that you renamed the variable, and that was what I wanted to see, yet you now post code that is only slightly better than what you originally had. I see that you are setting i back to 0, which would solve part of the problem, but the fundamental mistake is the same: You use i as an iterator variable and you use i in a totally different role as a current row index. That's going to cause you trouble all over the place, so stop doing that. Use i ONLY as a local iterator variable, and use a different name for variables at form scope. You are just asking for trouble by declaring your iterator variable at form scope.
the code I have written works for my earlier project with the variable "i". I just copied the same code from my earlier project but it won't work, I have no idea of what is happening. You told me to change the variable "i", I did change it to "int" but still the variable is underlined.
Thank You!
-
Oct 13th, 2014, 08:05 PM
#12
Re: Data doesn't retrieve!!!
Where did you change it to int? What is the message that the underline is giving you.
By the way, you shouldn't be guessing as to why it doesn't work. At the point you get the exception, assuming that it is the IndexOutOfRange exception that you mentioned the first time, you have all the information you need...at first. If the line that throws the exception is this one:
TextBox1.Text = dt.Rows(i)(0)
then just hover the mouse over the i...which should be changed to something else, so if you change it to int, then hover it over the int. What is the value of the variable? If it is reasonable, then select dt.Rows (but not the (i)(0)) and press Shift+F9. That will let you see the contents of dt.Rows. What is the row count?
If the exception is not taking you to that line because it is just being caught in the error trap that you have in the click event, then you go into the Debug menu, select Exceptions (or something like that), and check the checkbox for When Thrown for runtime exceptions (but don't bother for any of the others). Then the execution will stop when the exception is thrown rather than going to the exception handler right away. That way you will know right where the exception is happening.
By the way, that exception handler is almost certainly a bad idea. You seem to have it in there solely to catch an overrun. Exception handlers cost nothing if no exceptions are raised, but they are horribly slow if an exception is thrown, so you only want to use them for exceptional situations. You can always check for whether the index is out of range prior to accessing the datatable rather than letting the code throw an exception.
My usual boring signature: Nothing
-
Oct 13th, 2014, 08:17 PM
#13
Re: Data doesn't retrieve!!!
Originally Posted by ashveen
do you have any sample code for that?
thank you very much!
Not in the same project. In another of my articles shows data binding found here.
-
Oct 14th, 2014, 12:40 AM
#14
Thread Starter
Addicted Member
Re: Data doesn't retrieve!!!
Originally Posted by Shaggy Hiker
Where did you change it to int? What is the message that the underline is giving you.
I modified the code and now it works but the only problem is that the image doesn't show on the picturebox.
Can you please help on that.
Thank You!
Code:
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(ByVal sender As System.Object, ByVal 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(ByVal sender As System.Object, ByVal 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(ByVal sender As Object, ByVal 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
-
Oct 14th, 2014, 12:40 AM
#15
Thread Starter
Addicted Member
Re: Data doesn't retrieve!!!
I modified the code and now it works but the only problem is that the image doesn't show on the picturebox1.
Can someone please help on that.
Thank You!
Code:
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(ByVal sender As System.Object, ByVal 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(ByVal sender As System.Object, ByVal 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(ByVal sender As Object, ByVal 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
-
Oct 14th, 2014, 10:55 AM
#16
Re: Data doesn't retrieve!!!
Wrap the code in the Load event in an exception handler and see whether or not it is throwing any exceptions. After all, the easiest answer is that the conversion is failing, and since it is in the Load event, the only way you would know would be that there would be no picture in the picturebox. If there is an exception thrown in there it is likely that you'll never see it.
That doesn't have to be the answer, but it is the first alternative that you need to rule out. The Load event is risky to use for what you are using it for because it may swallow exceptions without you ever knowing about it. An exception handler will at least show you the exceptions.
My usual boring signature: Nothing
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|