Programatically Adding A Column to a Dataset

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 7228 Views - Last Post: 29 May 2015 - 01:34 AM Rate Topic: -----

#1 BobV365   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 25-May 15

Programatically Adding A Column to a Dataset

Posted 25 May 2015 - 06:54 PM

I have a DataSet that is based on an existing Access Database. I know how to add a column to the table programmatically, but the problem is that the new column does not appear in the DataSet, even though it has been added to the bound table. The code I have is below. Any help in programmatically adding a column to the Dataset would be greatly appreciated.

Imports System.Data.OleDb

Public Class frmAddColumn

    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection

    'Add Button Click Event
    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AssetDatabase.mdb"
        myConnection.ConnectionString = connString
        myConnection.Open()
        Dim str As String = "ALTER TABLE tblAssetData ADD COLUMN [" & Me.TextBox1.Text & "] TEXT(50)"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        myConnection.Close()
    End Sub




This post has been edited by andrewsw: 26 May 2015 - 12:40 AM
Reason for edit:: Removed VB.NET from topic title, it is redundant


Is This A Good Question/Topic? 0
  • +

Replies To: Programatically Adding A Column to a Dataset

#2 andrewsw   User is offline

  • quantum multiprover
  • member icon

Reputation: 6775
  • View blog
  • Posts: 27,938
  • Joined: 12-December 12

Re: Programatically Adding A Column to a Dataset

Posted 25 May 2015 - 11:11 PM

Adding Columns to a DataTable

I haven't tried modifying and updating an already filled DataSet in this way, I suspect that you would need to refill the DataSet after modifying the table.
Was This Post Helpful? 0
  • +
  • -

#3 BobV365   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 25-May 15

Re: Programatically Adding A Column to a Dataset

Posted 26 May 2015 - 08:42 AM

Thanks Andrew for the reply to my question. I am hoping that someone will be able to help me with the Visual Basic statement that I will need in order to add a column in the DataSet. When I use the code in my original post ("ALTER TABLE tblAssetData ADD COLUMN [" & Me.TextBox1.Text & "] TEXT(50)""), a column is added to the existing Access Database that is located in the Debug folder of the project. However, it doesn't appear in the DataSet for the form, so it can't be filled with data. I need this capability so that as I add new features or fix bugs in the program, I can store the new data that may be required.
Was This Post Helpful? 0
  • +
  • -

#4 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 370
  • View blog
  • Posts: 1,586
  • Joined: 07-September 13

Re: Programatically Adding A Column to a Dataset

Posted 26 May 2015 - 08:51 AM

Andrew has already stated that you may have to refill the dataset after you modify the data structure to pick up the changes (which is seems like you do).

AKA after you have added the new column re-get the data for the dataset.

If that is not the answer you could potentially attempt to add the column directly into the datatable, i do now know how you would go about binding it for updating however.

Dataset.Tables("TableName").Columns.Add(New DataColumn("newColumnName"))

This post has been edited by maceysoftware: 26 May 2015 - 08:53 AM

Was This Post Helpful? 0
  • +
  • -

#5 BobV365   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 25-May 15

Re: Programatically Adding A Column to a Dataset

Posted 26 May 2015 - 09:19 AM

Thanks for the reply. I tried the visual basic statement you suggested in your reply, but it appears that Tables is not an object of the DataSet. There is a squiggly line under the word Tables in the statement: Dataset.Tables("TableName").Columns.Add(New DataColumn("newColumnName")). Of Course, I used the actual name of my table when I entered it in my project code. Is there something that I am missing?
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • quantum multiprover
  • member icon

Reputation: 6775
  • View blog
  • Posts: 27,938
  • Joined: 12-December 12

Re: Programatically Adding A Column to a Dataset

Posted 26 May 2015 - 09:31 AM

You would replace 'Dataset' with the name of your DataSet. You said you had a DataSet, what is it named?
Was This Post Helpful? 0
  • +
  • -

#7 BobV365   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 25-May 15

Re: Programatically Adding A Column to a Dataset

Posted 26 May 2015 - 10:27 AM

Hi Andrew, the name of my DataSet is AssetDatabaseDataSet. Below is the code that I am using; however, now I get a squiggly line under the name of my DataSet and the word Tables. When I hover my mouse over the squiggly line it says "Reference to non-shared member requires an object reference. I am not sure what that means, or what I need to do to correct it.

 [b][u]AssetDatabaseDataSet.Tables[/u][/b]("tblAssetData").Columns.Add(New DataColumn("SalesTax"))


Sorry, the code should read as follows:
AssetDatabaseDataSet.Tables("tblAssetData").Columns.Add(New DataColumn("SalesTax"))

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • quantum multiprover
  • member icon

Reputation: 6775
  • View blog
  • Posts: 27,938
  • Joined: 12-December 12

Re: Programatically Adding A Column to a Dataset

Posted 26 May 2015 - 11:37 AM

The reference AssetDatabaseDataSet isn't available in the current scope. You've declared it elsewhere, or as a member of an object, and it isn't directly available where you are attempting to run this code.

This post has been edited by andrewsw: 26 May 2015 - 11:38 AM

Was This Post Helpful? 0
  • +
  • -

#9 BobV365   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 25-May 15

Re: Programatically Adding A Column to a Dataset

Posted 26 May 2015 - 04:28 PM

Hi Andrew. I have still not been able to get the code right. I am getting an error that says "Object reference not set to an instance of an object." Below is my code. The error happens with the line: DS.Tables("tblAssetData").Columns.Add(New DataColumn("SalesTax123")).

I have spent several days trying to figure this out. I am stumped. There must be something wrong with the DataSetAdapter. Somithing must be Null, but I don't know how to correct it. Any idea as to how I can fix it?

Imports System.Data.OleDb

Public Class frmAddColumn

    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection


    'Form Load Event
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        frmAddEditAssets.TblAssetDataTableAdapter.Fill(frmAddEditAssets.AssetDatabaseDataSet.tblAssetData)
    End Sub

    'Add Button Click Event
    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AssetDatabase.mdb"
        myConnection.ConnectionString = connString
        myConnection.Open()
        Dim str As String = "ALTER TABLE tblAssetData ADD COLUMN [" & Me.TextBox1.Text & "] TEXT(50)"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        Try

            Dim DS As New AssetDatabaseDataSet
            Dim TblAssetDataTableAdapter As New frmAddEditAssets
            frmAddEditAssets.TblAssetDataTableAdapter.Fill(frmAddEditAssets.AssetDatabaseDataSet.tblAssetData)
            DS.Tables("tblAssetData").Columns.Add(New DataColumn("SalesTax"))

            cmd.ExecuteNonQuery()
            cmd.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        myConnection.Close()
    End Sub


Was This Post Helpful? 0
  • +
  • -

#10 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 370
  • View blog
  • Posts: 1,586
  • Joined: 07-September 13

Re: Programatically Adding A Column to a Dataset

Posted 27 May 2015 - 12:46 AM

You cant create a instance of the dataset like that. at least i dont think you can.

Anyhow your dataset is on a different form so you have to point it at that datasource.


frmAddEditAssets.AssetDatabaseDataSet.Tables("tblAssetData").Columns.Add(New DataColumn("SalesTax"))

Was This Post Helpful? 0
  • +
  • -

#11 BobV365   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 25-May 15

Re: Programatically Adding A Column to a Dataset

Posted 27 May 2015 - 03:14 PM


I need help in writing the proper code to add a new column to an existing DataSet and update the DataSet so that the new column can be later bound to a form. I have the code to programmatically add a new column to the database (and that works perfectly), but I am stuck on how to write the code to add the same column to the DataSet. I am either missing something, or just using the wrong code.

Below is the code for the Button Click Event where the name of the new column is acquired so it can be used in the code to add the column. The code that I am having trouble with starts at Line 19. I have one database named People.accdb that has one table named Addresses, and I want to programmatically add additional columns to the database and the dataSet. This is a sample database so that I can learn how to write the code to add a column to the DataSet and then use this knowledge on a bigger applicstion that I am writing. Thanks so much for taking the time to provide some guidance, and it is greatly appreciated. Obviously I am a beginner with VB.Net and have a lot to learn.


'Button Click Event - Add a column to People.accdb and to the PeopleDataSet
    Private Sub btnAddColumn_Click(sender As Object, e As EventArgs) Handles btnAddColumn.Click
        Dim connString As String
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim str As String = "ALTER TABLE Addresses ADD COLUMN [" & Me.txtColName.Text & "] TEXT(50)"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)

        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\People.accdb"
        myConnection.ConnectionString = connString
        myConnection.Open()

        Try

            'Execute the NonQuery to Add Column To People.accdb
            cmd.ExecuteNonQuery()
            cmd.Dispose()

            'Add Column To PeopleDataSet
            Dim DS As New DataSet
            DS = Me.PeopleDataSet
            DS.Tables("Addresses").Columns.Add(Me.txtColName.Text, GetType(String))

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        myConnection.Close()
    End Sub


Was This Post Helpful? 0
  • +
  • -

#12 andrewsw   User is offline

  • quantum multiprover
  • member icon

Reputation: 6775
  • View blog
  • Posts: 27,938
  • Joined: 12-December 12

Re: Programatically Adding A Column to a Dataset

Posted 27 May 2015 - 03:22 PM

Topics merged. Please do not start a new topic for the same question.

What is the problem with your code? What error message do you get (if any) and what line does it refer to? (It is unlikely to refer to line 19.)

            'Add Column To PeopleDataSet
            Dim DS As New DataSet
            DS = Me.PeopleDataSet

If you are attempting to modify an existing DataSet then there is no need to create a new DataSet.
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw   User is offline

  • quantum multiprover
  • member icon

Reputation: 6775
  • View blog
  • Posts: 27,938
  • Joined: 12-December 12

Re: Programatically Adding A Column to a Dataset

Posted 27 May 2015 - 03:43 PM

If Me.PeopleDataSet is a reference to the existing DataSet then
Me.PeopleDataSet.Tables("Addresses").Columns.Add(..)

should probably work.

There will still be problems in updating the data source from this DataSet, and I suggest that re-filling the DataSet will be the simplest approach.

Otherwise, you will need to re-build command objects to take into account the new column:

Generating Commands with CommandBuilders :MSDN

and, before this, probably a call to RefreshSchema on the CommandBuilder is required.

Anyway, if you continue with your approach, the above information should be useful.
Was This Post Helpful? 0
  • +
  • -

#14 BobV365   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 25-May 15

Re: Programatically Adding A Column to a Dataset

Posted 27 May 2015 - 03:44 PM

There is no error. After running the code, when I open the DataSet from the Solution Explorer, the Column is not getting added to the DataSet. And when I double-click on the Database from the Solution Explorer, the column is not there either. However, the column is in the database that is located in the DataSource mdb file that is located in the Debug\Bin folder (Data Source=|DataDirectory|\People.accdb). Is there a code statement that I am missing that will update the DataSet to accept the column that I am trying to add?
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw   User is offline

  • quantum multiprover
  • member icon

Reputation: 6775
  • View blog
  • Posts: 27,938
  • Joined: 12-December 12

Re: Programatically Adding A Column to a Dataset

Posted 27 May 2015 - 03:54 PM

See this link about the behaviour of the database file:

How to: Manage Local Data Files in Your Project :MSDN

In particular, the Copy to Output Directory property.

I assumed that you would be adding a column to an in-memory DataSet, not attempting to change its definition permanently. I cannot help you with that. I doubt that it is easy, but good luck!

This post has been edited by andrewsw: 27 May 2015 - 03:56 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2