Checkbox problem in VB.NET

Question

kavithabhaskar 0 Junior Poster

15 Years Ago

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

vb.net

3 Contributors
12 Replies
137 Views
4 Days Discussion Span
Latest Post 15 Years Ago Latest Post by rohitbce

All 12 Replies

selvaganapathy 31 Posting Pro

15 Years Ago

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 31 Posting Pro

15 Years Ago

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

selvaganapathy 31 Posting Pro

15 Years Ago

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()
....
....

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kavithabhaskar 0 Junior Poster · Answer 1 · 2008-07-10T22:05:52+00:00

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 0 Junior Poster · Answer 2 · 2008-07-10T22:09:11+00:00

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)
....

kavithabhaskar 0 Junior Poster · Answer 3 · 2008-07-10T23:39:10+00:00

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 0 Junior Poster · Answer 4 · 2008-07-11T01:34:32+00:00

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

kavithabhaskar 0 Junior Poster · Answer 5 · 2008-07-11T09:18:04+00:00

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 0 Junior Poster · Answer 6 · 2008-07-11T20:52:48+00:00

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 0 Junior Poster · Answer 7 · 2008-07-11T20:54:42+00:00

kavithabhaskar 0 Junior Poster

15 Years Ago

how do i mark a post as the right answer ?

selvaganapathy 31 Posting Pro · Answer 8 · 2008-07-11T22:06:55+00:00

selvaganapathy 31 Posting Pro

15 Years Ago

Click Mark Thread as Solved below the replies

rohitbce 0 Newbie Poster · Answer 9 · 2008-07-14T18:17:02+00:00

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

Checkbox problem in VB.NET

Recommended Answers Collapse Answers

All 12 Replies

Recommended Answers