我正在嘗試將資料插入資料庫中，并將 MS Access 中的自動編號作為主鍵。我收到一條錯誤訊息，提示“查詢值和目標欄位的數量不一樣。MS Access 中的資料型別是自動編號（我沒有在 INSERT 陳述句中包含它）、字串 (@OrderNo)、字串 (@Product) 、數字 (@Qty) 和日期 (@TDate)。圖片如下：
這是我的代碼：
For Each row As DataGridViewRow In DataGridView1.Rows
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Daily Inventory.accdb;"
Using conn As New OleDbConnection(connString)
Using cmd As New OleDbCommand("Insert into Table1 Values(@OrderNo, @Product, @Qty, @TDate)", conn)
cmd.Parameters.AddWithValue("@OrderNo", TxtOrder.Text.ToString)
cmd.Parameters.AddWithValue("@Product", row.Cells("Product").Value)
cmd.Parameters.AddWithValue("@Qty", row.Cells("Qty").Value)
cmd.Parameters.AddWithValue("@TDate", Date.Now.ToString("MM/dd/yyyy"))
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using
Next
uj5u.com熱心網友回復：
您需要將 sql 更改為“插入 Table1 (OrderNo,Product,Qty,TDate) Values(@OrderNo, @Product, @Qty, @TDate)”。
以下代碼適用于我。
DataGridView1.AllowUserToAddRows = False
For Each row As DataGridViewRow In DataGridView1.Rows
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=...;"
Using conn As New OleDbConnection(connString)
Using cmd As New OleDbCommand("Insert into Table1 (OrderNo,Product,Qty,TDate) Values(@OrderNo, @Product, @Qty, @TDate)", conn)
cmd.Parameters.AddWithValue("@OrderNo", TxtOrder.Text.ToString)
cmd.Parameters.AddWithValue("@Product", row.Cells("Product").Value)
cmd.Parameters.AddWithValue("@Qty", row.Cells("Qty").Value)
cmd.Parameters.AddWithValue("@TDate", Date.Now.ToString("MM/dd/yyyy"))
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using
Next
uj5u.com熱心網友回復：
好吧，我想我們在這里處理的是雞和蛋的問題？
我的意思是，網格是如何創建的？
您如何以及在哪里設定網格中的列？
讓我們將資料網格視圖拖放到表單中。放入一個按鈕。
我們有這個代碼來加載網格：
Private Sub HotelGridEdit_Load(sender As Object, e As EventArgs) Handles Me.Load
LoadGrid()
End Sub
Sub LoadGrid()
Using conn As New OleDbConnection(My.Settings.AccessDB)
Dim strSQL As String =
"SELECT ID, FirstName, LastName, City, HotelName, Description, Active FROM tblHotelsA
ORDER BY HotelName"
Using cmdSQL As New OleDbCommand(strSQL, conn)
conn.Open()
Dim rstData As New DataTable
rstData.Load(cmdSQL.ExecuteReader)
DataGridView1.DataSource = rstData
End Using
End Using
End Sub
所以，現在我們有了這個：
現在，在上面，我可以游標四處游蕩——進行任何我想要的編輯。
我也可以在底部的空白行中輸入以添加新行。
我可以單擊左側的記錄選擇器，然后按洗掉鍵。
現在，要保存我的編輯，保存我的添加，并保存我的洗掉？
我在頂部有一個按鈕，此代碼可用于執行所有更新、所有添加和所有洗掉。
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
' save all edits, and new rows, and deletes
Using conn As New OleDbConnection(My.Settings.AccessDB)
Dim strSQL As String =
"SELECT ID, FirstName, LastName, City, HotelName, Description, Active FROM tblHotelsA"
Using cmdSQL As New OleDbCommand(strSQL, conn)
conn.Open()
Dim da As New OleDbDataAdapter(cmdSQL)
Dim daC As New OleDbCommandBuilder(da)
da.Update(DataGridView1.DataSource)
End Using
End Using
End Sub
所以，代碼不多。
In fact, often using a data table is a lot less work, since you don't have to mess with all those parameters anyway - even if your data grid was somehow not feed from the database. But, if you feeding the "grid" from the database, then really, you can just add rows - and then write some code to send the changes back to the database with the above code. And if you want to start out with a blank grid - not show previous rows, then just add to your sql like this:
SELECT * from tblHotels WHERE ID = 0 Order by Hotelname
Use above to create a data table without any rows but STILL bind that to the grid, and thus once again, to add, or delete rows, you just edit and add them, and again the SAME code I had above will work to save edits, deletes and additions.
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/447462.html
標籤：数据库 VB.net 视觉工作室 毫秒访问 数据网格视图