如何将从一个表格获得的值插入VB中的另一个表中获得的值

Question

我的代码是错误的，但这是我可以想到的解决方案。我正在尝试将指定用户的所有点从1个表添加，然后将该值插入另一个表中。任何帮助都将不胜感激。

这是我的代码：

Imports MySql.Data.MySqlClient
Public Class Leaderboard
Dim MysqlConn As MySqlConnection
Dim COMMAND As MySqlCommand
Private Sub submitBtn_Click(sender As Object, e As EventArgs) Handles    submitBtn.Click
    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString =
    "server=localhost;userid=root;password=root99;database=database"
    Dim READER As MySqlDataReader
    Try
        MysqlConn.Open()
        Dim Query As String
        Query = "  select (select SUM(Points) as sumpoints from database.progress where candidate_number_fk = '" & textbox_can.Text & "') (insert into database.leaderboard (username,points) values ('" & textbox_user.Text & "','  sumpoints '  )"
        COMMAND = New MySqlCommand(Query, MysqlConn)
        READER = Command.ExecuteReader
        MessageBox.Show("You have successfully submitted your score")
        MysqlConn.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        MysqlConn.Dispose()
    End Try
    My.Forms.Menu1.Show()
    Me.Hide()
End Sub
End Class

Answer 1

您不需要双重查询，因为您可以使用INSERT INTO .. SELECT FROM构造并在单个查询中完成

等

insert into database.leaderboard (username,points)
select 'Niam', SUM(Points) as sumpoints 
from database.progress 
where candidate_number_fk = '" & textbox_can.Text & "';

请注意，您使用字符串串联直接在查询中使用用户输入，这可能会导致SQL Indection 。使用参数化查询而不是使用MySqlParameter类。