VB.NET Access DB INSERT不插入数据？ [英] VB.NET Access DB INSERT Not Inserting Data?

问题描述

我正在尝试在Access中的表中插入一行。该表名为Site，它有两列：SiteId（AutoNumber）和SiteName（Text）。为了测试目的，我从Excel工作表中获取了一个值，并尝试使用以下代码将该值插入到SiteName列中。

  Private Sub Button1_Click（ByVal sender As System.Object，ByVal e As System.EventArgs）Handles Button1.Click 
 Dim App As New Excel.Application 
 Dim WB As Excel.Workbook = App.Workbooks .Open（C：\MyFile.xlsx）
 Dim WS As Excel.Worksheet = DirectCast（WB.Worksheets（2），Excel.Worksheet）
 App.Visible = False 
 
 Dim Range As Excel.Range 
 Range = WS.Range（A8）
 Dim cnn As New OleDbConnection（My.Settings.cnn）
 Dim cmd As New OleDbCommand INSERT INTO Site（SiteName）VALUES（@SiteName），cnn）
 cmd.Parameters.Add（@ SiteName，OleDbType.Char）.Value = Range.Value.ToString 
 
尝试
使用cmd.Connection 
 cmd.Connection.Open（）
 cmd.ExecuteNonQuery（）
结束使用
 Catch ex As Excepti在
 MessageBox.Show（ex.Message）
结束尝试
结束子
  

即使我删除了cmd.Parameters行，只是把一个像Value（'Test'）这样的直接值，它不会被插入。执行命令时，代码中没有错误。为什么值不行？我已经验证了从Excel工作表返回的数据，工作正常。我无法让它插入到Access中的表中。连接工作正常，因为如果我使用此插入...

  Dim cmd As New OleDbCommand（INSERT INTO Site VALUES （@SiteName），cnn）
  

它会告诉我，查询值的数量不匹配

解决方案

值插入（至少大部分时间）;）你有手动检查mdb（在输出文件夹中）的内容？

您已将mdb文件添加到项目中？转到文件的属性窗口，并将复制到输出目录设置为NEVER。

当您添加文件时，正常设置为如果较新或永远复制。这意味着每当您在调试器中运行应用程序时，项目目录中的mbd文件将被复制到调试/发行文件夹，并且OVERWRITES已经在其中的所有文件（以及存储了记录的位置）。

I'm trying to insert a row into a table I have in Access. The table is named Site and it has two columns, SiteId (AutoNumber) and SiteName (Text). For testing purposes, I grabbed a value from an Excel sheet and attempted to insert the value into the SiteName column with the following code...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim App As New Excel.Application
    Dim WB As Excel.Workbook = App.Workbooks.Open("C:\MyFile.xlsx")
    Dim WS As Excel.Worksheet = DirectCast(WB.Worksheets(2), Excel.Worksheet)
    App.Visible = False

    Dim Range As Excel.Range
    Range = WS.Range("A8")
    Dim cnn As New OleDbConnection(My.Settings.cnn)
    Dim cmd As New OleDbCommand("INSERT INTO Site(SiteName) VALUES(@SiteName)", cnn)
    cmd.Parameters.Add("@SiteName", OleDbType.Char).Value = Range.Value.ToString

    Try
        Using cmd.Connection
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Even if I remove the cmd.Parameters line and just put a straight value like Values('Test') it doesn't make an insert. I get no errors in the code as I perform the command. Why is the value not working? I have verified the data returning from the Excel sheet and that works fine. I just can't get it to insert into the table in Access. The connection works fine as well because if I use this Insert...

Dim cmd As New OleDbCommand("INSERT INTO Site VALUES(@SiteName)", cnn)

it will tell me that the number of query values does not match the field count or something of the sort.

解决方案

The value IS inserted (at least most of the times) ;) Have you ever checked the content of the mdb (in the output folder!) manually?

You have added the mdb file to your project? Go to the properties window of the file and set "Copy to output directory" to "NEVER".

NORMALLY when you add the file, it is set to "copy if newer" or "copy always". That means whenever you run your app in the debugger the mbd file from the project directory is copied to the debug/release folder and OVERWRITES any file that is already inside (and where you have stored your records).

这篇关于VB.NET Access DB INSERT不插入数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！