mysql - 如何在mysql和visual basic中设置变量组？

Question

如何正确设置@dategiven1？

set @dategiven1 = ('1','2','3');
set @dategive4 = 2020;
set @activity = "Aerial Cable Installation";

SELECT id, billingreference, opac, projectorder, projectlocation, headedby, dategiven 
FROM project_info 
WHERE MONTH(dategiven) IN (@dategiven1) 
  AND YEAR(dategiven) = @dategiven4 
  AND id IN ( SELECT project_id 
              FROM manpower_project 
              WHERE activity = @activity )

我怎样才能应用这个视觉基础？

谢谢，关注我无法解释的问题:(

标签： mysql.netvb.net

Answer 1

MySql 中的 Month 方法似乎返回一个 Integer，因此，您需要用括号括起来的逗号分隔数字。参数传递单个值，因此此处不适合使用参数。您需要构建一个字符串并将其连接到您的 select 语句中。

Private Function InClause(Months As Integer(), Year As Integer, Activity As String) As DataTable
    'StringBuilder is mutable so it save creating a new string and tossing the old one on each iteration
    'Start the string out with the opening parenthesis
    Dim BuildInClause As New StringBuilder("(")
    'loop through each element in the array and add it to the string builder along with a comma
    For Each m In Months
        BuildInClause.Append(m & ",")
    Next
    'convert the StringBuilder to a string, Trim off the final comma and add the closing parenthesis.
    Dim str = BuildInClause.ToString.Trim(","c) & ")"
    'Check what the string looks like
    Debug.Print(str)
    Dim dt As New DataTable
    'The Using...End Using block ensures that you connection and command are closed and disposed even if there is an error
    Using cn As New MySqlConnection("Your connection String"),
            cmd As New MySqlCommand("Select id, billingreference, opac, projectorder, projectlocation, headedby, dategiven 
                From project_info
                Where Month(dategiven) In " & str &
                " And YEAR(dategiven) = @dategiven4 
                And id IN ( SELECT project_id From manpower_project 
                  Where activity = @activity ))", cn)
        cmd.Parameters.Add("@dategiven4", MySqlDbType.Int32).Value = Year
        cmd.Parameters.Add("@activity", MySqlDbType.String).Value = Activity
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function

用法：

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim intArray = {1, 2, 3}
    Dim dt = InClause(intArray, 2020, "Aerial Cable Installation")
    DataGridView1.DataSource = dt
End Sub