如何在不删除现有数据的情况下将列添加到现有Access数据库表？ [英] How do I add a column to an existing Access database table without deleting the existing data?

问题描述

我正在使用Visual Studio 2013并在VB.Net中编程。我的项目中有VB.Net代码，可以在现有的Access数据库表（Data Source表和DataSet）中添加一列。请参阅下面的代码。



执行代码后，我转到DataSet Designer并添加列，然后使用查询生成器创建新查询（Fill，GetData，Update，Insert） ，和删除）。但是，执行此操作后，将删除现有Access数据库中的原始数据。我需要在添加列之前保留Access数据库中的数据。



如果对此问题的代码有任何帮助，我将非常感激。在此先感谢。

I am using Visual Studio 2013 and programming in VB.Net. I have the VB.Net code in my project to add a column to the existing Access database tables (the Data Source table and the DataSet.) Please see the code below.

After I execute the code, I go to the DataSet Designer and Add the Column and then use the Query Builder to create new queries (Fill, GetData, Update, Insert, and Delete). However, after doing this, the original data in the existing Access database is deleted. I need to retain the data that was in the Access database before the column is added.

I would be greatly appreciative for any help with the code in the matter. Thanks in advance.

Imports System.Data.OleDb
Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DB1DataSet.FixedAssets' table. You can move, or remove it, as needed.
        Me.FixedAssetsTableAdapter.Fill(Me.DB1DataSet.FixedAssets)

    End Sub

    Private Sub FixedAssetsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles FixedAssetsBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.FixedAssetsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DB1DataSet)
    End Sub

    'Add Column to Database
    Private Sub btnAddColumn_Click(sender As Object, e As EventArgs) Handles btnAddColumn.Click
        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|DB1.mdb"
        Dim TextBoxName As String
        TextBoxName = Me.UpdateTextBox.Text
        MsgBox(TextBoxName)

        'UPDATE DB1.MDB IN BIN FOLDER 
        'Dim SqlString As String = "ALTER TABLE FixedAssets ADD COLUMN " + UpdateTextBox.Text.Trim() + " Text(250)"
        Dim SqlString As String = "ALTER TABLE FixedAssets ADD COLUMN " & UpdateTextBox.Text.Trim() & " Double"
        MsgBox(SqlString)
        Using conn As New OleDbConnection(ConnString)
            Using cmd As New OleDbCommand(SqlString, conn)
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        End Using
        Me.Validate()

        'UPDATE DB1.MDB IN DeprCalcProj2 FOLDER (DATA SOURCE)
        Dim ConnString2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Bob\Documents\Visual Studio 2013\Projects\DeprCalcProj2\DeprCalcProj2\DB1.mdb"
        Dim SqlString2 As String = "ALTER TABLE FixedAssets ADD COLUMN " & UpdateTextBox.Text.Trim() & " Double"
        Using conn As New OleDbConnection(ConnString2)
            Using cmd As New OleDbCommand(SqlString2, conn)
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        End Using
        Me.Validate()

    End Sub

End Class

推荐答案

您没有提供上述代码的错误信息。所以，我只能猜测...... MS Access数据库引擎不知道 Double 数据类型 [ ^ ]。您应该设置正确的数据类型，例如 NUMERIC 或 MONEY



您的代码很危险，因为 SQL Injection [ ^ 。我不建议通过代码修改表格！想象一下，当用户进入 UpdateTextBox 文本时会发生什么： DELETE FixedAssets ......

使用参数化查询代替！



如需了解更多信息，请参阅：

ALTER TABLE（MS Access） [ ^ ]

SQL注入以及如何避免它[ ^ ]

You did not provide information what's wrong with above code. So, i can only guess that... MS Access database engine does not know Double data type[^]. You should set proper data type, such as NUMERIC or MONEY

Your code is dangerous, because is open for SQL Injection[^]. I do no recommend to modify tables via code! Imagine, what happen when user enter into UpdateTextBox text like this: "DELETE FixedAssets"...
Use parametrized queries instead!

For further information, please see:
ALTER TABLE (MS Access)[^]
SQL Injection and how to avoid it[^]

这篇关于如何在不删除现有数据的情况下将列添加到现有Access数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！