VB.NET无法在数据视图中更新SQL数据

Question

我尝试了很多方法，但问题仍然无法解决。

我的目的是在单击“搜索”按钮后，datagridview将在特定条件下显示来自SQL数据库的数据。

当我第一次单击“搜索”按钮时，它可以工作。但是，我第二次单击search按钮时，datagridview显示为空白(我更改了一些条件，比如不同的行号等)。

请帮帮我!非常感谢!

以下是我的VB代码：

Private Sub search_btn_Click(sender As Object, e As EventArgs) Handles search_btn.Click


    Dim connectionString As String = "Data Source=xxx.xx.xx.xxx;Initial Catalog=QCDB;User ID=sa;Password=xxxxx;"
    'different content of FROMTIME AND TOTIME
    If FROMTIME = " " And TOTIME = " " Then
        sql = "SELECT * FROM PRODUCT WHERE (SAMPLE_SOURCE = @SOURCE OR @SOURCE = 'all') AND (PRO_TYPE = @TYPE OR @TYPE = 'all') AND (PRO_NAME = @NAME OR @NAME = 'all')" &
        "AND (EMBOSS_TOP = @EMBOSS_TOP OR @EMBOSS_TOP = 'all') AND (EMBOSS_BOTTOM = @EMBOSS_BOTTOM OR @EMBOSS_BOTTOM = 'all') AND (LINE_NO = @LINE_NO OR @LINE_NO = 'all')" &
        "AND (SHIFT = @SHIFT OR @SHIFT = 'all') AND (STD_THICK = @STAND_THICKNESS OR @STAND_THICKNESS = 'all') AND (COLOR = @COLOR OR @COLOR = 'all') "
    ElseIf FROMTIME = " " And TOTIME <> " " Then
        FROMTIME = DateTime.Now.AddYears(-15).ToString("yyyy-MM-dd")
        sql = "SELECT * FROM PRODUCT WHERE (SAMPLE_SOURCE = @SOURCE OR @SOURCE = 'all') AND (PRO_TYPE = @TYPE OR @TYPE = 'all') AND (PRO_NAME = @NAME OR @NAME = 'all')" &
        "AND (EMBOSS_TOP = @EMBOSS_TOP OR @EMBOSS_TOP = 'all') AND (EMBOSS_BOTTOM = @EMBOSS_BOTTOM OR @EMBOSS_BOTTOM = 'all') AND (LINE_NO = @LINE_NO OR @LINE_NO = 'all')" &
        "AND (SHIFT = @SHIFT OR @SHIFT = 'all') AND (STD_THICK = @STAND_THICKNESS OR @STAND_THICKNESS = 'all') AND (COLOR = @COLOR OR @COLOR = 'all') AND PRO_DATE BETWEEN'" & FROMTIME & "' AND '" & TOTIME & "'"
    ElseIf FROMTIME <> " " And TOTIME = " " Then
        TOTIME = DateTime.Now.ToString("yyyy-MM-dd")
        sql = "SELECT * FROM PRODUCT WHERE (SAMPLE_SOURCE = @SOURCE OR @SOURCE = 'all') AND (PRO_TYPE = @TYPE OR @TYPE = 'all') AND (PRO_NAME = @NAME OR @NAME = 'all')" &
        "AND (EMBOSS_TOP = @EMBOSS_TOP OR @EMBOSS_TOP = 'all') AND (EMBOSS_BOTTOM = @EMBOSS_BOTTOM OR @EMBOSS_BOTTOM = 'all') AND (LINE_NO = @LINE_NO OR @LINE_NO = 'all')" &
        "AND (SHIFT = @SHIFT OR @SHIFT = 'all') AND (STD_THICK = @STAND_THICKNESS OR @STAND_THICKNESS = 'all') AND (COLOR = @COLOR OR @COLOR = 'all') AND PRO_DATE BETWEEN'" & FROMTIME & "' AND '" & TOTIME & "'"
    Else
        sql = "SELECT * FROM PRODUCT WHERE (SAMPLE_SOURCE = @SOURCE OR @SOURCE = 'all') AND (PRO_TYPE = @TYPE OR @TYPE = 'all') AND (PRO_NAME = @NAME OR @NAME = 'all')" &
        "AND (EMBOSS_TOP = @EMBOSS_TOP OR @EMBOSS_TOP = 'all') AND (EMBOSS_BOTTOM = @EMBOSS_BOTTOM OR @EMBOSS_BOTTOM = 'all') AND (LINE_NO = @LINE_NO OR @LINE_NO = 'all')" &
        "AND (SHIFT = @SHIFT OR @SHIFT = 'all') AND (STD_THICK = @STAND_THICKNESS OR @STAND_THICKNESS = 'all') AND (COLOR = @COLOR OR @COLOR = 'all') AND PRO_DATE BETWEEN'" & FROMTIME & "' AND '" & TOTIME & "'"
    End If

    Dim connection As New SqlConnection(connectionString)
    Dim cmd = New SqlCommand(sql, connection)
    Dim dataadapter As New SqlDataAdapter(cmd)
    Dim ds As New System.Data.DataSet()

    cmd.Parameters.AddWithValue("@SOURCE", sample_source_combox.Text)
    cmd.Parameters.AddWithValue("@TYPE", product_type_combox.Text)
    cmd.Parameters.AddWithValue("@NAME", product_name_combox.Text)
    cmd.Parameters.AddWithValue("@EMBOSS_TOP", embossing_top_combox.Text)
    cmd.Parameters.AddWithValue("@EMBOSS_BOTTOM", embossing_bottom_combox.Text)
    cmd.Parameters.AddWithValue("@LINE_NO", line_number_combox.Text)
    cmd.Parameters.AddWithValue("@SHIFT", shift_serach_combox.Text)
    cmd.Parameters.AddWithValue("@STAND_THICKNESS", std_thickness_combox.Text)
    cmd.Parameters.AddWithValue("@COLOR", color_search_combox.Text)

    Try
        connection.Open()

        dataadapter.Fill(ds, "PRODUCT")
        DataGridView1.Columns.Clear()
        DataGridView1.Refresh()

        With Me.DataGridView1
            .RowsDefaultCellStyle.BackColor = Color.AliceBlue
            .AlternatingRowsDefaultCellStyle.BackColor = Color.White
        End With

        connection.Close()
        DataGridView1.DataSource = ds.Tables(0)
        DataGridView1.AutoGenerateColumns = False

    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try
End Sub

Answer 1

我花了一段时间才发现问题，在这样做的过程中，我不得不重写它，以与我通常做事情的方式相匹配(如下所示)。您可以跳过下面这段很长的代码片段，但我认为这是值得一看的，因为它确实解决了涉及日期范围的代码中的两个巨大问题。

Private Sub search_btn_Click(sender As Object, e As EventArgs) Handles search_btn.Click
    Dim sql As String = "SELECT * FROM PRODUCT WHERE 1=1"
    Dim connectionString As String = "Data Source=xxx.xx.xx.xxx;Initial Catalog=QCDB;User ID=sa;Password=xxxxx;"

    Try

        Using connection As New SqlConnection(connectionString), _
              cmd As New SqlCommand(sql, connection)

            'Match each of the parameter types and lengths to the columns
            If sample_source_combox.Text <> "all" Then
                cmd.CommandText += " AND SAMPLE_SOURCE = @SOURCE"
                cmd.Paramters.Add("@SOURCE", SqlDbType.NVarChar, 15).Value = sample_source_combox.Text
            End If

            If product_type_combox.Text <> "all" Then
                cmd.CommandText += " AND PRO_TYPE = @TYPE"
                cmd.Parmeters.Add("@TYPE", SqlDbType.NVarChar, 15).Value = product_type_combox.Text
            End If

            If product_name_combox.Text <> "all" Then
                cmd.CommandText += " AND PRO_NAME = @NAME"
                cmd.Parmeters.Add("@Name", SqlDbType.NVarChar, 60).Value = product_name_combox.Text
            End If

            If embossing_top_combox.Text <> "all" Then
                cmd.CommandText += " AND EMBOSS_TOP = @EMBOSS_TOP"
                cmd.Parameters.Add("@EMBOSS_TOP", SqlDbType.NVarChar, 20).Value = embossing_top_combox.Text
            End If

            If embossing_bottom_combox.Text <> "all" Then
                cmd.CommandText += " AND EMBOSS_BOTTOM = @EMBOSS_BOTTOM"
                cmd.Parameters.Add("@EMBOSS_BOTTOM", SqlDbType.NVarChar, 20).Value = embossing_top_combox.Text
            End If

           If line_number_combox.Text <> "all" Then
               cmd.CommandText += " AND LINE_NO = @LINE_NO"
               cmd.Parameters.Add("@LINE_NO", SqlDbtype.Int).Value = Integer.Parse(line_number_combox.Text)
           End If

           If shift_search_combox.Text <> "all" Then
               cmd.CommandText += " AND SHIFT = @SHIFT"
               cmd.Parameters.Add("@SHIFT", SqlDbType.NVarChar, 10).Value = shift_search_combox.Text
           End If

           'This should probably be a RANGE of thicknesses, just like the dates.
           If std_thickness_combox.Text <> "all" Then 
              cmd.CommandText += " AND STD_THICK = @STAND_THICKNESS"
              cmd.Parameters.Add("@STAND_THICKNESS", SqlDbType.Float).Value = Double.Parse(std_thickness_combox.Text)
           End If

            If color_search_combox.Text <> "all" Then
               cmd.CommandText += " AND COLOR = @COLOR"
               cmd.Parameters.Add("@COLOR", SqlDbType.NVarChar, 15).Value = color_search_combox.Text
            End If

            If Not String.IsNullOrWhiteSpace(FROMTIME) THEN
               cmd.CommandText += " AND PRO_DATE >= @FROMTIME"
               cmd.Parameters.Add("@FROMTIME", SqlDbType.DateTime).Value = DateTime.Parse(FROMTIME)
            End If

             If Not String.IsNullOrWhiteSpace(TOTIME) THEN
               cmd.CommandText += " AND PRO_DATE < @TOTIME"
               cmd.Parameters.Add("@TOTIME", SqlDbType.DateTime).Value = DateTime.Parse(TOTIME)
            End If

            Dim dataadapter As New SqlDataAdapter(cmd)
            Dim ds As New System.Data.DataSet()  
            dataadapter.Fill(ds, "PRODUCT")
        End Using

        With Me.DataGridView1
            .RowsDefaultCellStyle.BackColor = Color.AliceBlue
            .AlternatingRowsDefaultCellStyle.BackColor = Color.White
        End With

        DataGridView1.DataSource = ds.Tables(0)
        DataGridView1.AutoGenerateColumns = False

    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try
End Sub

关于为什么你第二次没有得到结果的解释。在运行此方法时，您可以执行以下操作：

DataGridView1.Columns.Clear()

但你也会这么做：

DataGridView1.AutoGenerateColumns = False

第一个片段擦除以前设置的任何列，无论是通过代码还是由设计器。你第一次就没事了，因为你可以自动生成列。但是，到第二次，您已经将其设置为False。现在，您没有为网格定义列，并且告诉它不要自动生成它们。因此，你看不到任何结果。