如何在VB.net中使用多个结果

Question

我需要在3个文本框中显示在同一语句中进行的3个查询的结果

我在mysql中有两个名为personal_sueldos和personal_haberes的表。它们都有相同的列。我需要在两个查询中从两者中选择相同的信息，第三个查询是从haberes中减去descuentos
所以我必须在VB.NET表单的3个文本框中显示所有3个结果。

MySql中查询的代码：

SELECT SUM(importe) FROM personal_haberes WHERE persona_doc = 55880334 AND mes = 11 AND año = 2019;
SELECT SUM(importe) FROM personal_descuentos WHERE persona_doc = 55880334 AND mes = 11 AND año = 2019;
SELECT (SELECT SUM(importe) FROM personal_haberes WHERE persona_doc = 
55880334 AND mes = 11 AND año = 2019) -
(SELECT SUM(importe) FROM personal_descuentos WHERE persona_doc = 
55880334 AND mes = 11 AND año = 2019) as importeTotal

VB.NET中的代码：

cnn.Open()
Dim Query As String
Query = "SELECT SUM(importe) as ImporteHaberes FROM personal_haberes WHERE persona_doc  = " & Module1.document & " AND mes = " & HR_DatePicker.Value.Month & " AND año = " & HR_DatePicker.Value.Month & ";" & _
"SELECT SUM(importe) as ImporteDescuentos FROM personal_descuentos WHERE persona_doc = " & Module1.document & " AND mes = " & HR_DatePicker.Value.Month & " AND año = " & HR_DatePicker.Value.Month & ";" & _
"SELECT (SELECT SUM(importe) FROM personal_haberes WHERE persona_doc    = " & Module1.document & " AND mes = " & HR_DatePicker.Value.Month & " AND año = " & HR_DatePicker.Value.Month & ";) -" & _
"(SELECT SUM(importe) FROM personal_descuentos WHERE persona_doc = " & Module1.document & " AND mes = " & HR_DatePicker.Value.Month & " AND año = " & HR_DatePicker.Value.Month & ";) AS ImporteTotal"
Dim cmd As New MySqlCommand(Query, cnn)
rdr = cmd.ExecuteReader
While rdr.Read
Me.HR_txtHaberes.Text = rdr.Item("ImporteHaberes").ToString
Me.HR_txtDescuentos.Text = rdr.Item("ImporteDescuentos").ToString
Me.HR_txtTotal.Text = rdr.Item("ImporteTotal").ToString
End While
cnn.Close()

我必须在一个文本框中显示每个查询的结果。

现在VB给我错误"在结果中找不到指定的列">

Answer 1

这里有很多需要清理。使用字符串串联来构建这样的查询是never可以的，即使在学习代码时也是如此。这也不是管理连接对象的正确方法。

一个更好的模式(有固定的代码，包括下一个结果问题(看起来是这样的：

'Multi-line string literals are now supported
' Note: there is NO CONCATENATION here. This is a constant.
' I also consolidated the SQL to avoid worrying about separate result sets.
Dim Query As String = "
SELECT SUM(importe) as Importe
FROM personal_haberes
WHERE persona_doc = @document AND mes = @mes AND año = @ano
UNION ALL
SELECT SUM(importe)
FROM personal_descuentos
WHERE persona_doc = @document AND mes = @mes AND año = @ano;"
Using cnn As New MySqlConnection("connection string here"), _
cmd As New MySqlCommand(query, cnn)
'Guessing at types/lengths here.
' Use the ACTUAL columns types and lengths from your database
cmd.Parameters.Add("@document", MySqlDbType.VarChar, 50).Value = Module1.document
cmd.Parameters.Add("@mes", MySqlDbType.Int32).Value = HR_DatePicker.Value.Month
cmd.Parameters.Add("@ano", MySqlDbType.Int32).Value = HR_DatePicker.Value.Year 'This was your error
'Wait as long as possible to call .Open()
cnn.Open()
Using rdr As MySqlDataReader = cmd.ExecuteReader()
rdr.Read()
Dim Haberes As Integer = CInt(rdr("Importe"))
rdr.Read()
Dim Descuentos As Integer = CInt(rdr("Importe"))
Me.HR_txtHaberes.Text = Haberes.ToString()
Me.HR_txtDescuentos.Text = Descuentos.ToString()
Me.HR_txtTotal.Text = (Haberes - Descuentos).ToString() 'No need to make the database do the subtraction work.
End Using
End Using ' This will take care of closing the connection.. even if an exception is thrown!
'The old code might have leaked open connections whenever there was an exception.

Answer 2

让我们开始将查询更改为参数化查询。你的老师告诉你，在你目前的情况下，这并不重要，但我强烈反对。编写参数化查询是一项需要尽快学习的技能。作为安全考虑的一部分，看看在没有所有这些串联的情况下，它现在有多清楚。

Dim Query As String = "
SELECT SUM(importe) as ImporteHaberes 
FROM personal_haberes 
WHERE persona_doc  = @doc AND mes = @mes AND año = @año;
SELECT SUM(importe) as ImporteDescuentos 
FROM personal_descuentos 
WHERE persona_doc = @doc AND mes = @mes AND año = @año;
SELECT  (SELECT SUM(importe) 
FROM personal_haberes 
WHERE persona_doc = @doc AND mes = @mes AND año = @año) -
(SELECT SUM(importe) 
FROM personal_descuentos 
WHERE persona_doc = @doc AND mes = @mes AND año = @año) AS ImporteTotal"

现在创建命令并添加三个参数
再次查看它如何更简洁、更清晰，并易于发现Value的问题。Month也为最终参数重复。

Dim cmd As New MySqlCommand(Query, cnn)
cmd.Parameters.Add("@doc", MySqlDbType.VarChar).Value = Module1.document
cmd.Parameters.Add("@mes", MySqlDbType.Int32).Value = HR_DatePicker.Value.Month
' ???? This should be HR_DatePicker.Value.Year ???? '
cmd.Parameters.Add("@año", MySqlDbType.Int32).Value = HR_DatePicker.Value.Month
rdr = cmd.ExecuteReader

现在是NextResult的用法。只需启动第一个循环即可获得第一个结果，然后传递到第二个结果，依此类推

While rdr.Read()
Me.HR_txtHaberes.Text = rdr.Item("ImporteHaberes").ToString
End While    
rdr.NextResult() 
While rdr.Read()
Me.HR_txtDescuentos.Text = rdr.Item("ImporteDescuentos").ToString
End While
rdr.NextResult() 
While rdr.Read()
Me.HR_txtTotal.Text = rdr.Item("ImporteTotal").ToString
End While

考虑到每个查询的返回总是一条记录，那么您也可以用一个简单的替换While循环

If rdr.Read() Then
Me.HR_txtHaberes.Text = rdr.Item("ImporteHaberes").ToString
End If
rdr.NextResult() 
.....