I am creating an inventory management system in VB.Net, where the basic function is the process incoming invoices. I would like to insert data into my remote MySQL database but only if the textbox is considered enabled. I have no issue inserting data into the database when I want it to insert all fields. But I would like to have checkboxes enable certain products and allow the employees to enter only specific items. The check boxes do enable and disable the text fields as required but when inserting data into the database it will enter null values for all product types and I don't want it doing that as it will mess up the invoicing system. Currently I tried an if then statement but the issue I ran into was that it wanted the not enabled textboxes to be defined. Code for what I tried is:

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

CodePudding user response：

You can't just insert a column without a row. So if you will insert one column, you must insert all columns. If you don't want to add a value, then the column could be NULL or empty string, depending on the columns in the database. Also use Using and take the database work off the UI with 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

If I understand your current design,

Table_Name

An incomplete invoice has empty string or NULL when one column isn't enabled

This is what this answer does.

If your database design is flexible, perhaps a better design would be

Invoice

InvoiceRec

InvoiceItem

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'

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.