Transfer data from combobox to columns with condition

rajdh75

Member
Joined
Mar 30, 2020
Messages
6
Programming Experience
Beginner
Hello,
I have a form from which I transfer data to access table.
The form has date, Invoice Id, Customer name and one combo box named as CmbTypeofSale which has two options 1. Cash 2. Credit
The access data table has date, Invoice Id, Customer name, Cash and Credit columns.
I want if Cash option is selected the amount should be saved in Cash column and Credit column should be blank,
and if Credit option is selected the amount should be saved in Cash column and Cash column should be blank.
VB.NET:
con.Close()
Private Sub BtnSaveInvoice_Click(sender As Object, e As EventArgs) Handles BtnSaveInvoice.Click
        Try
            con.Open()

            cmd = New OleDbCommand("Insert into TblSearchInvoice ([DateofSale],[InvoiceID],[CustomerName],[Cash],[Credit])values('" + TxtDate.Text + "','" + TxtInvoiceID.Text + "','" + CmbCustomerName.Text + "','" + CmbTypeofSale.Text + "','" + CmbTypeofSale.Text + "') ", con)
            cmd.Parameters.Add(New OleDbParameter("DateofSale", CType(TxtDate.Text, Date)))
            cmd.Parameters.Add(New OleDbParameter("InvoiceID", CType(TxtInvoiceID.Text, Integer)))
            cmd.Parameters.Add(New OleDbParameter("CustomerName", CType(CmbCustomerName.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Cash", CType(CmbTypeofSale.Text, Integer)))
            cmd.Parameters.Add(New OleDbParameter("Credit", CType(CmbTypeofSale.Text, Integer)))

            cmd.ExecuteNonQuery()
            cmd.Dispose()

        Catch ex As Exception
            MsgBox("Error", ex.Message)
        Finally
            con.Close()
        End Try
        con.Close()
End Sub
How can I achieve this ?

Thanks in advance.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,251
Location
Sydney, Australia
Programming Experience
10+
Firstly, it's good that you tried to use parameters but I'm afraid you failed. You are still concatenating values into your SQL so the parameters you add will be ignored. The idea is that you use placeholders in the SQL rather than values and then the values are obtained from the parameters by the database.

As for the question, you want to do one thing in one case and another thing in another case. How do you usually make that sort of choice? Why should it be any different here? You can build that into the SQL code and let the database work it out (Access supports IIf in SQL) or you can do it in your VB code and use different SQL in each case. Consider the options and give it a go.
 
Top Bottom