如何将datagridview单元格用于vb.net中的WHERE子句语句

Question

我在vb.net中具有此SQL查询，其中我必须将datagridview单元格用作选择查询的参数。

这是代码：

Private Sub ClassSchedule()
        Dim strConn As String = My.Settings.SLCBRegistrarDBConnectionString
        Dim sqlCon As SqlConnection = New SqlConnection(strConn)
        Try
            sqlCon.Open()
            Dim QUERY As String
            QUERY = "SELECT CSchedClass.SCode as Code, ListofSubjects.[Course No.], ListofSubjects.[Descriptive Title], CSchedSubTD.TimeAndDay as Schedule, UtlyRoom.RoomName as Room, CSchedSubInstructor.NameInit as Instructor, CSchedSubDept.Dept, CSchedSubDept.Department " &
                    "FROM CSchedMAIN INNER JOIN CSchedClass ON CSchedMAIN.SubjCode = CSchedClass.id INNER JOIN  ListofSubjects ON CSchedClass.Subj = ListofSubjects.SubjectID INNER JOIN CSchedSubTD ON CSchedMAIN.TDCode = CSchedSubTD.TDCode INNER JOIN UtlyRoom ON CSchedMAIN.RoomID = UtlyRoom.RoomID INNER JOIN CSchedSubInstructor ON CSchedMAIN.InstID = CSchedSubInstructor.EmpID INNER JOIN  CSchedSubDept ON CSchedMAIN.Dept = CSchedSubDept.DeptID INNER JOIN SemesterList ON CSchedMAIN.SemID = SemesterList.SemID INNER JOIN SchoolYear ON CSchedMAIN.SYID = SchoolYear.[SY ID] INNER JOIN CSchedSubSect ON CSchedMAIN.Section = CSchedSubSect.id " &
                    "WHERE (CSchedSubSect.Section = '" & dgvSections.SelectedCells & "') AND  (SchoolYear.[School Year] = '" & cmbSY.Text & "') AND (SemesterList.Description = '" & cmbSemester.Text & "')"
            CMD = New SqlCommand(QUERY, sqlCon)
            CMD.ExecuteNonQuery()
            Adapter = New SqlDataAdapter(CMD)
            DT = New DataTable()
            Adapter.Fill(DT)
            dgvSchedule.DataSource = DT
            dgvSchedule.Columns(0).Width = 80
            dgvSchedule.Columns(1).Width = 120
            dgvSchedule.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            dgvSchedule.Columns(3).Width = 150
            dgvSchedule.Columns(4).Width = 80
            dgvSchedule.Columns(5).Width = 150
            dgvSchedule.Columns(6).Visible = False
            dgvSchedule.Columns(7).Visible = False
            sqlCon.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

问题是下面的语法是不正确的：

WHERE (CSchedSubSect.Section = '" & dgvSections.SelectedCells & "')

目的是当我单击datagridview" dgvsections"中的特定单元格时，上面的查询应执行并加载dataGridView" dgvschedule"。

请帮助！

Answer 1

您需要参数化查询。

通过您使用SelectedCells属性，我假设您也想检查多个值。如果是这种情况，那么您还需要将SQL语句从平等的符号操作员更改为在运算符中，这是逻辑上等同于使用或多个值的逻辑。否则，如果您只想检查一个值，则需要从使用SelectedCells更改为CurrentCell。

这是一个快速示例，演示我的建议：

'Declare the object that will bind the DataGridView
Dim dt As DataTable = New DataTable
'Declare the connection object
Dim con As SqlConnection
'Wrap code in Try/Catch
Try
    'Set the connection object to a new instance
    'TODO: Change "My Connection String Here" with a valid connection string
    con = New SqlConnection("My Connection String Here")
    'Create a new instance of the command object
    Using cmd As SqlCommand = New SqlCommand("SELECT
                                                cschedclass.scode AS code,
                                                listofsubjects.[Course No.],
                                                listofsubjects.[Descriptive Title],
                                                cschedsubtd.timeandday AS schedule,
                                                utlyroom.roomname AS room,
                                                cschedsubinstructor.nameinit AS instructor,
                                                cschedsubdept.dept,
                                                cschedsubdept.department
                                            FROM cschedmain
                                            INNER JOIN cschedclass
                                                ON cschedmain.subjcode = cschedclass.id
                                            INNER JOIN listofsubjects
                                                ON cschedclass.subj = listofsubjects.subjectid
                                            INNER JOIN cschedsubtd
                                                ON cschedmain.tdcode = cschedsubtd.tdcode
                                            INNER JOIN utlyroom
                                                ON cschedmain.roomid = utlyroom.roomid
                                            INNER JOIN cschedsubinstructor
                                                ON cschedmain.instid = cschedsubinstructor.empid
                                            INNER JOIN cschedsubdept
                                                ON cschedmain.dept = cschedsubdept.deptid
                                            INNER JOIN semesterlist
                                                ON cschedmain.semid = semesterlist.semid
                                            INNER JOIN schoolyear
                                                ON cschedmain.syid = schoolyear.[SY ID]
                                            INNER JOIN cschedsubsect
                                                ON cschedmain.section = cschedsubsect.id
                                            WHERE 
                                                cschedsubsect.section    = @section AND
                                                schoolyear.[School Year] = @year AND
                                                semesterlist.description = @semester;", con)
        'Parameterize the query
        With cmd.Parameters
            .AddWithValue("@section", dgvSections.CurrentCell.Value.ToString)
            .AddWithValue("@year", cmbSY.Text)
            .AddWithValue("@semester", cmbSemester.Text)
        End With
        'Create a DataAdapter to fill the underlying DataTable with data
        Using adapter As SqlDataAdapter = New SqlDataAdapter(cmd)
            'Open the connection
            con.Open()
            'Use the Fill method to complete the operation
            adapter.Fill(dt)
            'Close the connection
            con.Close()
        End Using
    End Using
Catch ex As Exception
    'Display the error
    Console.WriteLine(ex.Message)
Finally
    'Check if the connection object was initialized
    If con IsNot Nothing Then
        If con.State = ConnectionState.Open Then
            'Close the connection if it was left open(exception thrown)
            con.Close()
        End If
        'Dispose of the connection object
        con.Dispose()
    End If
End Try
'Bind the table to the DataGridView
dgvSchedule.DataSource = dt