我正在 VB.Net 中創建一個庫存管理系統，其中基本功能是處理傳入發票。我想將資料插入到我的遠程 MySQL 資料庫中，但前提是文本框被認為是啟用的。當我希望它插入所有欄位時，將資料插入資料庫沒有問題。但我想讓復選框啟用某些產品并允許員工僅輸入特定專案。復選框確實根據需要啟用和禁用文本欄位，但是當將資料插入資料庫時??，它將為所有產品型別輸入空值，我不希望它這樣做，因為它會弄亂發票系統。目前我嘗試了一個 if then 陳述句，但我遇到的問題是它想要定義未啟用的文本框。我嘗試的代碼是：
Public Sub btnEnter_Click(sender As Object, e As EventArgs) Handles btnEnter.Click
Dim mysqlconn as new MySqlConnection ("ServerConnection")
mysqlconn.Open()
dim mysqlCmd as new MysqlCommand
mysqlcmd.Connection = MysqlConn
mysqlcmd.CommandText = "Insert into Table_Name (Column1,Column2,Column3) Values (@rec1,@Rec2,@Rec3)"
If txtTextbox1.Enabled = True then
mysqlcmd.Parameters.AddWithValue("@Rec1",Column1.text)
End If
If txtTextBox2.Enabled = True then
mysqlcmd.Parameters.AddWithValue(@Rec2,Column2.text)
End IF
IF txtTextBox3.Enabled = True then
mysqlcmd.Parameters.AddWithValue(@Rec3,Column3.text)
End If
uj5u.com熱心網友回復：
您不能只插入沒有行的列。因此，如果要插入一列，則必須插入所有列。如果您不想添加值，則該列可能是 NULL 或空字串，具體取決于資料庫中的列。還可以使用Using并將資料庫從 UI 中移除Async/Await
Public Async Sub btnEnter_Click(sender As Object, e As EventArgs) Handles btnEnter.Click
' if your columns take NULL to mean no value
Await AddParams(
If(txtTextbox1.Enabled, Column1.Text, Nothing),
If(txtTextbox2.Enabled, Column2.Text, Nothing),
If(txtTextbox3.Enabled, Column3.Text, Nothing))
' else, if an empty string means no value
Await AddParams(
If(txtTextbox1.Enabled, Column1.Text, ""),
If(txtTextbox2.Enabled, Column2.Text, ""),
If(txtTextbox3.Enabled, Column3.Text, ""))
End Sub
Private Function AddParams(param1 As String, param2 As String, param3 As String) As Task
Return Task.Run(
Sub()
Using mysqlconn As New MySqlConnection("ServerConnection")
mysqlconn.Open()
Using mysqlCmd As New MySqlCommand("Insert into Table_Name (Column1,Column2,Column3) Values (@rec1,@Rec2,@Rec3)", mysqlconn)
mysqlCmd.Parameters.AddWithValue("@Rec1", param1)
mysqlCmd.Parameters.AddWithValue("@Rec2", param2)
mysqlCmd.Parameters.AddWithValue("@Rec3", param3)
End Using
End Using
End Sub)
End Function
如果我了解你目前的設計，
表名
| ID | 第 1 列 | 第 2 列 | 第 3 列 |
|---|---|---|---|
| 1 | 美國廣播公司 | 定義 | 吉 |
| 2 | jkl | mno | pqr |
當未啟用一列時，不完整的發票具有空字串或 NULL
| ID | 第 1 列 | 第 2 列 | 第 3 列 |
|---|---|---|---|
| 1 | 美國廣播公司 | 空值 | 吉 |
| 2 | 空值 | mno | pqr |
這就是這個答案的作用。
如果您的資料庫設計是靈活的，那么更好的設計可能是
發票
| ID | 姓名 |
|---|---|
| 1 | 發票 1 |
| 2 | 發票 2 |
InvoiceRec
| ID | Name |
|---|---|
| 1 | Column1 |
| 2 | Column2 |
| 3 | Column3 |
InvoiceItem
| ID | InvoiceID | InvoiceRecID | Value |
|---|---|---|---|
| 1 | 1 | 1 | abc |
| 2 | 1 | 3 | ghi |
| 3 | 2 | 2 | mno |
| 4 | 2 | 3 | pqr |
Now you aren't storing any null when an item is not enabled. The SQL to select would then be
SELECT
i.Name InvoiceName
, ir.Name InvoiceRecName
, ii.Value Value
FROM InvoiceItem ii
INNER JOIN Invoice i ON i.ID = ii.InvoiceID
INNER JOIN InvoiceRec ir ON i.ID = ir.InvoiceRecID
WHERE i.Name = 'Invoice 1'
| InvoiceName | InvoiceRecName | Value |
|---|---|---|
| Invoice 1 | Column1 | abc |
| Invoice 1 | Column3 | ghi |
This would totally invalidate the code I wrote (except keep the Async and Using) and you'd need to query multiple tables before making multiple inserts, but the design would be normalized and database storage size would be reduced.
Further, you can build your UI from the metadata tables [Invoice] and [InvoiceRec] so if wanted to add another InvoiceRec to your business, you can add in SQL, and the UI will not need to be modified.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/448309.html