Checkbox, insert for each box checked. VB.NET

ASP Free Forums Sponsor:

September 25th, 2006, 10:03 AM

Rictor

Contributing User

Join Date: Mar 2006

Posts: 324

Time spent in forums: 1 Week 1 Day 16 h 23 m 10 sec
Reputation Power: 213

Checkbox, insert for each box checked. VB.NET

Hey,

I'm wondering if I can get some help with some VB.NET code to go along with my asp.net. My setup is I have a detailsview to insert data into the database. As I have it setup right now, it has a few textbox controls, and a dropdown menu to insert records. Now as it stands, when I add information about an employee odds are it's going to be the same information in the text boxes, the only thing that changes is the dropdown menu. So all the text boxes (on this particular employee) never change, just the dropdown is the variable that will change. THe downside to this is I have to type in the textboxes after each insert again. What'd I"d prefer, is to have say a checkbox with all the options listed, they coudl check how many are applicable, enter the text boxes, and for each checkbox that is checked it enter a new row of data into the database using the text boxes, and a single record for the checkbox.

Now on my first try I got it to semi-work but it's entering all the checkboxes into the same field into the database, instead of creating a new unique entry for each one. I'm extremely lacking on my VB skills, but I'm pretty sure I need to use some code behind to loop through each checkbox that is checked, and insert it. However like I said my vb knowledge sucks currently, so I'm wondering if anybody has done anything similiar and can post some generic example code so I can get an idea of how the framework of this might worK? I tried google, couldn't find anything there - but I"d be more than happy to read an article if anybody knows where one is?

Many thanks in advance,

Dave

September 25th, 2006, 10:55 AM

subbaram_k

M o D e R a T o R

Join Date: Feb 2004

Location: Missouri

Posts: 470

Time spent in forums: 4 Days 6 h 10 m 37 sec
Reputation Power: 38

Are you checkboxes in a CheckBoxList..?

You need to have the insert statement inside the loop.

The code will be looking like this

Code:

Dim ctr As Integer
     For ctr = 0 To chkBoxList.Items.Count - 1
          If chkBoxList.Items(ctr).Selected Then
//Your insert statement should go here. 
          End If
     Next

Basically for each Item seleted you need to do an insert statement. I hope this helps

Comments on this post

D.O.M.I.N.A.T.O.R agrees!

Rictor agrees: Thanks!

__________________
Knowledge is power. Ignorance is no excuse.

Useful Post? Click on Scales

September 25th, 2006, 12:03 PM

Rictor

Contributing User

Join Date: Mar 2006

Posts: 324

Time spent in forums: 1 Week 1 Day 16 h 23 m 10 sec
Reputation Power: 213

Quote:

Originally Posted by subbaram_k

Are you checkboxes in a CheckBoxList..?

You need to have the insert statement inside the loop.

The code will be looking like this

Code:

Dim ctr As Integer
     For ctr = 0 To chkBoxList.Items.Count - 1
          If chkBoxList.Items(ctr).Selected Then
//Your insert statement should go here. 
          End If
     Next

Basically for each Item seleted you need to do an insert statement. I hope this helps

Exaaaactly what I was hoping for, thanks a ton man. You never fail to amaze.

Sorry, wouldn't let me give ya any rep points - but submitted the thanks anyway. Stupid rep timeframe - shouldn't be so long, especially for people like you who answer so many questions.

September 25th, 2006, 03:56 PM

Rictor

Contributing User

Join Date: Mar 2006

Posts: 324

Time spent in forums: 1 Week 1 Day 16 h 23 m 10 sec
Reputation Power: 213

I'm running into a problem here,

I used the template you gave, and implemented it. Now when I ran the insert statement, it indeed inserted a record for each checkbox that was checked. The problem was that instead of inserting the value for the checkbox checked, it'd take the lowest value that was checked, and insert that as the value for all the inserts. (Example, if I had boxes 1,3,4 checked, it'd make 3 records with value 1 be inserted for all my values. If I had 2 and 4 checked, it'd make 2 records, with the value as 2 on both)After doing some digging around, I found out it was because I had PropertyName="SelectedValue" in my ControlParameter statement, however I couldn't find what to put in there in it's stead. I did find this page:

http://msdn2.microsoft.com/en-us/li...ert yname.aspx

That lists what the value for a CheckBox should be, but it doesn't work for a CheckBoxList.

I *think* this is what's wrong, but I might just be attacking this the wrong way. Below is my code for review:

Event Code:

Code:

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim ctr As Integer
            For ctr = 0 To CheckBoxList1.Items.Count - 1
                If CheckBoxList1.Items(ctr).Selected Then
                    SqlDataSource1.Insert()
                End If
            Next
        Catch ex As Exception
            lbl1.Text = "Failed because:" & ex.Message
        End Try
    End Sub

Page Code:

Code:

    <asp:CheckBoxList ID="CheckBoxList1" runat="server">
        <asp:ListItem Value="1">CheckBoxItem1</asp:ListItem>
        <asp:ListItem Value="2">CheckBoxItem2</asp:ListItem>
        <asp:ListItem Value="3">CheckBoxItem3</asp:ListItem>
        <asp:ListItem Value="4">CheckBoxItem4</asp:ListItem>
    </asp:CheckBoxList>&nbsp;
    Textbox:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:REMSQLConnectionString %>"
        InsertCommand="INSERT INTO [testCheckListStore] ([TextBoxColumn], [CheckListColumnTypeID]) VALUES (@TextBoxColumn, @CheckListColumnTypeID)"
        SelectCommand="SELECT [TextBoxColumn], [CheckListColumnTypeID], [TestID] FROM [testCheckListStore]">
        <InsertParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="TextBoxColumn" Type="String" PropertyName="Text" />
            <asp:ControlParameter ControlID="CheckBoxList1" Name="CheckListColumnTypeID" Propertyname="???????" Type="Int32" />
        </InsertParameters>
    </asp:SqlDataSource>
    <br />
    <asp:Button ID="Button1" runat="server" Text="Submit" />
    <br />
    <asp:Label ID="lbl1" runat="server"></asp:Label>

September 25th, 2006, 05:01 PM

subbaram_k

M o D e R a T o R

Join Date: Feb 2004

Location: Missouri

Posts: 470

Time spent in forums: 4 Days 6 h 10 m 37 sec
Reputation Power: 38

Code:

Dim insertCommand As SQLCommand

Try
            Dim ctr As Integer
            Dim str As String
            For ctr = 0 To CheckBoxList1.Items.Count - 1
                If CheckBoxList1.Items(ctr).Selected Then
//                    SqlDataSource1.Insert()

Instead of using a SQLDataSource 
can you use a sql command object 
and insert values 

like this 
str = "Insert into table name (Field1, Field2) values 
('" & TextBox1.Text, CheckBoxList1.Items(ctr).Value & "')"
insertCommand = new SQLCommand(str, objConnection)
insertCommand.ExecuteNonQuery()               

 End If
            Next
        Catch ex As Exception
            lbl1.Text = "Failed because:" & ex.Message
        End Try

It is giving only one value becos we have to take the exact value from the collection like CheckBoxList1.Items(ctr).Value. This will give the exact value of which Item in the Collection is selected and for which we are making an Insert.

Last edited by subbaram_k : September 25th, 2006 at 05:04 PM.

September 26th, 2006, 10:26 AM

Rictor

Contributing User

Join Date: Mar 2006

Posts: 324

Time spent in forums: 1 Week 1 Day 16 h 23 m 10 sec
Reputation Power: 213

Quote:

Originally Posted by subbaram_k

Ahh, I can see why it'd get the one value and stay at that. I tried to stay away from the SQL command object because I know little about them. I started asp during 2.0 so I've been spoiled with datasources.

I am trying to implement it the way you put above, it was giving quite a few errors but with google I think I've got most of them worked out - however I get one error message on the line:

Code:

str = "Insert into CheckListStore (TextBoxColumn, CheckListColumnTypeID) VALUES ('" & TextBox1.text, CheckBoxList1.Items(ctr).Value & "')"

**End of Statement expected

I've been trying to figure out what's wrong for about 2 hours now, and I can't figure it out *embarassed*

I had to add a lot more than what was mentioned above, and maybe that's where I'm going wrong? I apologize, I'm really outside my element when I have to go into this type of stuff. So if you wouldn't mind looking at this again and tell me where I might be doing something wrong I'd appreciate it.

Whole statement:

Code:

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim insertCommand As SQLCommand
        Dim strConnection As String = "Data Source=***\SQLEXPRESS;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***"
        Dim objConnection As New SqlConnection(strConnection)
        Try
            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 CheckListStore (TextBoxColumn, CheckListColumnTypeID) VALUES ('" & TextBox1.text, CheckBoxList1.Items(ctr).Value & "')"
                    insertCommand = New SqlCommand(str, objConnection)
                    insertCommand.ExecuteNonQuery()
                End If
            Next
            objConnection.Close()
        Catch ex As Exception
            lbl1.Text = "Failed because:" & ex.Message
        End Try
    End Sub

September 26th, 2006, 10:47 AM

subbaram_k

M o D e R a T o R

Join Date: Feb 2004

Location: Missouri

Posts: 470

Time spent in forums: 4 Days 6 h 10 m 37 sec
Reputation Power: 38

I think I posted the wrong insert statement.

Change the statement to this

Code:

str = "Insert into CheckListStore (TextBoxColumn, CheckListColumnTypeID) VALUES ('" & TextBox1.text & "," &   CheckBoxList1.Items(ctr).Value & "')"

Basically the string was not well formed. Since there is a Comma we need to break the string and then start.

September 26th, 2006, 11:06 AM

Rictor

Contributing User

Join Date: Mar 2006

Posts: 324

Time spent in forums: 1 Week 1 Day 16 h 23 m 10 sec
Reputation Power: 213

Quote:

Originally Posted by subbaram_k

I think I posted the wrong insert statement.

Change the statement to this

Code:

str = "Insert into CheckListStore (TextBoxColumn, CheckListColumnTypeID) VALUES ('" & TextBox1.text & "," &   CheckBoxList1.Items(ctr).Value & "')"

Basically the string was not well formed. Since there is a Comma we need to break the string and then start.

Bingo. Got rid of that error - now I'm working on one more. This one is actually after I load the page, put in the info and try to click button1 so it's catching it as an exception.

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Just started working on this one, but honestly I can't see how it's grabbing more columns. The code you provided is pretty straight forward. Two columns, Two values.

I'll keep using google. If anything pops out at ya right away let me know

***EDIT:GOT IT!

Forgot to add single quotes to seperate the two values.

Code:

('" & TextBox1.Text & "','" & CheckBoxList1.Items(ctr).Value & "')"

Wow, syntax hell! Lol.

Working perfect now and grabbing the correct values.

Thanks a ton subbaram_k!!

September 26th, 2006, 11:18 AM

subbaram_k

M o D e R a T o R

Join Date: Feb 2004

Location: Missouri

Posts: 470

Time spent in forums: 4 Days 6 h 10 m 37 sec
Reputation Power: 38

Does your table have only two fields..? If there are more, then match the number of fields in the fields section of the query and pass equivalent values to the fields.

After checking your insert statement of the previous post, it appears that you have just two fields in your table

Quote:

="INSERT INTO [testCheckListStore] ([TextBoxColumn], [CheckListColumnTypeID])

Debug the sql statement and see what it is forming like.

#10

September 26th, 2006, 03:50 PM

Rictor

Contributing User

Join Date: Mar 2006

Posts: 324

Time spent in forums: 1 Week 1 Day 16 h 23 m 10 sec
Reputation Power: 213

Got it, as mentioned above. Just some syntax on the quotes. Thanks again for the help, I just implemented it on the real project instead of that fake page/tables I had made to test it out and it's working beautifully there too.

So thanks for not only help troubleshooting but taking the time to explain why things were happening as well, greatly appreciated.

Dave

#11

September 26th, 2006, 04:58 PM

subbaram_k

M o D e R a T o R

Join Date: Feb 2004

Location: Missouri

Posts: 470

Time spent in forums: 4 Days 6 h 10 m 37 sec
Reputation Power: 38

Glad that I was of some help

#12

June 23rd, 2009, 11:06 PM

yuzhen

Registered User

Join Date: Jun 2009

Posts: 1

Time spent in forums: 54 m 56 sec
Reputation Power: 0

Hello,

I would like to ask if my checkbox is in individual instead of CheckBoxList, how do I edit the code in a way that it save all the checked boxes into mySQL table (using visual studio 2005 & Microsoft SQL Server 2005)

Thks =)

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: