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
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
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,
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
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
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.
Answered By – djv
Answer Checked By – Robin (BugsFixing Admin)