SQL从变量中选择列名称，列数据等于整数 [英] SQL Select from variable is column name and column data equals an integer

问题描述

我正在尝试在VB中创建一个SQL Select语句。

我需要根据复选框选择一个列，然后选择将其写入datagridview的数据，并添加3个列要附加到不同的表。



下面的代码不会尝试创建上述所有内容，只需将数据拉出并放入datagridview中并添加3列

 Private Sub Button1_Click（sender As System.Object，e As System.EventArgs）Handles Button1.Click 
'声明 - 有些会给出未使用的错误（这是预期的）
 Dim CheckBox1 As CheckBox 
 Dim CheckBox2 As CheckBox 
 Dim CheckBox3 As CheckBox 
 Dim Runflg作为Char 
 
'IF循环选择复选框
如果Me.CheckBox1.Checked = False和Me.CheckBox2.Checked = False和Me.CheckBox3.Checked = False那么
 MsgBox（你必须选择一个转换!!!）
退出子
结束如果
如果Me.CheckBox1.Checked = True且Me.CheckBox2.Checked = True且Me.CheckBox3.Checked = True则
 MsgBox（你必须选择一个班次！ !!）
退出Sub 
结束如果
 
如果Me.CheckBox1.Checked = True且Me.CheckBox2.Checked = True或Me.CheckBox1.Checked = True和Me .CheckBox3.Checked = True或Me.CheckBox2.Checked = True和Me.CheckBox3.Checked = True然后
 MsgBox（你必须选择一个转换!!!）
退出Sub 
结束如果
 
如果Me.CheckBox1.Checked = True则
 Runflg =RunFlag1
结束如果
 
如果Me.CheckBox2.Checked = True然后
 Runflg =RunFlag2
结束如果
 
如果Me.CheckBox3.Checked = True那么
 Runflg =RunFlag3
结束如果
 
'SQL数据连接
 Dim sqlCon1作为新的SqlConnection（Da ta Source = SERVER1\DEV01; database = Production; uid = sa; pwd = passwordhere）
 Dim daAdap1 As New SqlDataAdapter（SELECT MACHINENUMBER，STYLE FROM ProductionSet WHERE VALUES Runflg = 1，sqlCon1）'我的问题在这里
 Dim SQLcmdBuilder1 As New SqlCommandBuilder（daAdap1）
 Dim ds1 As New DataSet 
 
'将列添加到Datagridview 
 Dim AddCol1 As New DataGridViewTextBoxColumn 
 Dim AddCol2 As New DataGridViewTextBoxColumn 
 Dim AddCol3 As New DataGridViewTextBoxColumn 
 AddCol1.DataPropertyName =Sections
 AddCol1.HeaderText =Sections
 AddCol1.Name =Sections
 AddCol2.DataPropertyName =RunTime
 AddCol2.HeaderText =RunTime
 AddCol2.Name =RunTime
 AddCol3.DataPropertyName =Date
 AddCol3.HeaderText =Date 
 AddCol3.Name =Date
 
'将datagridview写入表单
 daAdap1.Fill（ds1，Runflg）
 DataGridView1.DataSource = ds1.Tables（0）
 DataGridView1.Columns.Add（AddCol1）
 DataGridView1.Columns.Add（AddCol2） 
 DataGridView1.Columns.Add（AddCol3）
 
 End Sub 

我的主要关注点是以下：

1.强制选中一个复选框。

2.根据选择，执行一个Select语句，它从Runflg变量等于列的所有记录中提取数据RunFlag1，RunFlag2或Runflag3的名称，列中的记录等于1.

3.将信息写入datagridview。

4.允许用户输入已添加第1列和第2列中的Sections和Runtime，并在表单的下拉列表中添加Data Selected到添加的第3列。

5.将datagridview中的数据写入/追加到另一个表中。 br />


我还没有超过select语句，所以数字3.，4。和5.没有完成。

解决方案

一些建议：为什么不使用Radio Buttons而不是复选框？用户习惯于为RB选择其中一个，他们希望复选框是独立的，因此他们可以选择零，一个或两个。还简化了你的代码...



我读你的问题的方式是你需要不同的SQL SELECT语句，具体取决于选择了哪个复选框？所以这样做：

  Dim  sql  As  字符串 
 如果 CheckBox1.Selected 然后 
 sql =   SELECT MACHINENUMBER，STYLE FROM ProductionSet WHERE VALUES Runflg1 = 1 
  Elseif  CheckBox2.Selected 然后 
 sql =   SELECT MACHINENUMBER，STYLE FROM ProductionSet WHERE VALUES Runflg2 = 1 
  Elseif  CheckBox3.Selected 然后 
 sql =   SELECT MACHINENUMBER，STYLE FROM ProductionSet WHERE VALUES Runflg3 = 1 
  Else  
 MessageBox.S how（ 请选择Shift）
 结束 如果 
  Dim  daAdap1 正如 新 SqlDataAdapter（sql，sqlCon1）

我会写一个proc然后传递我要检查的标志

 SELECT MACHINENUMBER，来自ProductionSet WHERE的样式（@ check1& runflg1 = 1）或（@ check2& runflg2 = 1）或（@ check3& runflg3 = 1）

&＃ 39; s未经测试，我认为有点会是1或0，它会起作用。对于要检查的不同值，更通用的解决方案是：

 SELECT MACHINENUMBER，来自ProductionSet的样式WHERE（@ check1 = 1 AND runflg1 = 1）或（@ check2 = 1 AND runflg2 = 1）或（@ check3 = 1 AND runflg3 = 1）

这就是我的工作方式。

我相信周日有300种方法来清理它。



现在得到AddCol3中的日期，并附加到新表格。

 Imports System.Data.SqlClient 
 Imports System.Windows。 Forms.DataGridView 
 Imports System.Windows.Forms.Checkbox 
 
 
 Public Class Form5 
 
 Public Sub Form5_Load（sender As System.Object，e As System.EventArgs）Handles MyBase.Load 
 Dim CheckBox1 As New CheckBox 
 Dim CheckBox2 As New CheckBox 
 Dim CheckBox3 As New CheckBox 
 
 End Sub 
 
'加载数据网格
 
私有子按钮1_Click（发送者为System.Object，e As System.EventArgs）处理Button1.Click 
 
 
 Dim sql As String 
 
'复选框检查循环
如果CheckBox1.CheckState = False和CheckBox2.CheckState = False和CheckBox3.CheckState = False然后
 MessageBox.Show（请选择一个Shift）
退出Sub 
结束如果
如果CheckBox1.Checked = True和CheckBox2.Checked = True然后
 MessageBox.Show（请选择一个班次）
退出Sub 
结束如果
如果CheckBox2.Checked = True和CheckBox3 .Checked = True然后
 MessageBox.Show（请选择一个班次）
退出子
结束如果
如果CheckBox1.Checked = True且CheckBox3.Checked = True然后
 MessageBox.Show（请选择一个班次）
退出子
结束如果
 
如果CheckBox1.Checked = True则
 sq l =SELECT MACHINENBERBER，STYLE FROM Production_Set WHERE RunFlag1 = 1
 ElseIf CheckBox2.Checked = True然后
 sql =SELECT MACHINENUMBER，STYLE FROM Production_Set WHERE RunFlag2 = 1
 ElseIf CheckBox3。 Checked = True然后
 sql =SELECT MACHINENUMBER，STYLE FROM Production_Set WHERE RunFlag3 = 1
 End if 
 
 Dim sqlCon1 As New SqlConnection（Data Source = SERVER1\DEV01 ; database = Production; uid = sa; pwd = passwordhere）
 Dim daSWC1 As New SqlDataAdapter（sql，sqlCon1）
 Dim SQLcmdBuilder1 As New SqlCommandBuilder（daSWC1）
 Dim ds1 As New DataSet 
 
 Dim AddCol1 As New DataGridViewTextBoxColumn 
 Dim AddCol2 As New DataGridViewTextBoxColumn 
 Dim AddCol3 As New DataGridViewTextBoxColumn 
 AddCol1.DataPropertyName =Sections
 AddCol1.HeaderText =部分
 AddCol1.Name =部分
 AddC ol2.DataPropertyName =RunTime
 AddCol2.HeaderText =RunTime
 AddCol2.Name =RunTime
 AddCol3.DataPropertyName =Date
 AddCol3.HeaderText =Date 
 AddCol3.Name =Date
 
 daSWC1.Fill（ds1，MACHINENUMBER）
 DataGridView1.DataSource = ds1.Tables（0）
 DataGridView1。 Columns.Add（AddCol1）
 DataGridView1.Columns.Add（AddCol2）
 DataGridView1.Columns.Add（AddCol3）
 
 End Sub 
 
 Private Sub Button3_Click（sender As System.Object，e As System.EventArgs）Handles Button3.Click 
 Me.Close（）
 End Sub 
 
 
 End Class 

I am trying to create a SQL Select statement in VB.
I need to select a column based on a check box, and then select that data where it writes it to a datagridview, and adds 3 columns to be appended to a different table.

The code below does not attempt to create all the above just yet, just pulling the data and putting it in a datagridview and adding the 3 columns

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        'Declarations - Some will give unused errors (this is expected)
        Dim CheckBox1 As CheckBox
        Dim CheckBox2 As CheckBox
        Dim CheckBox3 As CheckBox
        Dim Runflg As Char
        
        'IF loop for checkbox selection
        If Me.CheckBox1.Checked = False And Me.CheckBox2.Checked = False And Me.CheckBox3.Checked = False Then
            MsgBox("You must Select ONE Shift!!!")
            Exit Sub
        End If
        If Me.CheckBox1.Checked = True And Me.CheckBox2.Checked = True And Me.CheckBox3.Checked = True Then
            MsgBox("You must Select ONE Shift!!!")
            Exit Sub
        End If

        If Me.CheckBox1.Checked = True And Me.CheckBox2.Checked = True Or Me.CheckBox1.Checked = True And Me.CheckBox3.Checked = True Or Me.CheckBox2.Checked = True And Me.CheckBox3.Checked = True Then
            MsgBox("You must Select ONE Shift!!!")
            Exit Sub
        End If

        If Me.CheckBox1.Checked = True Then
            Runflg = "RunFlag1"
        End If

        If Me.CheckBox2.Checked = True Then
            Runflg = "RunFlag2"
        End If

        If Me.CheckBox3.Checked = True Then
            Runflg = "RunFlag3"
        End If
        
        'SQL Data Connection
        Dim sqlCon1 As New SqlConnection("Data Source=SERVER1\DEV01;database=Production;uid=sa;pwd=passwordhere")
        Dim daAdap1 As New SqlDataAdapter("SELECT MACHINENUMBER, STYLE FROM ProductionSet WHERE VALUES Runflg = 1", sqlCon1)' My issue is here
        Dim SQLcmdBuilder1 As New SqlCommandBuilder(daAdap1)
        Dim ds1 As New DataSet
        
        ' Adds Columns to Datagridview
        Dim AddCol1 As New DataGridViewTextBoxColumn
        Dim AddCol2 As New DataGridViewTextBoxColumn
        Dim AddCol3 As New DataGridViewTextBoxColumn
        AddCol1.DataPropertyName = "Sections"
        AddCol1.HeaderText = "Sections"
        AddCol1.Name = "Sections"
        AddCol2.DataPropertyName = "RunTime"
        AddCol2.HeaderText = "RunTime"
        AddCol2.Name = "RunTime"
        AddCol3.DataPropertyName = "Date"
        AddCol3.HeaderText = "Date"
        AddCol3.Name = "Date"

        'Writes datagridview to form
        daAdap1.Fill(ds1, Runflg)
        DataGridView1.DataSource = ds1.Tables(0)
        DataGridView1.Columns.Add(AddCol1)
        DataGridView1.Columns.Add(AddCol2)
        DataGridView1.Columns.Add(AddCol3)

End Sub

My main focus is the following:
1. Force a checkbox selection.
2. Based on the selection, execute a Select Statement where it pulls the data from all records where the Runflg variable equals column name of RunFlag1, RunFlag2 or Runflag3, and the record in the column equal 1.
3. Write the info to a datagridview.
4. Allow the user to enter in Sections and Runtime in added columns 1 and 2, and add the Data Selected in a drop down on the form to added column 3.
5. Write/Append the data in the datagridview to another table.

I haven't got past the select statement yet, so numbers 3., 4., and 5. are not completed.

解决方案

Some suggestions: instead of checkboxes, why not use Radio Buttons? Users are used to "these select one of" for RB, where they expect checkboxes to be independant, so they can select zero, one or two of them. Also simplifies your code...

The way I read your question is that you want different SQL SELECT statements depending on which checkbox is selected? So do it that way:

Dim sql As String
if CheckBox1.Selected Then 
   sql = "SELECT MACHINENUMBER, STYLE FROM ProductionSet WHERE VALUES Runflg1 = 1"
Elseif CheckBox2.Selected Then
   sql = "SELECT MACHINENUMBER, STYLE FROM ProductionSet WHERE VALUES Runflg2 = 1"
Elseif CheckBox3.Selected Then
   sql = "SELECT MACHINENUMBER, STYLE FROM ProductionSet WHERE VALUES Runflg3 = 1"
Else
   MessageBox.Show("Please select a Shift")
End If
Dim daAdap1 As New SqlDataAdapter(sql, sqlCon1)

I would write a proc and pass in flags for what I wanted to check

SELECT MACHINENUMBER, STYLE FROM ProductionSet WHERE (@check1 & runflg1 = 1) or (@check2 & runflg2 = 1 ) or (@check3 & runflg3 = 1)

That's untested, I assume that as a bit would be 1 or 0, it would work. A more general solution, for different values to check, would be:

SELECT MACHINENUMBER, STYLE FROM ProductionSet WHERE (@check1 = 1 AND runflg1 = 1) or (@check2 = 1 AND runflg2 = 1 ) or (@check3 = 1 AND runflg3 = 1)

Here is what I got to work.
I am sure there are 300 ways to Sunday to clean this up.

Now to get the Date in AddCol3, and to append to a new table.

Imports System.Data.SqlClient
Imports System.Windows.Forms.DataGridView
Imports System.Windows.Forms.Checkbox


Public Class Form5

    Public Sub Form5_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim CheckBox1 As New CheckBox
        Dim CheckBox2 As New CheckBox
        Dim CheckBox3 As New CheckBox

    End Sub

    'Loads Datagrid

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click


        Dim sql As String

        'Checkbox Check Loop
        If CheckBox1.CheckState = False And CheckBox2.CheckState = False And CheckBox3.CheckState = False Then
            MessageBox.Show("Please select a Shift")
            Exit Sub
        End If
        If CheckBox1.Checked = True And CheckBox2.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If
        If CheckBox2.Checked = True And CheckBox3.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If
        If CheckBox1.Checked = True And CheckBox3.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If

        If CheckBox1.Checked = True Then
            sql = "SELECT MACHINENUMBER, STYLE FROM Production_Set WHERE RunFlag1 = 1"
        ElseIf CheckBox2.Checked = True Then
            sql = "SELECT MACHINENUMBER, STYLE FROM Production_Set WHERE RunFlag2 = 1"
        ElseIf CheckBox3.Checked = True Then
            sql = "SELECT MACHINENUMBER, STYLE FROM Production_Set WHERE RunFlag3 = 1"
        End If

        Dim sqlCon1 As New SqlConnection("Data Source=SERVER1\DEV01;database=Production;uid=sa;pwd=passwordhere")
        Dim daSWC1 As New SqlDataAdapter(sql, sqlCon1)
        Dim SQLcmdBuilder1 As New SqlCommandBuilder(daSWC1)
        Dim ds1 As New DataSet

        Dim AddCol1 As New DataGridViewTextBoxColumn
        Dim AddCol2 As New DataGridViewTextBoxColumn
        Dim AddCol3 As New DataGridViewTextBoxColumn
        AddCol1.DataPropertyName = "Sections"
        AddCol1.HeaderText = "Sections"
        AddCol1.Name = "Sections"
        AddCol2.DataPropertyName = "RunTime"
        AddCol2.HeaderText = "RunTime"
        AddCol2.Name = "RunTime"
        AddCol3.DataPropertyName = "Date"
        AddCol3.HeaderText = "Date"
        AddCol3.Name = "Date"

        daSWC1.Fill(ds1, "MACHINENUMBER")
        DataGridView1.DataSource = ds1.Tables(0)
        DataGridView1.Columns.Add(AddCol1)
        DataGridView1.Columns.Add(AddCol2)
        DataGridView1.Columns.Add(AddCol3)

    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub


End Class

这篇关于SQL从变量中选择列名称，列数据等于整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！