Filling DataGridView

[holisticsam]

I'd like to delete and add data to a access database using DataGridView1. I know there's a more elegant solution using OleDb.OleDbConnection but I want to do something a little simplier.

So this is what I did so far. I added a DataGridView1 linking to an access database.

i=0
Do while ... end of text file
Read first line from text file
DataGridView1.Item(1, i).Value = line_by_line
i = i+1
Loop

The problem is if the data from the text file is longer that the number of rows in the datagridview I get an error. If the text file is shorter, the remaining existing data in the datagridview is not cleaned out. Any suggestions?

[jmcilhinney]

You seem to be under some misconceptions. A DataGridView has nothing whatsoever to do with databases. It is simply a way to display data to the user in tabular form and allow them to edit that data. Where the data comes from before it's displayed and where it goes to after it's edited is absolutely nothing to do with the grid itself. That's like saying that you want to bake a cake with your fridge because that's where you're going to store it. You still need to use an oven to bake the cake, whether you're going to store it in the fridge or not.

So, if you want to retrieve data from an Access database then you need an OleDbConnection. No ifs, not buts. You connect to the database, execute a query and retrieve the result set. Only once you've done all that can you display the data you retrieved in the DataGridView. The user can then edit the data in the grid as desired. You must then get that from the grid, connect to the database again and send the changes back to the database.

The logical way to do all that is with an OleDbDataAdapter. You need the connection object and the adapter and you can then Fill a DataTable. You can bind that table to the grid to display the data to the user and let them edit it. All change they make will automatically be pushed to the DataTable. Once they're done, you can use the same adapter to Update the database. For an example of the data access part of that, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

[holisticsam]

I'm not very experience in setting up a DataGridView so I was not describing my setup clearly. When I added the DataGridView, VB 2008 wizard walked me through a process that connected my access database to the DataGridView. The DataSet, BindingSource and TableAdapter were created.

I meant I didn't want to add the OleDbConnection using code. So back to my original question, I'd like to delete all the rows in the DataGridView, add data found in a text file and update the database.

I was able to find this code to clear all the rows in the DataGridView
EbaycompetitoranalyzerproductsearchBindingSource.DataSource = Nothing
DataGridView1.Rows.Clear()

I'm having problems adding rows now because I unbinded the DataSource.

--> This doesn't work.
Dim row As String() = New String() {"1", "Product 1", "1000"}
DataGridView1.Rows.Add(row)

[jmcilhinney]

You don't want to set the DataSource to nothing. The DataSource is effectively the DataTable that contains the data coming from the database, going to the database or both. You want to send data to the database so you need that DataTable bound to the grid.

Basically, you should not be working with the grid at all. The grid is basically irrelevant to you working in code. The grid is there for the user, to display data to them and to allow then to add, edit and delete data. If you're working in code then you should be working with the DataTable exclusively.

First of, you don't need to clear anything. There's no point getting all the data from the database and then discarding it. If you don't want to retrieve data from the database then don't. Your DataTable starts empty and, if you don't Fill it, it will remain empty.

You should then be adding rows directly to the DataTable. That data will be displayed in the grid because it's bound to the table but you never actually touch the grid in code. Your code will look something like this:

Dim table = MyTypedDataSet.SomeTable
Dim row = table.NewSomeRow()
 
row.SomeField = someValue
row.SomeOtherField = someOtherValue
table.AddSomeRow(row)

The names will be specific to your data but the pattern will be much like that. If you have multiple rows to add then you'll use a loop. When you're done you use the TableAdapter to Update the database with the changes contained in the DataTable.

[holisticsam]

I'm not clear as to what is entered for NewSomeRow() and AddSomeRow(row). I entered a number as NewSomeRow but that's not correct. Rows do not have field names like columns.

I know what to enter here -->
Dim table = MyTypedDataSet.SomeTable
row.SomeField = someValue
row.SomeOtherField = someOtherValue

Please clarify for these lines -->
Dim row = table.NewSomeRow()
table.AddSomeRow(row)

[dunfiddlin]

Good old jmc. Helpful as ever!

Public Class Form1
 
    Dim dsTable As DataTable = New DataTable 'create a table
 
 
    
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 
        For i = 0 To 3
            dsTable.Columns.Add() 'give it some fields (you can add names/headers if you want to)
        Next
 
 
    For i = 0 To 3 'create new row (repeat this section to add all your rows)
        Dim row As DataRow = dsTable.NewRow 
 
        With row
            .SetField(0, "This") 'add values to each column in turn (you'll be getting these from your file)
            .SetField(1, 1)
            .SetField(2, "is")
            .SetField(3, i)
        End With
 
        dsTable.Rows.Add(row) 'add the row to the table
   Next
 
        DataGridView1.DataSource = dsTable 'bind the data grid view
 
    End Sub
End Class

[holisticsam]

dunfiddlin, Thank you for your great reply. It works.

[jmcilhinney]

Good old jmc. Helpful as ever!

Good old dunfiddlin, thinks he knows everything as usual. The OP has specified that they used the Data Source wizard, meaning that they have a typed DataSet. As a result, all the type names and method names have been generated specifically for the OP's data and we don't know what they are. That would explain why I used example names and specifically stated that the names the OP needed to use would be different. The code you have provided is for an untyped DataSet, so it ignores the features provided by the typed DataSet and therefore renders the creation of a typed DataSet pointless. It will work but it is not the code that should be used in this case.

If the OP had cared to look they would have seen that each typed DataTable has the NewRow method inherited from its DataTable base class and it also has a NewSomeSpecificRow method as well, where the SomeSpecific part depends on the table it was generated from. Where the NewRow method returns an untyped DataRow, the specific method returns a typed DataRow, thus providing you the ability to use the features specific to that type. For example, if I was to create a Person table with FirstName and LastName columns then using a typed DataSet for that would look like this:

Dim table = Me.ExampleDataSet.Person
Dim row = table.NewPersonRow()
 
row.FirstName = "Joe"
row.LastName = "Bloggs"
 
table.AddPersonRow(row)

Notice how that code EXACTLY follows the pattern that I provided earlier but the names are specific to the table in my database. That's why I was not able to provide specific names previously and why I specifically stated that the names would be different in each case. If anyone would care to simply type the dot followed by New or Add then Intellisense would display what matching methods were available. If you're going to use a typed DataSet then use a typed DataSet. If you're going to use dunfiddlin's code then you may as well not use a typed DataSet in the first place. Hopefully there's more than one person who can learn something from this.

[dunfiddlin]

I refer the honourable gentleman to the reply from the OP given some moments before his own. The OP did indeed specify that he had originally used the Data Source Wizard but he also made it abundantly clear that he did not find that that was how he wanted to approach this problem. All question of correctly interpreting the true needs of the OP aside, however, the fact remains that your code example was misleading and confusing without the necessary context. I had no little difficulty in eradicating the wavy blue lines myself so I can perfectly empathise with a newbie who is "not clear as to what is entered". I don't for one minute suppose myself to be a better, more experienced, more talented, or more knowledgeable coder than you. But for you it seems this site provides nothing but a chance to show off and blind people with 'science' rather than actually respond to their very real confusion and uncertainty. The 'expert' who cannot remember what it's like not to have a clue is rarely the best teacher or counsellor.

Good old dunfiddlin, thinks he knows everything as usual. The OP has specified that they used the Data Source wizard, meaning that they have a typed DataSet. As a result, all the type names and method names have been generated specifically for the OP's data and we don't know what they are. That would explain why I used example names and specifically stated that the names the OP needed to use would be different. The code you have provided is for an untyped DataSet, so it ignores the features provided by the typed DataSet and therefore renders the creation of a typed DataSet pointless. It will work but it is not the code that should be used in this case.

If the OP had cared to look they would have seen that each typed DataTable has the NewRow method inherited from its DataTable base class and it also has a NewSomeSpecificRow method as well, where the SomeSpecific part depends on the table it was generated from. Where the NewRow method returns an untyped DataRow, the specific method returns a typed DataRow, thus providing you the ability to use the features specific to that type. For example, if I was to create a Person table with FirstName and LastName columns then using a typed DataSet for that would look like this:

Dim table = Me.ExampleDataSet.Person
Dim row = table.NewPersonRow()
 
row.FirstName = "Joe"
row.LastName = "Bloggs"
 
table.AddPersonRow(row)

Notice how that code EXACTLY follows the pattern that I provided earlier but the names are specific to the table in my database. That's why I was not able to provide specific names previously and why I specifically stated that the names would be different in each case. If anyone would care to simply type the dot followed by New or Add then Intellisense would display what matching methods were available. If you're going to use a typed DataSet then use a typed DataSet. If you're going to use dunfiddlin's code then you may as well not use a typed DataSet in the first place. Hopefully there's more than one person who can learn something from this.

[jmcilhinney]

The OP did indeed specify that he had originally used the Data Source Wizard but he also made it abundantly clear that he did not find that that was how he wanted to approach this problem.

The OP said no such thing. You should have just answered the question the OP asked:

Please clarify for these lines -->
Dim row = table.NewSomeRow()
table.AddSomeRow(row)

If you didn't know how to, which is apparently the case, then you shouldn't have answered at all. You've now diverted the OP from proper use of a typed DataSet. Congratulations on confusing the issue further.

holisticsam, let me explain what's going on here. There are two ways to use a DataSet. dunfiddlin obviously only knows one way and has directed you to do it that way. If you have used the Data Source wizard, as you say you have, then that is the wrong way to do it. With a standard untyped DataSet, you have to use what are often called "magic strings" to identify tables and columns, e.g.

Dim table = myUntypedDataSet.Tables("SomeTableName")
Dim row = table.Rows(0)
 
row("SomeColumnName") = someValue

Because you're using Strings to specify the table and column you get no Intellisense or type safety, i.e. there's nothing to stop you assigning a value in a column that is the wrong type, e.g. a String in an Integer column. When you use the Data Source wizard, the IDE generates new classes for you on the fly based specifically on your data. It generates a class inheriting DataSet and one class for each table inheriting DataTable. The typed DataSet class then has a property for each typed DataTable. There is also one class per DataTable generated inheriting DataRow to represent a row in that table. Each of those typed DataRow classes has a property for each column. That means that you get full Intellisense for each table and column as well as type safety for each column, e.g. if a column stores Integers then the corresponding property is type Integer, so you can't assign a String to a field in that column.

Now, when using an untyped DataSet you would call NewRow to create a new row and call AddRow to add that row to the table. If you're using a typed DataSet the each DataTable has additional methods that work with the appropriate typed DataRow for that table. Instead of NewRow you call NewSomeRow, where the highlighted part will be different for every single table, which is why I was unable to tell you the actual name of the method to call. Likewise you would call AddSomeRow instead of AddRow, where the highlighted part will be different for each table. dunfiddlin obviously doesn't know how to use typed DataSets, hence the poor advice. dunfiddlin is rather fond of criticising me for not explaining things properly when they have either already been explained or can't be explained fully with the information available.

Anyway, hopefully now you know why and how to use the types and members generated for you by the Data Source wizard. All you need to do is type the dot and Intellisense will show you what members are available and you will see the appropriate method that corresponds to NewSomeRow and AddSomeRow. They will have names like that that resemble the name of your table, as I showed in post #8.