Can't Update Column Captions to Database

T

TedN

Freshman
Joined
Sep 25, 2006
Messages
35
I have an empty table in a database that has default column headings (Field1, Field2.....).

I would like to change these headings programmatically by downloading the table to a dataset, changing the column captions in the dataset, then updating the database table from the dataset. (It's part of a course excercise).

My code seems to work OK - the database table fills the dataset and the column captions are changed in the dataset. However, after the database table is updated no changes occur to the column headings in the database.
I get no errors either.

Appreciate it if someone could point me in the right direction.

Following is my code. The database is Assign9.mdb (Access) and the database table is "StudentGrade".

Code: 
Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click


        'Connect to database Assign9.mdb.
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Assign9.mdb"
        con.Open()


        'Fill dataset with records from database table StudentGrades.
        Dim ds As New DataSet("Student")
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        sql = "SELECT * FROM StudentGrade"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Student")



        'Create array to hold label text.
        Dim dsItem(7) As String

        dsItem(1) = Label1.Text
        dsItem(2) = Label2.Text
        dsItem(3) = Label3.Text
        dsItem(4) = Label4.Text
        dsItem(5) = Label5.Text
        dsItem(6) = Label6.Text


        'Change column captions.
        Dim dc(6) As DataColumn
        Dim i As Integer

        For i = 1 To 6
             ds.Tables(0).Columns(i).Caption = dsItem(i)
        Next


        'Update database.
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        da.Update(ds, "Student")

        con.Close()

    End Sub
End Class
 
Hi,
DataAdapters can send Data Manipulation Language (DML) statements like Select, Insert, Update and Delete to the DB but not Data Definition Language (DDL) statements like Alter Table and Alter Column. DataAdapters would also not work with procedures like sp_rename(). You can however send the alter column statement using the ExecuteNonQuery("Alter column code goes here") method of the OleDbCommand class.

Hope this helps!
 
Last edited:
IUnknown said:
Hi,
DataAdapters can send Data Manipulation Language (DML) statements like Select, Insert, Update and Delete to the DB but not Data Definition Language (DDL) statements like Alter Table and Alter Column. DataAdapters would also not work with procedures like sp_rename(). You can however send the alter column statement using the ExecuteNonQuery("Alter column code goes here") method of the OleDbCommand class.

Hope this helps!
Click to expand...

Thanks for the info. I'll check this out. In the meantime I got around the problem by programmatically adding a new table to the database including column headings.

Thanks,
TedN
 
My previous post is not entirely correct. sp_rename() is an SQL Server stored proc, not Access. Renaming an Access table column using DDL statements (Alter Table) involves several steps, i.e, adding a new column with the new name, copying the data from the column to be renamed to the new column and finally deleting the column to be renamed. I've used ADOX in the past with VB6 to rename Access columns but have not done so in .Net.
 
You should be able to use DAO to change the column name, e.g., db.TableDefs("Table1").Fields("XXX").Name = "YYY".
 
Back
Top