Click here to Skip to main content
15,039,244 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
As I am very new to VB.Net I need the solution of this problem as I have a database called LoanData which have 6 columns namely (EmployeeID,L6501,L6502,L6503,L6504,L6505) and i have two text boxes namely txtEmpID and TxtAmount and one ComboBox namely CmbLoanHead.
I want that when i select one of the head via ComboBox then the amount will be added to the specific column my database LoanData which head is selected in ComboBox.

for Example when I selected the head (L6502) in ComboBox then the amount of txtAmount whill be inserted in column (L6502) of LoanData Database, not in all columns or any other column....

My programing language is VB.Net...

What I have tried:

VB
Private Sub BtnAddBPSWiseDeduct_Click(sender As Object, e As EventArgs) Handles BtnAddBPSWiseDeduct.Click
Cmd = New SqlCommand
        Cmd.Connection = Con
        Cmd.CommandText = "Insert into Loans_Data_Table(EmployeeID,L6501,L6502,L6503,L6504,L6505) values(@EmployeeID,@L6501,@L6502,@L6503,@L6504,@L6505)"
        Cmd.Parameters.AddWithValue("@EmployeeID", TxtEmpID.Text)
        Cmd.Parameters.AddWithValue("@L6501,@L6502,@L6503,@L6504,@L6505", TxtLoanAmount.Text = CmbLoanType.SelectedText)
        Con.Open()
        Cmd.ExecuteNonQuery()
        Con.Close()
    End Sub
Posted
Updated 1-Jun-21 5:40am
v2

1 solution

It's perhaps easiest to use if...else structure to decide which column will be used in the insert. You'll have only one bind in each case. Consider the following pseudo code example
VB
...
Cmd.CommandText = "Insert into Loans_Data_Table(EmployeeID, "
if (comboboxselection = "L6501") then
   Cmd.CommandText = Cmd.CommandText & "L6501";
else if (comboboxselection = "L6502") then
   Cmd.CommandText = Cmd.CommandText & "L6502";
else if...
end if

Cmd.CommandText = Cmd.CommandText & ") values (@EmployeeID, @loanAmount)"

Cmd.Parameters.AddWithValue("@EmployeeID", TxtEmpID.Text)
Cmd.Parameters.AddWithValue("@loanAmount", TxtLoanAmount.Text)
...
   
Comments
Ali Khan 3 1-Jun-21 12:41pm
   
Dear Wendelius when I put the above code into form but the data didn't save to the targeted database... I don't know why
Wendelius 1-Jun-21 13:04pm
   
That was just part of your original code. Did you still have the connection open and ExecuteNonQuery etc as you have in your original post?
Ali Khan 3 1-Jun-21 13:28pm
   
No I removed it. Now the code is like....

Private Sub BtnAddBPSWiseDeduct_Click(sender As Object, e As EventArgs) Handles BtnAddBPSWiseDeduct.Click
Cmd = New SqlCommand
Cmd.Connection = Con1
Cmd.CommandText = "Insert into Loans_Data_Table(EmployeeID, "
If (CmbLoanType.SelectedText = "L6501") Then
Cmd.CommandText = Cmd.CommandText & "L6501"
ElseIf (CmbLoanType.SelectedText = "L6502") Then
Cmd.CommandText = Cmd.CommandText & "L6502"
ElseIf (CmbLoanType.SelectedText = "L6503") Then
Cmd.CommandText = Cmd.CommandText & "L6503"
ElseIf (CmbLoanType.SelectedText = "L6504") Then
Cmd.CommandText = Cmd.CommandText & "L6504"
ElseIf (CmbLoanType.SelectedText = "L6505") Then
Cmd.CommandText = Cmd.CommandText & "L6505"
End If
Cmd.CommandText = Cmd.CommandText & "values (@EmployeeID, @LoanAmount)"
Cmd.Parameters.AddWithValue("@EmployeeID", TxtEmpID.Text)
Cmd.Parameters.AddWithValue("@LoanAmount", TxtLoanAmount.Text)
Con1.Close()
BindingDetails()
End Sub
Wendelius 1-Jun-21 14:00pm
   
That explains, after setting the bind variables, continue normally with the statement

...
Cmd.Parameters.AddWithValue("@LoanAmount", TxtLoanAmount.Text)
Con.Open()
Cmd.ExecuteNonQuery()
Con.Close()
...
Ali Khan 3 2-Jun-21 10:18am
   
Thanks So Much Dear the problem is solved with just little modification in if condition
If (CmbLoanType.Text = "L6501") Then
Cmd.CommandText = Cmd.CommandText & "L6501"
ElseIf (CmbLoanType.Text = "L6502") Then
Cmd.CommandText = Cmd.CommandText & "L6502"
ElseIf...
End If

Instead of (comboboxselection = "L6501")
Wendelius 2-Jun-21 10:51am
   
That's good. As said that was only pseudo code to demonstrate the idea.

Glad you got it solved
Ali Khan 3 3-Jun-21 9:41am
   
yes but without your cooperation i will be unable to solve it... at the end all credit goes to you my fried...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900