How to fetch data associated with Checked Field from CheckBoxList

Question

0.00/5 (No votes)

See more:

My problem is that
I have got a CheckedListBox
in which I am loading the
Field names or say Columns on the basis of
selected table names from a ComboBox's Selected Index Changed
Event.Number of Fields vary for Each selected
table From Combobox and hence the Items in CheckedListBox.
I just want, how to write a Select Statement
to fetch the data associated with the field
which are checked in the CheckedListBox.

or Can I Get all the checked fields seperated
with Comma .

I am getting Confused where to start.
Need help.

Posted 26-May-11 21:31pm

Karwa_Vivek

Updated 26-May-11 23:19pm

v3

Add a Solution

2 solutions

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)

Rob Branaghan · Accepted Answer · 2011-05-26T23:57:00

Solution 1

Ok what I did is add a button to click after selecting all of the items you want.

VB

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      'Create a list of type string and initialise
      Dim strings As List(Of String) = New List(Of String)
      'Then for each string that is checked in the listbox
      For Each s As String In CheckedListBox1.CheckedItems
          'Add it to the list
          strings.Add(s)
      Next
      'Now you have a list of all the selected strings
      For Each s As String In strings
          'I just show them in messagebox
            MsgBox(s)
        Next

    End Sub

Hope that helps

Posted 26-May-11 23:57pm

Rob Branaghan

Comments

Karwa_Vivek 27-May-11 6:40am

Thanks for the Help
it worked fine ,but i need to pass
these fields in a Sql Select Statment
in place of Field names.So that according
to the fields i can fetch the data stored in the
Table. Is Some How tricky but I think it is possible ..

Rob Branaghan 27-May-11 6:46am

Are these fields like for example...

Table : Person
#################
Field : FirstName
Field : LastName
Field : Age
#################
And you want to have FirstName or Age selected and then generate SQL from that?

Is that what you mean?

Cant you just return all the information and then in the background vb code populate the datagrid view or what ever?

Karwa_Vivek 27-May-11 7:06am

It can Be Done .but the requirement is
that user can select
the field according to there Need

Karwa_Vivek 27-May-11 6:57am

yes Exactly..
But later on I have also to Supply
the Table Name which I Selected from the comBobox

Rob Branaghan 27-May-11 7:00am

I suppose you could do it where you then combine all the selected items from that list into a string and then select that in the sql, I dont like doing that though, I prefer to use stored procedures.

Karwa_Vivek 27-May-11 7:00am

Is it Possible Like That ?

Karwa_Vivek 27-May-11 7:03am

Ok .I have managed to Store all the selected
Field in a single String separated by comma.
Now Got Stuck ..how to pass in the query

Rob Branaghan 27-May-11 7:32am

I managed to write a stored procedure that does what you need.

/*

Details
|0|DynamicStoredProc|0x|
|1||1x|
|2|1|2x|

Creator
|4||4x|
|3|Robert Branaghan @ Love2Code Ltd|3x|

Modified
|5||5x|
|6||6x|
|7||7x|
*/
CREATE PROCEDURE DynamicStoredProc
-- Add the parameters for the stored procedure here
@Select varchar(500) = '',
@Table varchar(500) = '',
@Where varchar(500) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @Query varchar(500)

SET @Query = 'SELECT ' + @Select + ' FROM ' + @Table

if LEN(@Where) > 0
BEGIN
SET @Query = @Query + ' WHERE ' + @Where
END

EXEC(@Query)
END

Rob Branaghan 27-May-11 8:00am

One thing though to note.... this allows SQL INjection to work. AS i managed to drop a database by sending a where statement of "1=1; Drop database destroyable; --"

So if you are going to allow a user to enter their own where statements, make sure that you protect against SQL Injection.

Karwa_Vivek 27-May-11 8:49am

Thanks a Ton
..I Got it working at The End of the Day

to avoid SQL Injection
I just Used
SET @Query = 'SELECT ' + @Select + ' FROM ' + @Table
thanks again

Rob Branaghan 27-May-11 9:29am

Im glad I could help!

Karwa_Vivek · Accepted Answer · 2011-05-27T18:41:00

So here is the Final Solution
What I did to get the required
Results Hope this helps
other Also :
the First part what I have done to get the Checked
Fields under a String Separated by commas

 Public Sub getCheckedValues()
 Dim sb As New StringBuilder
        Dim i As Integer

        For i = 0 To chkBox.Items.Count - 1
            If chkBox.GetItemChecked(i) = True Then
                sb.Append(chkBox.Items(i).ToString & ",")
            End If
        Next
Dim str As String = sb.ToString.Trim(",")

and then As Rob suggested,Used a Dynamic Stored Procedure to fetch the Data and then loaded them to The DataGridView

Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        With con
            .ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=New1;Integrated Security=True;Pooling=False"
            .Open()

        End With
        Dim s As String = cmbTabView.Text.ToString
        Dim o As String = cmbColom.Text.ToString
        
        Dim sCconnect = "Data Source=.\sqlexpress;Initial Catalog=New1;Integrated Security=True;Pooling=False"
        Dim xSqlConnection As SqlConnection = New SqlConnection(sCconnect)

        Dim xSqlCommand As SqlCommand = New SqlCommand("DynamicStoredProc", xSqlConnection)
        xSqlCommand.CommandType = CommandType.StoredProcedure
        xSqlCommand.Parameters.AddWithValue("@Select", str)
        xSqlCommand.Parameters(0).SqlDbType = SqlDbType.VarChar
        xSqlCommand.Parameters.AddWithValue("@Table", s)
        xSqlCommand.Parameters(1).SqlDbType = SqlDbType.VarChar
        xSqlCommand.Parameters.AddWithValue("@Order", o)
        xSqlCommand.Parameters(2).SqlDbType = SqlDbType.VarChar
        xSqlCommand.Connection.Open()
        Dim xSqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(xSqlCommand)
        Dim dt As New DataTable
        xSqlDataAdapter.Fill(dt)
       
 
        dgvData.DataSource = dt

    End Sub

called This Function on Show Button Click.Here I also added Order by Clause to Sort the Data.Structure of Stored Procedure what I used is as

ALTER PROCEDURE dbo.DynamicStoredProc 
	@Select varchar(500) = '',
	 @Table varchar(500) = '',
	 @Order varchar(500)=''
	 
AS
	SET NOCOUNT ON;
	DECLARE @Query varchar(500)
	SET @Query = 'SELECT ' + @Select + ' FROM ' + @Table + ' ORDER BY ' + @Order 
	
	EXEC(@Query) 

RETURN

That's all.And Yes Don't Forget To import System.Text namespace which is needed for StringBuilder Class.