0

I want to put data in SQL table through vb.net in two columns which are Txn_Amount and Post_Amount

where textbox3 = Txn_Amount

Post Amount = Textbox4 - textbox3

but I want if textbox4 = "" than Post amount should be 0

This is my code:

Call Get_TxnID()
        
        Dim Txn_Amount As String = TextBox3.Text
          Dim Post_Amount As String = Val(TextBox4.Text) - Val(TextBox3.Text)
       

        Dim query As String = "Insert into Txn_Master values (@Txn_Amount,  @Post_Amount)"
        Using cmd As New SqlCommand(query, Connection)
            cmd.Parameters.AddWithValue("Txn_Amount", Txn_Amount)
            cmd.Parameters.AddWithValue("Post_Amount", Post_Amount)
            
            Connection.Open()
            cmd.ExecuteNonQuery()
            Connection.Close()

        End Using
        MsgBox("Transaction Success", MsgBoxStyle.Information)

It work well when i have value in both boxes For example :- textbox3.text = 25000 and textbox4.text = 50000 then Post_Amount is 25000

but if textbox3.text = 25000 and textbox4.text = "" then it shows -25000 in post_amount but i want if textbox4 = "" then post amount should be "" or "0"

I have tried

Dim Txn_Amount As String = TextBox3.Text
      If textbox4.text="" then
          Dim Post_Amount As String = ""
      Else
          Dim Post_Amount As String = Val(TextBox4.Text) - Val(TextBox3.Text)
      endif
       
        Dim query As String = "Insert into Txn_Master values (@Txn_Amount,  @Post_Amount)"
        Using cmd As New SqlCommand(query, Connection)

         
            cmd.Parameters.AddWithValue("Txn_Amount", Txn_Amount)
            cmd.Parameters.AddWithValue("Post_Amount", Post_Amount)
            
            Connection.Open()
            cmd.ExecuteNonQuery()
            Connection.Close()
        End Using
        MsgBox("Transaction Success", MsgBoxStyle.Information)

But it is now working, please help me with this

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Why not just declare and assign your value in separate statements? – Thom A Jan 10 '23 at 15:35
  • 1
    You have to include the "@" in the name of the parameter. Also, just so you know: [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/), [AddWithValue is evil!](https://chrisrickard.blogspot.com/2007/06/addwithvalue-is-evil.html), and [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). – Andrew Morton Jan 10 '23 at 18:33
  • 3
    Another thing: you should set [`Option Strict On`](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-strict-statement) for this project (and set it as the default for new projects) so that Visual Studio can point out problems like the line `Dim Post_Amount As String = Val(TextBox4.Text) - Val(TextBox3.Text)` - and even suggest possible corrections. – Andrew Morton Jan 10 '23 at 18:36
  • Is it required that the values are integers and not negative? – Andrew Morton Jan 11 '23 at 18:02
  • @AndrewMorton Dear sir, if we don't use ad with value it make trouble. Let me explain you with a little example. Suppose that i have two textbox 1 and 2 where textbox1 have value "500" and textbox2 have value "100" . now i want to add that value by using Textbox1.text+textbox2.text = textbox3.text. but in result it shows "500100" . it is not adding the value. But if i use val(textbox1.text)+val(textbox2.text)= textbox3.text thn it shows "600" so how can i avoid use of add with value? – Prince Arya Jan 27 '23 at 04:34

1 Answers1

0

If you initialise a variable for "Post_Amount" to zero, then you can check if the appropriate TextBox has an entry before setting its value, something like this:

Dim txnAmount As Integer = 0

If Not Integer.TryParse(tbTxnAmount.Text, txnAmount) Then
    ' Prompt user to enter an appropriate value in the TextBox.
    ' Exit Sub
End If

Dim postAmount As Integer = 0

'TODO Use sensible names for tbAmountA and tbAmountB.
If Not String.IsNullOrWhiteSpace(tbAmountB.Text) Then
    'TODO: Use sensible names for these variables.
    Dim a = 0
    Dim b = 0
    If Integer.TryParse(tbAmountA.Text, a) AndAlso Integer.TryParse(tbAmountB.Text, b) Then
        postAmount = b - a
    End If
End If

Using conn As New SqlConnection("your connection string")
    Dim sql = "INSERT INTO [Txn_Master] VALUES (@Txn_Amount, @Post_Amount)"
    Using cmd As New SqlCommand(sql, conn)
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Txn_Amount",
                                                  .SqlDbType = SqlDbType.Int,
                                                  .Value = txnAmount})

        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Post_Amount",
                                                  .SqlDbType = SqlDbType.Int,
                                                  .Value = postAmount})

        conn.Open()
        cmd.ExecuteNonQuery()
        cmd.Clone()

    End Using

End Using

I strongly recommend that you use meaningful names for the TextBoxes and variables. "tbAmountB" is your "TextBox4", but it still needs a better name.

Strictly speaking, it doesn't need the String.IsNullOrWhiteSpace test as such a string would fail the parsing, but it does leave the intent clear.

Also, to make your code easier for others to read, it is convention to use camelCase for variable names: Capitalization Conventions.

Andrew Morton
  • 24,203
  • 9
  • 60
  • 84