這是我的功能：
Function SQL_InsertUpdate(mySQLConnection As OleDbConnection, mySQLCommand As String, mySQLTable As String, mySQLTableColumns() As String, myParameters() As String)
Dim SQLCommand As OleDbCommand = New OleDbCommand(mySQLCommand, mySQLConnection)
Dim myStringConstruct = mySQLCommand & " " & mySQLTable & " ("
'==============
For Each item In mySQLTableColumns
myStringConstruct = myStringConstruct & item & ", "
Next
myStringConstruct = Strings.Left(myStringConstruct, Len(myStringConstruct) - 2)
myStringConstruct = myStringConstruct & ") VALUES ("
For i As Integer = 0 To mySQLTableColumns.Length - 1
myStringConstruct = myStringConstruct & "@" & mySQLTableColumns(i) & ", "
SQLCommand.Parameters.AddWithValue("@" & mySQLTableColumns(i), myParameters(i))
Next
myStringConstruct = Strings.Left(myStringConstruct, Len(myStringConstruct) - 2)
myStringConstruct = myStringConstruct & ")"
SQLCommand.ExecuteNonQuery()
End Function
這就是我呼叫函式的方式：
Dim myParameters() As String = ({myNewID.ToString, myUser.ToString, myDepartment.ToString, mySubsidiary.ToString, myTitle.ToString, myRecurrence.ToString, myImpact.ToString, myTimeSaved.ToString, myPriority.ToString, myStatus.ToString, myTechnology.ToString, myDeveloper.ToString, myCostSave.ToString, myDescription.ToString, myCommentary.ToString, myDateSubmitted.ToString, myDateModified.ToString, myInReviewDate.ToString, myManagerReviewDate.ToString, myDigitalReviewDate.ToString, myRejectedDate.ToString, myInProgressDate.ToString, myDevelopedDate.ToString, myImplementedDate.ToString})
Dim mySQLTableColumns() As String = ({"ID", "myUser", "myDepartment", "mySubsidiary", "myTitle", "myRecurrence", "myImpact", "myTimeSaved", "myPriority", "myStatus", "myTechnology", "myDeveloper", "myCostSave", "myDescription", "myCommentary", "myDateSubmitted", "myDateModified", "myInReviewDate", "myManagerReviewDate", "myDigitalReviewDate", "myRejectedDate", "myInProgressDate", "myDevelopedDate", "myImplementedDate"})
SQL_InsertUpdate(SQLConnection, "INSERT INTO", "SIMSBase", mySQLTableColumns, myParameters)
這是構造的命令字串輸出：
插入 SIMSBase（ID、myUser、myDepartment、mySubsidiary、myTitle、myRecurrence、myImpact、myTimeSaved、myPriority、myStatus、myTechnology、myDeveloper、myCostSave、myDescription、myCommentary、myDateSubmitted、myDateModified、myInReviewDate、myManagerReviewDate、myDigitalReviewDate、myRejectedDate、myInProgressDate、 myImplementedDate) 值（@ID、@myUser、@myDepartment、@mySubsidiary、@myTitle、@myRecurrence、@myImpact、@myTimeSaved、@myPriority、@myStatus、@myTechnology、@myDeveloper、@myCostSave、@myDescription、@myCommentary、@ myDateSubmitted、@myDateModified、@myInReviewDate、@myManagerReviewDate、@myDigitalReviewDate、@myRejectedDate、@myInProgressDate、@myDevelopedDate、@myImplementedDate)
這是我收到的錯誤：
-
$exception {"Syntax error in INSERT INTO statement."} System.Data.OleDb.OleDbException
現在我不知道我可能有什么語法錯誤，我在這里尋找另一個在這個堆疊問題中沒有問題的人：Insert data into SQL database in VB.NET，我的語法是相似的。
我不知道出了什么問題，如果 Access 資料庫列不是資料型別短/長文本，它會給出語法錯誤嗎？
引數已正確添加（檢查除錯）。
uj5u.com熱心網友回復：
實際上，我經常發現創建一個復雜的插入例程作業量太大。更糟糕的是，我經常不在乎或不想提供所有的列。
因此，.net 為您提供了所謂的命令生成器。
這在很大程度上意味著您可以以與 Access 中的 VBA 代碼的作業方式類似的方式撰寫大量代碼。
所以，假設我想添加一個新行——表可能有 50 列，但我并不在乎。
所以，我可以這樣寫代碼：
Dim rstHotels As DataTable
rstHotels = MyRst("SELECT * FROM tblHotels WHERE ID = 0")
' now add 3 new hotels
For i = 1 To 3
Dim OneRow = rstHotels.NewRow
OneRow("HotelName") = "Hotel #" & i
OneRow("City") = "City #" & i
OneRow("FirstName") = "Test First name #" & i
OneRow("Active") = True
rstHotels.Rows.Add(OneRow)
Next
' ok, added rows to rstHotels - now write back to database
MyRstUpDate(rstHotels, "tblHotels")
' or update 5 rows and do compplex processing to exising data.
Dim rstFun As DataTable = MyRst("SELECT * from tblHotels where City = 'Banff'")
For Each MyRow As DataRow In rstFun.Rows
MyRow("Active") = True
' more complex cpde here
Next
' now send data changes back to database
MyRstUpdate(rstFun, "tblHotels")
所以，請注意我們如何不必有一些復雜的插入陳述句，我們也不必撰寫一些回圈來對列的數量表示關注。因此，.net 資料操作已經為您構建了所有這些東西 - 很少甚至幾乎沒有理由讓您嘗試在這里重新發明輪子。
我有兩個方便的花花公子代碼例程？是：
Public Function MyRst(strSQL As String) As DataTable
Dim rstData As New DataTable
Using conn As New OleDbConnection(My.Settings.AccessDB)
Using cmdSQL As New OleDbCommand(strSQL, conn)
conn.Open()
rstData.Load(cmdSQL.ExecuteReader)
rstData.TableName = strSQL
End Using
End Using
Return rstData
End Function
Public Sub MyRstUpdate(rstData As DataTable, strTableName As String)
Using conn As New OleDbConnection(My.Settings.AccessDB)
Using cmdSQL As New OleDbCommand("SELECT * from " & strTableName, conn)
Dim da As New OleDbDataAdapter(cmdSQL)
Dim daUP As New OleDbCommandBuilder(da)
conn.Open()
da.Update(rstData)
End Using
End Using
End Sub
Now, I am really rather free to just code out my general routines.
So, you need to say load up a grid, or even a combo box? You can now do this:
ListBox1.DataSource = MyRst("SELECT ID, Salutation from tblGender ORDER BY Salutation")
So, for a simple insert, or even edit of some rows? No need to create some monster huge insert statement with a boatload of parameters. Just create a data table, and then use a simple data row to either add new rows, or even update existing ones.
The beauty of above is not only do you eliminate a boatload of parameters, but you also get parameter safe, and even type conversions. So, you can for example do this:
OneRow("InvoiceDate") = Date.Today
Thus a strong typed value of "money" or integer, or datetime can be used in code - and no messey format convertions are required in most cases.
This so called "data base" first can be really handy, and often for some operations this is a lot less setup time and learning curve then say using EF, or even the previous dataset designer (EF = "Entity framework", which works really much like the older data set designer system - but introduction of these object model systems can be a big system to chew on when you just starting out).
But, no, don't write your own looping code to write out and create all the columns for a update command. (or insert command - note how that ONE routine can handle both updates or inserts. And you can even use row.Delete and then call tht update routine - it will also work!!.
If you think about this, that really amounts to a lot of work, and built in systems exist for this propose - saves you having to re-invent the wheel.
uj5u.com熱心網友回復：
我很笨。
操作的順序是關閉的，函式應該是這樣的：
Function SQL_InsertUpdate(mySQLConnection As OleDbConnection, mySQLCommand As String, mySQLTable As String, mySQLTableColumns() As String, myParameters() As String)
Dim myStringConstruct = mySQLCommand & " " & mySQLTable & " ("
'==============
For Each item In mySQLTableColumns
myStringConstruct = myStringConstruct & item & ", "
Next
myStringConstruct = Strings.Left(myStringConstruct, Len(myStringConstruct) - 2)
myStringConstruct = myStringConstruct & ") VALUES ("
For i As Integer = 0 To mySQLTableColumns.Length - 1
myStringConstruct = myStringConstruct & "@" & mySQLTableColumns(i) & ", "
Next
myStringConstruct = Strings.Left(myStringConstruct, Len(myStringConstruct) - 2)
myStringConstruct = myStringConstruct & ")"
Dim SQLCommand As OleDbCommand = New OleDbCommand(myStringConstruct, mySQLConnection)
For i As Integer = 0 To mySQLTableColumns.Length - 1
SQLCommand.Parameters.AddWithValue("@" & mySQLTableColumns(i), myParameters(i))
Next
SQLCommand.ExecuteNonQuery()
End Function
基本上，我傳遞了一個不完整的命令。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/418515.html
標籤：
上一篇：基于特定SQL查詢創建索引