SQL Select from variable is column name and column data equals an integer

Question

0.00/5 (No votes)

See more:

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.

Posted 9-Jan-14 3:26am

moordoom

Updated 9-Jan-14 3:27am

v2

Add a Solution

Comments

PIEBALDconsult 9-Jan-14 9:59am

You haven't shown us what your database table looks like, but I suspect it's the main cause of your trouble and you should rethink it.
Other things to consider include using a parameterized query to pass in the selected shift and trty not to use the sa user.

3 solutions

Solution 2

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)

Posted 9-Jan-14 11:21am

Christian Graus

Solution 3

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

Posted 10-Jan-14 3:13am

moordoom

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-01-09T03:44:00

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:

VB

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)