在VB.NET Winforms中使用SQL Server选择查询时出错

Question

此查询不返回任何值，也不会导致任何错误：

Dim cmdAs1 As String
Dim daAs1 As SqlClient.SqlDataAdapter
Dim dsAs1 As DataSet
Dim dtAs1 As DataTable

cmdAs1 = "SELECT * FROM [SN_Male_Quest_2018].[dbo].[Section_3] WHERE sub_village_id= '" & sub_village_id & "' and household_id= '" & household_id & "' and hrid= '" & male & "' and hrid= '" & female & "' and hrid= '" & adolscent & "' and hrid= '" & respid & "'"
daAs1 = New SqlClient.SqlDataAdapter(cmdAs1, cnn)
dtAs1 = New DataTable()
dsAs1 = New DataSet()
daAs1.Fill(dsAs1, "Section_3")
dtAs1 = dsAs1.Tables("Section_3")
lbloperator.Text = dtAs1.Rows.Count

If (dtAs1.Rows.Count.Equals(0)) Then

Answer 1

将您的connection对象保持在使用它的方法的本地。这使您可以控制它是否被关闭和释放。即使出现错误，Using...End Using也会阻止您执行此操作。在本例中，连接和命令都包含在使用中。

正如@devio在评论中提到的，你不能让一个字段等于几个不同的值。我只能假设你的意思是“或”

始终使用参数。我不得不猜测数据类型和字段大小。请检查您的数据库并相应地调整代码。

Private Sub FillDataTable(sub_village_id As String, household_id As String, male As String, female As String, adolscent As String, respid As String)
    Dim dtAs1 As New DataTable
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand("SELECT * FROM [Section_3] WHERE sub_village_id= @villageID and household_id= @householdID and (hrid= @hrIDMale Or hrid= @hrIDFemale Or hrid= @hrIDAdolescent Or hrid= @hrIDResID);", cn)
        With cmd.Parameters
            .Add("@villageID", SqlDbType.VarChar, 100).Value = sub_village_id
            .Add("@householdID", SqlDbType.VarChar, 100).Value = household_id
            .Add("@hrIDMale", SqlDbType.VarChar, 100).Value = male
            .Add("@hrIDFemale", SqlDbType.VarChar, 100).Value = female
            .Add("@hrIDAdolescent", SqlDbType.VarChar, 100).Value = adolscent
            .Add("@hrIDResID", SqlDbType.VarChar, 100).Value = respid
        End With
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    lbloperator.Text = dt.Rows.Count.ToString
    If dt.Rows.Count = 0 Then
        'Your code here
    End If
End Sub