VB.net/MS访问每月捐款系统帮助

Question

我正在为我的数据库管理主题做一个项目。我无法弄清楚如何将金额添加到以前添加的金额。目前，我只能更新金额。这是代码。如果我解释不好，我很抱歉。

我有2种形式。我的第一个表格允许我输入姓氏并将数据检索到我的列表视图。

我的第二种形式可以让我检索我在第一个表格中输入的数据，它将显示在带有“Last Name | Amount”选项卡的单独列表视图中。

我有两个文本框。一个用于姓氏设置为只读以禁用编辑，另一个用于我想输入的数量。

进入量之后，让我们说20，它将更新的列表视图和我的数据库为20

的问题是，当我输入相同的姓氏一新的总额，比方说30， 30将取代20，但它应该是50，因为20 + 30 = 50.

我了解逻辑，并且我尝试添加另一个文本框用于添加，但我根本不知道它的代码。

Imports System.Data.OleDb 

Public Class Form2 
Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Israel De Leon\Documents\testing.accdb;" 
Dim con As OleDbConnection = New OleDbConnection(conString) 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database2.accdb 
Dim cmd As OleDbCommand 
Dim adapter As OleDbDataAdapter 
Dim dt As DataTable = New DataTable() 


Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load 

    'SET LISTVIEW PROPERTIES 
    ListView1.View = View.Details 
    ListView1.FullRowSelect = True 

    'Construct Columns 
    ListView1.Columns.Add("Last Name", 100) 
    ListView1.Columns.Add("Amount", 100) 
End Sub 

Private Sub UpdateLV(lname As String) 
    'Updates last name and amount entered into the database 
    Dim sql As String = "UPDATE Table1 SET LastName='" + TextBox1.Text + "',Amount='" + TextBox2.Text + "' WHERE LastName='" + lname + "'" 
    cmd = New OleDbCommand(sql, con) 

    'OPEN CON, EXECUTE, UPDATE, CLOSE 
    Try 
     con.Open() 
     adapter = New OleDbDataAdapter(cmd) 

     adapter.UpdateCommand = con.CreateCommand() 
     adapter.UpdateCommand.CommandText = sql 

     If (adapter.UpdateCommand.ExecuteNonQuery() > 0) Then 
      MsgBox("Successfully Updated") 

     End If 

     con.Close() 

     Retrieve() 
     ClearBox() 
    Catch ex As Exception 
     MsgBox(ex.Message) 
     con.Close() 
    End Try 

End Sub 

Private Sub Retrieve() 
    ListView1.Items.Clear() 
    'SQL STM 
    Dim sql As String = "SELECT * FROM Table1 " 
    cmd = New OleDbCommand(sql, con) 

    'OPEN CON, RETRIEVE, FILL LISTVIEW 
    Try 
     con.Open() 
     adapter = New OleDbDataAdapter(cmd) 

     adapter.Fill(dt) 

     'LOOP THROUGH DT 
     For Each row In dt.Rows 
      Populate(row(0), row(1)) 'Index of database row 
     Next 

     'CLEAR DATATABLE 
     dt.Rows.Clear() 
     con.Close() 
    Catch ex As Exception 
     MsgBox(ex.Message) 
     con.Close() 
    End Try 
End Sub 
Private Sub Populate(lname As String, aamount As String) 
    'ROW ARRAY 
    Dim row As String() = New String() {lname, aamount} 

    Dim item As ListViewItem = New ListViewItem(row) 

    'ADD TO ROWS COLLECTION 
    ListView1.Items.Add(item) 
End Sub 

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 
    Retrieve() 
End Sub 
Private Sub ListView1_MouseClick(sender As Object, e As MouseEventArgs) Handles ListView1.MouseClick 
    Dim llname As String = ListView1.SelectedItems(0).SubItems(0).Text 
    Dim amounts As String = ListView1.SelectedItems(0).SubItems(1).Text 

    TextBox1.Text = llname 
    TextBox2.Text = amounts 
End Sub 

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click 
    Dim amounts As String = ListView1.SelectedItems(0).SubItems(0).Text 
    UpdateLV(amounts) 
End Sub 

Private Sub ClearBox() 
    TextBox1.Text = "" 
    TextBox2.Text = "" 
End Sub 

末级

Answer 1

数学运算不应使用字符串来完成。这是一个真正的基本原则，许多VB.NET程序员都没有足够的认识，这要归功于VB.NET项目设置中的Option Strict Off允许的宽恕。
如果您刚刚在VB.NET中启动一个新项目，请不要使用此设置，而是尽快将其切换为开。当您尝试使用字符串时，这会让您停下来，因为它们是数字，并强制您进行适当的转换并检查提供的值。

所以，你的代码更新改写

Private Sub UpdateLV(lname As String) 

    ' Get the amount as a number (decimal for currency is the best) 
    Dim addAmt As Decimal 
    if Not decimal.TryParse(textbox2.Text, addAmt) Then 
     MessageBox.Show("Insert a valid amount please") 
     return 
    End If 

    ' Sanity check 
    if addAmt <= 0 Then 
     MessageBox.Show("Amount should be > 0") 
     return 
    End If 

    'Updates last name and amount entered into the database 
    Dim sql As String = "UPDATE Table1 SET [email protected] 
           ,[email protected] 
           WHERE [email protected]" 
    cmd = New OleDbCommand(sql, con) 
    Try 
     con.Open() 

     ' Using an adapter here is wrong. You use directly the command 
     cmd.Parameters.Add("@name", OleDbType.VarWChar).Value = textBox1.Text 
     cmd.Parameters.Add("@amt", OleDbType.Decimal).Value = addAmt 
     cmd.Parameters.Add("@oldname", OleDbType.VarWChar).Value = lName 
     If (cmd.ExecuteNonQuery() > 0) Then 
      MsgBox("Successfully Updated") 
     End If 
     con.Close() 
     Retrieve() 
     ClearBox() 
    Catch ex As Exception 
     MsgBox(ex.Message) 
     con.Close() 
    End Try 
End Sub 

别的东西的量是不是在你的代码清晰。在这里更改姓氏的目的是什么？最后不要保留全局连接对象。相反，在需要时创建它，然后用Using语句将其销毁。它会更适合你的内存占用和数据库