Is it possible to not save all the grids into the database?

I have a datagrid for displaying all data from a database. Is it possible to add a column to the datagrid for the user, but when I update the database using the fill mothod from the OLE adapter, this new column will not be updated into the database?

You don't update a database by calling the Fill method of a DataAdapter. Fill retrieves data from the database. Update saves changes to the database. When you call Update it will execute the DeleteCommand and/or InsertCommand and/or UpdateCommand, depending on what changes the DataTable contains. Whatever SQL code is assigned to the CommandText of those Commands is what gets executed. If you don't want a particular column affected then don't include the column in that SQL code.

Sorry, long day for me today, I mean the update command, da.Update(ds).

Can you give me an example of this SQL command before calling da.Update?

When/How do I add this new column to the datagrid? I tried the following but it don't work. Thanks:

----------------------------

VB Code:

' Define database related controls
    Dim cn As OleDbConnection
 
    ' Create a datatable
    Dim WithEvents myTable As DataTable = New DataTable("MTCGD")
 
    ' Create a dataset to hold the data for the database
    Dim ds As New DataSet
 
    Private Sub FrmRejects_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        ' Establish connection to the database
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MTC.mdb;")
        'provider to be used when working with access database
 
        Dim sql As String = "select * from MTCGD"
        Dim da As New OleDbDataAdapter(sql, cn)
 
        Try
            cn.Open()
            ' Fill dataset
            da.Fill(ds, "myTable")
 
            cn.Close()
 
           'Create new column
            Dim led As New DataColumn("NEWcol", GetType(String)) <---
            led.Unique = False                                                   <----
            myTable.Columns.Add(led)                                         <----
 
            DataGrid1.DataSource = ds.Tables("myTable")
 
        Catch ex As Exception
            Debug.Write(ex.Message)
        End Try
    End Sub

You're using two completely separate DataTable objects. You're adding the column to one of them but that's not the one that gets populated or bound to the grid. This line:

VB Code:

Dim WithEvents myTable As DataTable = New DataTable("MTCGD")

creates a DataTable. Let's call it Table1. At no point do you add Table1 to your DataSet. This line:

VB Code:

da.Fill(ds, "myTable")

creates a second DataTable, this time in the DataSet, and populates it. Let's call that Table2. Table1 now has no data and is not in the DataSet while Table2 does contain data and is in the DataSet. This line:

VB Code:

myTable.Columns.Add(led)

adds an extra column to Table1, which before that had no columns at all. This line:

VB Code:

DataGrid1.DataSource = ds.Tables("myTable")

binds Table2 to the grid.

There is absolutely no point using a DataSet in this case anyway as it serves no purpose. It's like creating an array to hold one String.

VB Code:

Dim myConnection As New OleDbConnection("connection string here")
Dim myDataAdapter As New OleDbDataAdapter("SELECT ID, Column1, Column2 FROM MyTable", myConnection)
Dim myDataTable As New DataTable
 
myDataAdapter.UpdateCommand = New OleDbCommand("UPDATE MyTable SET Column1 = @Column1, Column2 = @Column2 WHERE ID = @ID", myConnection)
 
With myDataAdapter.Parameters
    .Add("@Column1", OleDbType.VarChar, 50, "Column1")
    .Add("@Column2", OleDbType.VarChar, 50, "Column2")
    .Add("@ID", OleDbType.Integer, 4, "ID")
End With
 
myDataAdapter.Fill(myDataTable)
myDataTable.Columns.Add("Column3", GetType(String))
 
'...
 
myDataAdapter.Update(myDataTable)

Note that the SQL UPDATE statement makes no mention of Column3 so it will be ignored. The IDE can do much of this for you. Even if you don't use the Data Adapter Configuration Wizard you can still create your data access objects in the designer in a much more visual way. It's not a bad idea to steer away from the wizard until you have a fairly good understanding of what's going on. Otherwise, if the wizard doesn't produce exactly the results you need you won't know how to fix it.

I am so thankful you are taking the time to help me in such a great details.

I am very confused with when to use dataset, dataview and datatable. I will go read more about this topic, then try out your code.

Thank you again. You have been really helpful.

By the way, I just tried your code, "With myDataAdapter.Parameters", Parmaeters is not a member of OleDbDataAdapter. Is it new for 2005?

Sorry. Hazards of writing code directly in the forum rather than pasting from the IDE. That should have been:

VB Code:

With myDataAdapter.UpdateCommand.Parameters

Quote:

---

Originally Posted by newtovbnet

I am so thankful you are taking the time to help me in such a great details.

I am very confused with when to use dataset, dataview and datatable. I will go read more about this topic, then try out your code.

Thank you again. You have been really helpful.

---

The DataSet, DataTable and DataView correspond to the entire database, a table within a database and a view within a database respectively.

A DataSet is a container for DataTables and DataRelations. There are some instances where you must use a Dataset. For instance, you can create a DataSet in the designer but you cannot create stand-alone DataTables. Also, if you want to maintain relationships between DataTables then you need to use a DataSet with DataRelations. Even if you are not creating it in the designer or creating relationships between tables, it is still a good idea to use a DataSet if you have a medium to large number of tables.

A DataTable is a container for DataColumns and DataRows. The DataColumns define the properties of the data, while the DataRows contain the data itself. If you are only using a single table then it is usually advisable to just create a stand-alone DataTable with no DataSet. I wouldn't use a DataSet for three or fewer tables unless I needed relationships or to create them in the designer.

A DataView doesn't contain any data itself. It provides a way of customising the way you view the data in a DataTable. You can sort the data via a DataView or you can filter it. This has no affect on the DataTable whatsoever, but when you access that data via the DataView it will obey the sorting and filtering rules you have defined.

Every DataTable is associated with a DataView by default via its DefaultView property. When you bind a DataTable to a control such as a grid, it is actually the contents of the DefaultView that gets displayed. When you click a column header in a DataGrid and sort the data you are actually setting the Sort property of the bound table's DefaultView.

Thanks.
Just for me to understand, if I have to use a dataset and a datatable in the future, how can I change my code to use 1 table and make it work?

I like to keep "Dim WithEvents myTable As DataTable" because I need to check for the field/column changes.

Thanks.

Then you need to add the Datatable to the DataSet:

VB Code:

Dim dt As New DataTable
Dim ds As New DataSet
 
ds.Tables.Add(dt)

Also, I strongly recommend not using "Dim" for class level variables. While it won't make any actual difference to how the code compiles and executes, it is considered good practice to explicitly state the access level of every class-level variable. If you use Dim then the variable becomes Private implicitly. If you want it to be Private then you should specify explicitly:

VB Code:

Private obj As Object

Do the add table before the fill?

Dim dt As New DataTable
Dim ds As New DataSet

ds.Tables.Add(myTable)
da.Fill(ds)

Sorry, I meant
Dim dt As New DataTable
Dim ds As New DataSet

ds.Tables.Add(dt)
da.Fill(ds)

It doesn't matter when you add the DataTable to the DataSet, as long as you fill that table:

VB Code:

Dim myDataTable As New DataTable
 
'Fill by specifying the DataTable.
myDataAdapter.Fill(myDataTable)
 
Dim myDataSet As New DataSet
 
myDataSet.Tables.Add(myDataTable)

VB Code:

Dim myDataTable As New DataTable
Dim myDataSet As New DataSet
 
myDataSet.Tables.Add(myDataTable)
 
'Fill by specifying the DataTable.
myDataAdapter.Fill(myDataTable)

VB Code:

Dim myDataTable As New DataTable([U]"MyTable"[/U])
Dim myDataSet As New DataSet
 
myDataSet.Tables.Add(myDataTable)
 
'Fill by specifying the name of the table within the DataSet
myDataAdapter.Fill(myDataSet, [U]"MyTable"[/U])

Can I assume that the first 2 case, I can use if I am dealing with just 1 table? And the third case, it's best to be use if I am dealing with more than 1 table?