looping throught asp.net checkbox list

Dr.Silly

Hi,

Am stuck on a problem,

I have a checkbox list where the user can check multiple options.
I want to store these in the database, on separate rows in one column.
I don't want to have to store them in one row like chk1;chk2;chk3.

I have the following but it's say object not referenced on the second line

For Each item As ListItem In CheckBoxList1.Items
If item.Selected Then
DS_CandidateSuitableRoles.InsertParameters("@Suitable_Roles_ID&quot.DefaultValue = item.Value
DS_CandidateSuitableRoles.Insert()
End If
Next


If I take out the loop and just to DS_CandidateSuitableRoles.Insert, it works, but obviously only inserts one checked item.

Dr.Silly

Sorry, I meant to say, I do want them to be split out into one column, with a separator.

so if three items are checked, I need to have
chk1;chk2;chk3

mewso

Dim sb as new System.Text.Stringbuilder
For Each item As ListItem In CheckBoxList1.Items
If item.Selected Then
   sb.append(item.Value & ";")
End If
Next

DS_CandidateSuitableRoles.InsertParameters("@Suitable_Roles_ID").DefaultValue = sb.tostring.trim(";")
DS_CandidateSuitableRoles.Insert()

edit - I'm using a stringbuilder simply because it's more efficient than using a string

Dr.Silly

mewso wrote: »

Dim sb as new System.Text.Stringbuilder
For Each item As ListItem In CheckBoxList1.Items
If item.Selected Then
   sb.append(item.Value & ";")
End If
Next

DS_CandidateSuitableRoles.InsertParameters("@Suitable_Roles_ID").DefaultValue = sb.tostring.trim(";")
DS_CandidateSuitableRoles.Insert()

edit - I'm using a stringbuilder simply because it's more efficient than using a string

Thanks for that.

I tried it and am now getting an error on the following line.

DS_CandidateSuitableRoles.InsertParameters("@Suitable_Roles_ID&quot.DefaultValue = sb.tostring.trim(";")


NullReference exception was unhandled
Object reference not set to an instance of an object.

mewso

Dr.Silly wrote: »

Thanks for that.

I tried it and am now getting an error on the following line.

DS_CandidateSuitableRoles.InsertParameters("@Suitable_Roles_ID&quot.DefaultValue = sb.tostring.trim(";")


NullReference exception was unhandled
Object reference not set to an instance of an object.

I just noticed that line is best as:-

DS_CandidateSuitableRoles.InsertParameters("@Suitable_Roles_ID&quot.DefaultValue = sb.tostring.trim(";"c)

but is unlikely to be the problem. Something on that line is = nothing. Are you creating the stringbuilder before the loop begins?

Dr.Silly

mewso wrote: »

I just noticed that line is best as:-

DS_CandidateSuitableRoles.InsertParameters("@Suitable_Roles_ID&quot.DefaultValue = sb.tostring.trim(";"c)

but is unlikely to be the problem. Something on that line is = nothing. Are you creating the stringbuilder before the loop begins?

Hi,
This is my complete code:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click


Dim sb As New System.Text.StringBuilder
For Each item As ListItem In CheckBoxList1.Items
If item.Selected Then
sb.append(item.Value & ";")
End If
Next

DS_CandidateSuitableRoles.InsertParameters("@Suitable_Roles_ID&quot.DefaultValue = sb.ToString.Trim(";")

DS_CandidateSuitableRoles.Insert()
End Sub

mewso

Hmm looks ok to me. Try breaking on that line and hovering over the objects to see which one is null.

Dr.Silly

ok, now heads wrecked :-)

I've changed it to use a stored procedure cause I thought it may have had something to do with the sqldatasource and multiple paramaters I was using.

Here it is again, complete code: ... It's working now, but it's only entering the first selected check box.
But even if I do my stored procedure

exec s_insertCandidates 1,'1;2;3','test'

it's only inserting 1,1,test into the database, (The second column is a varchar).


Dim sb As New System.Text.StringBuilder
For Each item As ListItem In CheckBoxList1.Items
If item.Selected Then
sb.append(item.Value & ";")
End If
Next
Dim myconn As New SqlConnection
myconn = New SqlConnection("server=(local); database= skillsDB;user id=sa;password=xxx")
Dim cmd As New SqlCommand
cmd = New SqlCommand("s_insertCandidates", myconn)
cmd.CommandType = CommandType.StoredProcedure
myconn.Open()
cmd.Parameters.Add("@Candidate_ID", SqlDbType.Int).Value = Session("Candidate_ID")
cmd.Parameters.Add("@Suitable_Roles_ID", SqlDbType.VarChar).Value = sb.ToString.Trim(";")
cmd.Parameters.Add("@notes", SqlDbType.VarChar).Value = txtSuitableNotes.Text
cmd.ExecuteNonQuery()
myconn.Close()

mewso

Try a comma instead of ; since ; is a statement terminator in sql it might be having some strange effect.

Dr.Silly

tried that also but didn't work,
ended up getting it working this way, thank you for your help also.

Dim insertCommand As SqlCommand
Dim strConnection As String = "Data Source=(local);Initial Catalog=skillsdb;Persist Security Info=True;User ID=sa;Password=xxx"
Dim objConnection As New SqlConnection(strConnection)

objConnection.Open()
Dim ctr As Integer
Dim str As String
For ctr = 0 To CheckBoxList1.Items.Count - 1
If CheckBoxList1.Items(ctr).Selected Then
str = "Insert into candidate_suitable_rolesTBL (candidate_id,suitable_roles_id) VALUES ('" & Session("Candidate_ID") & "','" & CheckBoxList1.Items(ctr).Value & "')"
insertCommand = New SqlCommand(str, objConnection)
insertCommand.ExecuteNonQuery()
End If
Next
objConnection.Close()
objConnection.Dispose()
objConnection = Nothing