Data doesnt appear on CheckBoxList without any error

Question

0.00/5 (No votes)

See more:

hello programmers, got a problem here. As you see ive created a dropdownlist where the data of the checkbox varies. The problem is when a choose a text from the dropdownlist, the data of the checkboxlist doesnt appear. There is no error indicating what should I do. Plss help me
Here is my code

VB

Dim strSQL As String
    Dim connection As SqlConnection = New SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
    strSQL = "Select CourseTitle from [tblTrainingPlan] WHERE([Category] = @Category)"
    connection.Open()
    Dim command As New SqlCommand(strSQL, connection)
    command.Parameters.AddWithValue("@Category", ddcategory.Text)
    cbTitle.DataSource = command.ExecuteReader
    cbTitle.DataTextField = "CourseTitle"
    cbTitle.DataValueField = "CourseTitle"
    cbTitle.DataBind()
End Sub

Thanks and more power.

Posted 15-May-11 14:32pm

janwel

Add a Solution

3 solutions

Solution 2

Sir an error shows up saying
There is already an open DataReader associated with this Command which must be closed first. pointing at cbTitle.datasource = command.executereader
Is my syntax correct?
here is my code sir

VB

Dim strSQL As String
Dim connection As SqlConnection = New SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
strSQL = "Select CourseTitle from [tblTrainingPlan]"
connection.Open()
Dim command As New SqlCommand(strSQL, connection)
command.Parameters.AddWithValue("@Category", ddcategory.Text)
Dim DataReader As SqlClient.SqlDataReader = command.ExecuteReader()
Do While DataReader.Read()
    cbTitle.DataSource = command.ExecuteReader
    cbTitle.DataTextField = "CourseTitle"
    cbTitle.DataValueField = "CourseTitle"
    cbTitle.DataBind()
Loop
command.Dispose()
connection.Close()

Posted 15-May-11 15:00pm

janwel

Comments

saxenaabhi6 15-May-11 21:13pm

dont execute the reader after entering the while block, remove that line.

walterhevedeich 15-May-11 21:13pm

See my updates on my answer.

janwel 15-May-11 21:17pm

sir forgive my newbie symptom but if I remove that reader is it possible to bind it with checkboxtlist which is cbTitle. But sir i really appreciate for helping hand ^^

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)

walterhevedeich · Accepted Answer · 2011-05-15T14:48:00

You need to do something like below if you are implementing it using ExecuteReader

VB

Dim reader As SqlDataReader
reader = cmd.ExecuteReader()
While reader.Read()
     'code to collect the data from the reader before setting the datasource
End While
'code to set the data source

[UPDATE]
You may want to try this code out. Its not an elegant code and there are more elegant ways to do this but its pretty readable for begginers who are learning ADO.Net.

VB

Dim strSQL As String
Dim connection As SqlConnection = New SqlConnection("Data Source=ML0003135586;Initial Catalog=TestSQL;Integrated Security=True")
strSQL = "Select CourseTitle from [tblTrainingPlan]"
connection.Open()
Dim command As New SqlCommand(strSQL, connection)
command.Parameters.AddWithValue("@Category", ddcategory.Text)
Dim DataReader As SqlClient.SqlDataReader = command.ExecuteReader()
Dim DatSource As New System.Data.DataTable
Dim column As DataColumn
Dim row As DataRow

column = New DataColumn
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "CourseTitle"
DatSource.Columns.Add(column)

Do While DataReader.Read()
   row = DatSource.NewRow
   row("CourseTitle") = DataReader("CourseTitle")
   DatSource.Rows.Add(row)
Loop

cbTitle.DataSource = DatSource
cbTitle.DataTextField = "CourseTitle"
cbTitle.DataValueField = "CourseTitle"
cbTitle.DataBind()
command.Dispose()
connection.Close()

saxenaabhi6 · Accepted Answer · 2011-05-15T15:08:00

Solution 3

for your case try this

<br />
while(dr.read())<br />
 <br />
{<br />
 <br />
dropdownlist.items.add(new listitem(DataReader["CourseTitle"], DataReader["CourseTitle"]);<br />
 <br />
} <br />
<br />

do it in vb

I would suggest you to get your sql query results back in data sets and bind that to datasource.
Data readers are generally to loop thru the data records.

Posted 15-May-11 15:08pm

saxenaabhi6

Updated 15-May-11 15:11pm

v2

Comments

janwel 15-May-11 21:25pm

sir forgive my newbie symptoms but ive change it to vb as you say. here is my code now

Do While DataReader.Read() 
cbTitle.items.add(new listitem(Datareader["CourseTitle"], DataReader["CourseTitle"]); 
Loop

but still an error shows up on DataReader[CourseTitle]

saxenaabhi6 15-May-11 21:32pm

My code is in c# you have to do that in vb (i think its not completely in vb yet), refer to walterhevedeich code, he created a datasource and looping thru the data reader to add data rows in it.
You got lot of options to load your drop down:
1) Get sql result in data set and bind your dropdown datasource to it.
2) Get sql result in data reader, loop thru the reader and fill your drop down list items.
3) Create a data source, loop thru reader and load data source and then bind it to your dropdown.
4) etc...

Please read more on datasets, data table, data readers and try diff examples on msdn...

janwel 15-May-11 21:40pm

sir how about this one?
cbTitle.Items.Add(New ListItem(DataReader("CourseTitle").ToString))

Sir trying to learn ASP.net so im eager to create something with the help of othr. Thanks in many aspect sir

saxenaabhi6 15-May-11 21:53pm

try this
cbTitle.Items.Add(New ListItem(DataReader("CourseTitle").ToString()))

janwel 15-May-11 21:56pm

sir cbTitle is a checkboxlist

saxenaabhi6 15-May-11 21:59pm

ya my bad, but still CheckBoxList, DropDownList, RadioButtonList follow same implimentations. if it works for one it works for all

janwel 15-May-11 21:59pm

sir is there any links toward tutorial?

saxenaabhi6 15-May-11 22:03pm

as i said use data set and make your life easier
click here