VB.NET：MS Access插入查询未正确插入。 [英] VB.NET: MS Access Insert Query is not inserting correctly.

问题描述

似乎我在这一小段编码中纠正了每一个问题，我最终找到了另一个问题。我有一个程序，当用户单击保存时，会尝试将列表视图中的数据存储在MS Access表中。我已经超过了这个问题，现在它只是弄乱了数据。



我的连接字符串：

It seems with every problem I correct in this tiny bit of coding, I end up finding another problem. I have a program that, when the user clicks Save, attempts to store the data from a listview in a MS Access table. I've made it past the point where I get errors from this, and now it just messes up the data.

My Connection String:

Public strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
System.Environment.CurrentDirectory & "\OmegaTestDatabase.mdb"

用于插入数据的代码：

Code used to insert the data:

Dim DbConnection As New OleDbConnection(strConnectionString)
Dim SqlQry As New OleDbCommand("INSERT INTO Inventory VALUES (@ItemName, @ItemQuality, @SellPrice)", DbConnection)
Dim DeleteCmd As New OleDbCommand("DELETE * FROM Inventory", DbConnection)

'deletes the current data to be replaced by new data
DbConnection.Open()

DeleteCmd.CommandType = CommandType.Text

DeleteCmd.Connection = DbConnection

DeleteCmd.ExecuteNonQuery()

DeleteCmd.Dispose()

DbConnection.Close()

'inserts a new record into the database for every item in ItemBagList
Dim item As ListViewItem
Dim ItemName As String
Dim ItemQuality As String
Dim SellPrice As Integer


For Each item In ItemBagList.Items
    DbConnection.Open()

    ItemName = item.SubItems(0).Text
    ItemQuality = item.SubItems(1).Text
    SellPrice = item.SubItems(2).Text

    SqlQry.Parameters.AddWithValue("@ItemName", ItemName)
    SqlQry.Parameters.AddWithValue("@ItemQuality", ItemQuality)
    SqlQry.Parameters.AddWithValue("@SellPrice", SellPrice)

    SqlQry.ExecuteNonQuery()

    DbConnection.Close()
Next

当我点击保存时，它正确输入列表视图中的第一个项目，并且它确实添加了正确数量的记录，但是第一个之后的每个记录都是副本第一个。这就像For Each循环的重复无法重写变量。它知道有更多的项目，但它并不打算重新收集数据。



有人可以给我一个解决方法或弄清楚我的代码有什么问题吗？

When I click save, it correctly inputs whatever the first item is in the listview, and it does add the correct amount of records, but every record after the first is a copy of the first. It's like the reiteration of the For Each loop fails to rewrite the variables. It knows there are more items, but it doesn't bother to recollect the data.

Can anybody give me a work around or figure out what's wrong with my code?

推荐答案

这段代码有点不对。



首先，你不应该是在删除命令后关闭连接。



接下来，您不应该打开和关闭循环内的连接。打开连接，执行所有命令，关闭连接。



最后，你在循环中使用Parameters.AddWithValue。您不断向命令添加新的参数对象！在第一遍中，您有3个参数。在循环的下一次迭代中，你有6，在下一次你有9，...



在循环外创建参数对象然后设置值循环中的参数。



每个newb都在Internet上看到AddWithValue并认为这是添加参数的唯一方法。实际上，这是添加参数时遇到麻烦的最快捷最简单的方法，因为它隐藏了在向命令添加参数时实际需要注意的大量工作，例如为参数使用正确的数据库数据类型。您使用的AddWithValue方法根据您传入的值的数据类型对使用哪些数据库数据类型做出某些假设。

There'a s ton wrong with this code.

First, you shouldn't be closing the connection after your Delete command.

Next, you should not be opening and closing the connection inside your loop. Open the connect, execute all of your commands, close the connection.

Lastly, you're using Parameters.AddWithValue inside the loop. You're constantly adding new parameter objects to your command! On the first pass, you have 3 parameters. On the next iteration of the loop, you have 6, on the next you have 9, ...

Create your parameter objects outside the loop then set the values of the parameters inside the loop.

Every newb sees AddWithValue on the Internet and thinks it's the only way to add parameters. It's actually the quickest and easiest way to get yourself in trouble when adding parameters because it hides a lot of the work you actually need to pay attention to when adding parameters to commands, such as using the correct database data types for your parameters. The AddWithValue methods you used make certain assumptions about which database data types to use based on the data type of the values you pass in.

' OldDbType and length must match your database table column definition
Dim ItemNameParam As New OleDbParameter("@ItemName", OldDbType.VarChar, 50)
Dim ItemQualityParam As New OldDbParameter(...)
...
SqlQry.Parameters.Add(ItemNameParam)
SqlQry.Parameters.Add(ItemQualityParam)
...

For Each item In ItemBagList.Items
    ItemNameParam.Value = item.SubItems(0).Text
    ItemQualityParam.Value = item.SubItems(1).Text
    ...
    SqlQry.ExecuteNonQuery()
Next

您的整体设计会产生更多问题，但我会将此限制在眼前的问题上。

There are more problems stemming from your overall design, but I'll limit this to the immediate concerns.

这篇关于VB.NET：MS Access插入查询未正确插入。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！