How do I subtract monetary value in an existing row in VB, SQL database

Question

0.00/5 (No votes)

See more:

How do I subtract a value in textbox to an existing row in VB?

What I have tried:

VB.NET

Dim cmd As New SqlCommand("UPDATE TblMemberships SET wallet = @wallet WHERE Username= @username", con)
Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = FormLogin.TxtBoxUsername.Text
cmd.Parameters.Add("@wallet", SqlDbType.VarChar).Value = FormProfileMain.LabelWallet.Text

con.Open()

If cmd.ExecuteNonQuery() = 1 Then
   If Val(TextBoxTotal.Text) > FormProfileMain.LabelWallet.Text Then
      MsgBox("Insufficient wallet, please load!", MsgBoxStyle.Information, "Wallet")
   Else
      MsgBox("Successfully ordered!", MsgBoxStyle.ApplicationModal, "Wallet")
   End If
   con.Close()
End If

Posted 1-May-20 23:22pm

Member 14814400

Updated 2-May-20 3:01am

phil.o

v3

Add a Solution

Comments

phil.o 2-May-20 7:34am

Are you storing the wallet value as a string in your database?

Member 14814400 2-May-20 7:41am

Yes, and I also tried changing its data type in sql as money and as smallmoney

phil.o 2-May-20 8:05am

You should never store as string a value for which there exists an appropriate type.

Member 14814400 2-May-20 8:10am

I also tried using the sqlcommand insert and use table.rows to deduct but I don't know how to update it afterwards.

2 solutions

Solution 1

First of all, please give your monetary column the datatype it deserves: money.
Then:

VB.NET

Dim cmd As New SqlCommand("UPDATE TblMemberships SET wallet = @wallet WHERE Username= @username", con)
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = FormLogin.TxtBoxUsername.Text
Dim money As Decimal
If (Not Decimal.TryParse(FormProfileMain.LabelWallet.Text, money)) Then
   money = 0
End If
cmd.Parameters.Add("@wallet", SqlDbType.Money).Value = money

con.Open()

If cmd.ExecuteNonQuery() = 1 Then
   Dim total As Decimal
   If (Not Decimal.TryParse(TextBoxTotal.Text, total)) Then
      total = 0
   End If
   Dim wallet As Decimal
   If (Not Decimal.TryParse(FormProfileMain.LabelWallet.Text, wallet)) Then
      wallet = 0
   End If
   If total > wallet Then
      MsgBox("Insufficient wallet, please load!", MsgBoxStyle.Information, "Wallet")
   Else
      MsgBox("Successfully ordered!", MsgBoxStyle.ApplicationModal, "Wallet")
   End If
   con.Close()
End If

Posted 2-May-20 2:24am

phil.o

Comments

Member 14814400 2-May-20 8:40am

I tried and it does not work, the labelwallet.text returns to 0 after i press the button. And it still said Insufficient wallet, please load! even if wallet > total

phil.o 2-May-20 8:44am

Put a breakpoint at the beginning of the button click handler, and start a debug session (F5 in Visual Studio). Then carefully execute line-by-line, and watch for the values of variables along the way. This will allow you to pinpoint the issue. We cannot do that for you, since it needs the exact code as well as the actual data.

Member 14814400 2-May-20 8:45am

my code that add data into wallet is varchar, i changed it to money and now it gives me this error Failed to convert parameter value from a String to a Decimal.

phil.o 2-May-20 8:54am

You have to change the datatype of the column in the database, too.

Member 14814400 2-May-20 9:04am

i did, i tried everything i know and it is till not working.

Add a Solution

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

MadMyche · Accepted Answer · 2020-05-02T03:02:00

Solution 2

Well this is going to be a mixed answer.

The SQL you have is correct for doing an UPDATE, and is not susceptible to SQL Injection.

What is most likely incorrect is data-types; subtraction infers that the data should be numerical in nature- but you are passing in @wallet as text.
UPDATE: based on comments that have come in... you need to correct the data types in your database

What is also incorrect is the sequence of events; you are doing the UPDATE before you check to see if there is sufficient funds.

What we don't know is what the values are or represent, so there may be other issues.
It also looks like you may not be calculating values correctly.

Here is some pseudo-VB on what I would expect to see for something like this. You may need to change data types for things such as currency amount.

VB

Dim FundsNeeded as Int = Val(TextBoxTotal.Text)
Dim FundsAvailable as Int = 0
Dim NewWalletValue as Int = 0

Dim UserName = FormProfileMain.LabelWallet.Text


Dim qryWalletRetrieve as string  = "SELECT wallet FROM TblMemberships WHERE Username = @username"
Dim qryWalletUpdate as string  = "UPDATE TblMemberships SET wallet = @wallet WHERE Username= @username"

Dim cmd As New SqlCommand(qryWalletRetrieve, con)
cmd.Parameters.AddWithValue("@username", UserName)

con.Open()

FundsAvailable = (Int)cmd.ExecuteScalar()

If (FundsAvailable < FundsNeeded) Then 
	MsgBox("Insufficient wallet, please load!", MsgBoxStyle.Information, "Wallet")
Else
	NewWalletValue = FundsAvailable - FundsNeeded

	cmd.CommandText = qryWalletUpdate
	cmd.Parameters.AddWithValue("@wallet", NewWalletValue)
	Dim RA as Integer = cmd.ExecuteNonQuery()                       ' RA = Rows Affected
	If RA = 1 Then
		MsgBox("Successfully ordered!", MsgBoxStyle.ApplicationModal, "Wallet")
	Else 
		' Oops... why are we here? 
	End
End

cmd.Dispose()
con.Close()

Posted 2-May-20 3:02am

MadMyche

Updated 2-May-20 3:31am

v3

Comments

Member 14814400 2-May-20 9:14am

there are errors in this lines
FundsAvailable = (Int)cmd.ExecuteScalar()

and

Int RA = cmd.ExecuteNonQuery() ' RA = Rows Affected
If (RA = 1) Then

sorry, Our prof are unable to taught this due to the pandemic. Thank you!

MadMyche 2-May-20 9:25am

Sorry, VB is not my native tongue; and I did this in Notepad...

I prefaced this as pseudo-code as I expected there to be some syntactical errors which you should be able to figure out.
To fix lines with issues... simply rewrite them as needed. Most likely you are using an IDE such as Visual Studio which has intellisense to help you with the formatting and correct cases

FundsAvailable = Convert.ToInt32(cmd.ExecuteScalar())
If RA = 1

Member 14814400 2-May-20 9:26am

i can't seem to find the issue in the said error above, thank you though!!

MadMyche 2-May-20 9:27am

FundsAvailable = Convert.ToInt32(cmd.ExecuteScalar())
If RA = 1

Member 14814400 2-May-20 9:30am

The remaining errors are with this:

Int RA = cmd.ExecuteNonQuery() ' RA = Rows Affected
If RA = 1 Then

the error description is:
Severity Code Description Project File Line Suppression State
Error BC30451 'RA' is not declared. It may be inaccessible due to its protection level.

MadMyche 2-May-20 11:06am

Did you figure it out yet?

Member 14814400 3-May-20 1:16am

unfortunately, not yet. :(

Member 14814400 3-May-20 3:06am

THANK YOU!! I HAVE IT REVISED AND IT IS NOW WORKING,

THIS IS NOW THE CODE THAT I AM RUNNING

Dim FundsNeeded As Integer = Val(TextBoxTotal.Text)
Dim FundsAvailable As Integer = Val(LabelWallet.Text)
Dim NewWalletValue As Integer = 0

Dim UserName = FormLogin.TxtBoxUsername.Text
'I change it so it can get the username in the database'

Dim qryWalletRetrieve As String = "SELECT wallet FROM TblMemberships WHERE Username = @username"
Dim qryWalletUpdate As String = "UPDATE TblMemberships SET wallet = @wallet WHERE Username= @username"

Dim cmd As New SqlCommand(qryWalletRetrieve, con)
cmd.Parameters.AddWithValue("@username", UserName)

con.Open()

FundsAvailable = Convert.ToInt32(cmd.ExecuteScalar())

If (FundsAvailable < FundsNeeded) Then
MsgBox("Insufficient wallet, please load!", MsgBoxStyle.Information, "Wallet")
Else
NewWalletValue = FundsAvailable - FundsNeeded

cmd.CommandText = qryWalletUpdate
cmd.Parameters.AddWithValue("@wallet", NewWalletValue)
Dim RA As Integer = cmd.ExecuteNonQuery() ' RA = Rows Affected
If RA = 1 Then
MsgBox("Successfully ordered!", MsgBoxStyle.ApplicationModal, "Wallet")
Else
' Oops... why are we here?
End If
End If

cmd.Dispose()
con.Close()

MadMyche 3-May-20 7:26am

Glad you got it fixed, please consider marking this question as resolved and rating the solutions that helped you

Member 14814400 3-May-20 8:44am

Sure!!

MadMyche 2-May-20 9:30am

Updated the answer-- I had a line of C# in there

Member 14814400 3-May-20 1:15am

where can i find it?

How do I subtract monetary value in an existing row in VB, SQL database

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How do I subtract monetary value in an existing row in VB, SQL database

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us