Checkbox problem in VB.NET

[kavithabhaskar]

when i check one or 2 or 3 or all 4 chechboxes..i want a corresponding SQL query to get executed.

Please help me rectify my code..
thanks
kavitha.

Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

con.Open()

If (CheckBox1.Checked = True) And (CheckBox1.Checked = True) And (CheckBox3.Checked = True) And (CheckBox4.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where MECH='X' and MACH='X' AND UGSTRUCTURES='X' AND UGPCBXCHANGE='X'")

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Try
Dim ds As DataSet = New DataSet()

ds.Tables.Add("table1")

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1").DefaultView

Finally

con.Close()

cmd1 = Nothing

da.Dispose()

con.Dispose()

End Try

End If

[selvaganapathy]

To execute t
he Corresponding Query first Generate you SQL by checking the Checkbox.

Dim sSQL as String 
Dim con As ...
..
..
sSQL = .... 'Your Default Query

If CheckBox1.Checked = True Then
    sSQL = ....
End If

If CheckBox2.Checked = True Then
    sSQL = ....
End If
....
....
'Finally
cmd1= New OleDbCommand(sSQL)
....

[kavithabhaskar]

Thanks for ur reply Selva Ganapathy.. BTW what does the default query mean here ?

I shall try out as you mentioned thanks

kavitha. I shall get back to u in a few minutes with the corrections..

[kavithabhaskar]

Hi KSG

the code as you mentioned is as follows,,but it does not work,,,

I also want multiple selections to be executed by the SQL query..pls suggest!!

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

con.Open()

If (CheckBox1.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where MECH='X'")

Else
If (CheckBox2.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where MACH='X'")

ElseIf (CheckBox3.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where UGSTRUCTURES='X'")

ElseIf (CheckBox4.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where UGPCBXCHANGE='X'")
End If

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Try
Dim ds As DataSet = New DataSet()

ds.Tables.Add("table1")

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1").DefaultView

Finally

con.Close()

cmd1 = Nothing

da.Dispose()

con.Dispose()

End Try

End If

End Sub

To execute t
he Corresponding Query first Generate you SQL by checking the Checkbox.

Dim sSQL as String 
Dim con As ...
..
..
sSQL = .... 'Your Default Query

If CheckBox1.Checked = True Then
    sSQL = ....
End If

If CheckBox2.Checked = True Then
    sSQL = ....
End If
....
....
'Finally
cmd1= New OleDbCommand(sSQL)
....

[selvaganapathy]

Do you mean If CheckBox1 is selected then Mech ='x' should be in query and If CheckBox2 is Selected then Mach='x' should be in query etc..

If i am Correct,

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")
 
        Dim cmd1 As OleDbCommand
        Dim sSQL As String
        Dim sWhereSQL As String = ""

        sSQL = "SELECT * FROM Table1 "

        If (CheckBox1.Checked = True) Then
            sWhereSQL = " WHERE MECH= 'X'"
        End If

        If (CheckBox2.Checked = True) Then
            If sWhereSQL = "" Then
                sWhereSQL = " WHERE"
            Else
                sWhereSQL = sWhereSQL & " AND"
            End If
            sWhereSQL = sWhereSQL & " MACH= 'X'"
        End If

        If (CheckBox3.Checked = True) Then
            If sWhereSQL = "" Then
                sWhereSQL = " WHERE"
            Else
                sWhereSQL = sWhereSQL & " AND"
            End If
            sWhereSQL = sWhereSQL & " UGSTRUCTURES='X'"
        End If

        If (CheckBox4.Checked = True) Then
            If sWhereSQL = "" Then
                sWhereSQL = " WHERE"
            Else
                sWhereSQL = sWhereSQL & " AND"
            End If
            sWhereSQL = sWhereSQL & " UGPCBXCHANGE='X'"
        End If
        cmd1 = New OleDbCommand(sSQL & " " & sWhereSQL )

           'Rest your codings

     End Sub

[kavithabhaskar]

Hi KSG: Thanks for the reply..but SQL code is even more confusing..can u help me with the same code in VB.NET?

it will be very helpful for me

thansk

kavitha

[kavithabhaskar]

yes.,what u understood is exactly i want to implement in my code..

[selvaganapathy]

actually, i gave you the full coding of SQL. Is this code working? SQL String is generated dynamically by checking the each status of the Check box. Then you can create command object using this SQL string. It is perfectly working in VB.NET. You Should add this coding with your coding and not separately.

Just Prototype

'Code Create the Connection Objec
'Dim Con as .....

'Default QUERY without any Condition
Dim sSQL as String = "SELECT * FROM Table1"
'This String to represent the Condition
Dim sWhereSQL as String = ""

'Then use the Above coding to Generate sWhereSQL  String
If CheckBox1.Checked = True Then
...

'Here at last sWhereSQL contains your condition
'Then Create your command Object according to the sWhereSQL string
'Now sSQL & sWhereSQL is your Query with Condition
...
'Now the Command object is created according to the Query, you can write your rest of codings

cmd1= New OleDbCommand ( sSQL & " " & sWhereSQL )

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Try
Dim ds As DataSet = New DataSet()
....
....

[kavithabhaskar]

Thanks KSG for the reply.. i dont have VB installed in my pc.. i'll go to school and get this code working else I shall get back to you again..i would really appreciate if you keep looking for any qns or doubts from my end.. thanks very much

kavitha.

[kavithabhaskar]

thanks KSG..i got it right..this is it.. :)

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim con As New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

con.Open()

If (CheckBox1.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where MECH='X'", con)

End If

If (CheckBox2.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where MACH='X'", con)

End If

If (CheckBox3.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where UGSTRUCTURES='X'", con)

End If

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Try
Dim ds As DataSet = New DataSet()

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1").DefaultView

Finally

con.Close()

cmd1 = Nothing

da.Dispose()

con.Dispose()

End Try

End Sub

[kavithabhaskar]

how do i mark a post as the right answer ?

[selvaganapathy]

Click Mark Thread as Solved below the replies

[rohitbce]

when i check one or 2 or 3 or all 4 chechboxes..i want a corresponding SQL query to get executed.

Please help me rectify my code..
thanks
kavitha.

Dim con As OleDbConnection = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\bashkark\Desktop\Copy of USERS.mdb")

Dim cmd1 As New OleDbCommand

con.Open()

If (CheckBox1.Checked = True) And (CheckBox1.Checked = True) And (CheckBox3.Checked = True) And (CheckBox4.Checked = True) Then

cmd1 = New OleDbCommand("select * from table1 where MECH='X' and MACH='X' AND UGSTRUCTURES='X' AND UGPCBXCHANGE='X'")

Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd1)

Try
Dim ds As DataSet = New DataSet()

ds.Tables.Add("table1")

da.Fill(ds, "table1")

DataGridView1.DataSource = ds.Tables("table1").DefaultView

Finally

con.Close()

cmd1 = Nothing

da.Dispose()

con.Dispose()

End Try

End If

HI

Dont Do like that Use check list box and by using that use for loop or for each do the Code you will get