checkboxlist insert to sql using sql parameters

Hello All,

 

I am trying to insert a lot of checkboxlist values using VB into a MSSQL table using a stored procedure. I am getting this error
"item In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. System.Data.SqlClient.SqlParameter" I found this error while debugging my application however i do not understand what it is trying to tell me. I have been googling all night and can not find an explanation to what it wants however it seems from the error it wants to reference by index but i do not know how to go about doing that.

 

This is how i coded my stored procedure

 

ALTER PROCEDURE [dbo].[usp_insertquestionnarie]

(@interestname varchar (50))
As

begin
INSERT INTO [a_abacus].[dbo].[joininterest]
Select interestid from appinterests where interestname = @interestname

Here is my vb codebehind code for my checkboxlist

    Protected Sub cmdsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdsubmit.Click

        Dim strConnString As String = WebConfigurationManager.ConnectionStrings("a_abacus").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand("usp_insertquestionnarie", con) 'references connectionstring and calls store procedure
        cmd.CommandType = Data.CommandType.StoredProcedure 'sets default to be storeprocedure




        Dim Item As ListItem
        lblmessage.Text = ""
            For Each Item In chkboxinterest.Items
            If Item.Selected = True Then
                'lblmessage.Text = lblmessage.Text _
                '& Item.Value & "<BR>"
                con.Open()
                cmd.Parameters.AddWithValue("@interestname", Item.Value)
                cmd.ExecuteNonQuery() 'for updating database
                con.Close()
                cmd.Parameters.Clear()
            Else

            End If
        Next
    End Sub

I am new to programming so any help in the right direction an article i could read that would help me get this fixed sample code with explanation as to why would be great also. Thanks for the help

 

 

Hi Jay,

You need to change .AddWithValue part, before that you need to add the parameter without value to the SqlCommand. However, declare it as type SQLDbType.NVarChar, like this:

 

cmd.Parameters.Add("@interestname", SQLDBType.NVarChar)

Then, in your loop, you need to change the value of the SQL Command.

 

cmd.Parameters("@interestname").Value = Item.Value

After that remove 

cmd.Parameters.Clear()

 

and also remove

 

con.Open() and con.Close()

You only need to open the connection before the loop, and close it after the loop.

Also, in your Alter Procedure statement is missing the required AS keyword

 

Best Regards,

Gjorgji

 

 

 

Hello Jay,

This problem usually occurs when working with VB.NET... C# is more clever and will exactly tell you what index causes your problem. Anyway, Gjorgi added some good suggestions that you have to follow (especially the one for open/close connection string that has to be out of your foreach - otherwise this may cause performance issues).

Now, lets get back to your problem...

First, of what type your 'interestname' is? The first possibility this error may happen if it's of Integer type and you are trying to insert this out of the range of the values in your db. If that's true, your code will break on ExecuteNonQuery() with the specified error you showed us. Make sure you validate this before executing the command.

Second, I don't like the way you are executing separate command for each checkbox checked item value. Wouldn't it better to create WHERE clause for each selected item and make something like this: "select column1 from tablenam1 where column1 = 'item1' or column1 = 'item2' or column1 = 'item3'" etc... Of course, don't forget the validation of each item before constructing the where clause. This way you will perform only one command in DB instead of multiple ones.

Hope this helps.

Regards,
Hajan

Hello Hajan,

 

I did not want to create a where clause if i am understanding you correctly because i have about 60 checkboxes so I was trying to find a way to loop through them and for it to add the values to the database.

The above code works offered by Gjorgji accept it adds an extra value to the database because i have textboxes i have to send to the database along with my checkbox values. The updated code is this i took into account Gjorgji suggestions.

 

        con.Open()
        cmd.Parameters.Add("@interestid", SqlDbType.Int)
        Dim Item As ListItem
        For Each Item In chkboxinterest.Items
            If Item.Selected = True Then
                cmd.Parameters("@interestid").Value = CInt(Item.Value)
                cmd.ExecuteNonQuery() 'for updating database    
                'cmd.Parameters.Clear()
            Else

            End If
        Next


        Try

            cmd.ExecuteNonQuery() 'for updating database
            Response.Redirect("ThankYouPage.aspx")
        Catch ex As SqlException
            lblmessage.Text = "Error inserting record."
            lblmessage.Text &= ex.Message
            lblmessage.ForeColor = Drawing.Color.DarkRed
        Finally
            con.Close()
        End Try
    End Sub

With this code it is adding the last checkbox value twice. If i use the cmd.parameters.clear() it clears my other parameters that i have on my code page. How could i set this up where i do not have to run 2 cmd.Executenonquery() or just clear the interestid parameters.

Also right now interestname is nothing. I am just sending the value directly to the database.

 

Thanks for your help

Aham... Now I see your code has changed from the first post. Now You don't have interestname, but interestid, which is not nvarchar but integer, so the situation isn't same.

Anyway, Im not sure if you understood me correctly since with the CLAUSE you will create on fly, depending of the selected item, you will RUN only one command in the database, instead of, lets say 60 commands if all 60 checkboxes are checked. Everytime ExecuteNonquery line runs, the query will be executed in the database, so if you loop 60 times for 60 checked checkboxes, you will have 60 commands running in less than 1 second... Imagine 100 or 1000 users do the same in the same time...

its: [Number of Users] x [Number of checked checkboxes] = 100 x 60 = 6.000 or 1000 x 60 = 60.000 executions in db. In local environment, this may looks very fast, in live environment, especially if your db server is on another machine (multitier architecture), and you run such number of commands for this operation only... seems not soo good.

So, if you can create only one SQL command for the selected checkboxes and even change the stored procedure, this will definitely give at most 60 times faster approach and 60 times less work on your database.

Maybe We can help you in rewritting the stored procedure and the scenario, can you give me little explanation about what is this, what you want to achieve (your ASPX code may tell us) and what is your database tables schema?

Thanks,
Hajan

Thanks for the in detail explanation i understand what you are saying now. The code I came up with was from a lot of searching and also as you can probably tell I am just learning vb. School is out for the summer but i do not want to forget the little bit i did learn when next year class starts.

Little overview on what I am trying to achieve. Basically I have a membership application which contains 3 separate pages with a lot of fields. Combine all 3 pages and i would say there are about 200 fields thats including checkboxes. Once the member fills out the first application form which is "MembershipApplicant.aspx"(which holds name, address,ssn,dob...so on) they would then click submit and it would insert what they filled out to the sql tables and then would transfer them to the "PersonalQuestionaire.aspx" which they would then have to fill that out there is another page i have not started it yet as I have been struggling to get this to work. Since i have 2 separte SQL stored procedures to insert the 2 different forms I am also having trouble with keeping the same "PAPPID" consistent. I will post snippets of the code as it is long but it should give you the general idea of how it is layed out just a lot more textboxes and parameters.

 

MSSQL Table_Schema

http://imageshack.us/photo/my-images/97/tableschema.jpg/

 

<form id="mainform" runat="server">
    <div class="wrapper">
    <div id="header">
    APPLICATION FOR MEMBERSHIP
    </div>
    <div id="orclogo">
        <asp:Image ID="Image1" runat="server" Height="145px" 
            ImageUrl="~/Images/ORClogo_color.jpg" Width="278px" />
    </div>
    <br />
   
    <div id="content">
        <br />
        <asp:Label ID="lblapplicantname" runat="server" CssClass="labels" Text="Applicant's Name:">
        </asp:Label>
        <asp:TextBox ID="txtapplicantfname" CssClass="txtapplicantfname"  runat="server"></asp:TextBox>
        <asp:TextBox ID="txtapplicantmname" CssClass="txtapplicantmname"  runat="server"></asp:TextBox>
        <asp:TextBox  ID="txtapplicantlname" CssClass="txtapplicantlname"  runat="server"></asp:TextBox>
                 (lots more textboxes and radiobutons)


CODE BEHIND FOR THIS PAGE


 Protected Sub cmdmemapp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdmemapp.Click

        Dim strConnString As String = WebConfigurationManager.ConnectionStrings("a_abacus").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand("usp_insertmemapp", con) 'references connectionstring and calls store procedure
        cmd.CommandType = Data.CommandType.StoredProcedure 'sets default to be storeprocedure
  


        cmd.Parameters.Add("@pappfname", Data.SqlDbType.NVarChar).Value = txtapplicantfname.Text
        cmd.Parameters.Add("@pappmname", Data.SqlDbType.NVarChar).Value = txtapplicantmname.Text
        cmd.Parameters.Add("@papplname", Data.SqlDbType.NVarChar).Value = txtapplicantlname.Text
        cmd.Parameters.Add("@pappwedding", Data.SqlDbType.Date).Value = txtwedding.Text
        cmd.Parameters.Add("@pappstateresidence", Data.SqlDbType.NVarChar).Value = txtstateofresidence.Text
        cmd.Parameters.Add("@pappstreet", Data.SqlDbType.NVarChar).Value = txtprincipalresstreet.Text
(LOTS MORE PARAMETERS HERE)


 Try
            con.Open()
            cmd.ExecuteNonQuery() 'for updating database
            Response.Redirect("PersonalQuestionaire.aspx")
        Catch ex As SqlException
            lblMessage.Text = "Error inserting record."
            lblMessage.Text &= ex.Message
            lblMessage.ForeColor = Drawing.Color.DarkRed
        Finally
            con.Close()

        End Try

STORED STOREDPROCEDURE FOR MembershipApplicant

ALTER Procedure [dbo].[usp_insertmemapp](
@pappfname nvarchar(50),
@pappmname nvarchar(50),
@papplname nvarchar(50),
@pappwedding nvarchar(50),
(....lots more parameters here)
)
AS

Begin Transaction
BEGIN TRY

DECLARE @pappid int 
IF EXISTS (SELECT @pappid FROM [a_abacus].[dbo].[primaryapplicant]WHERE pappssn = @pappssn)
BEGIN
Select @pappid = (SELECT Top 1 pappid FROM [a_abacus].[dbo].[primaryapplicant]WHERE pappssn = @pappssn)
Update [a_abacus].[dbo].[primaryapplicant]
SET
pappfname = @pappfname,pappmname = @pappmname,papplname = @papplname,pappwedding = @pappwedding,.......Where pappid = @pappid

UPDATE [a_abacus].[dbo].[spouseapplicant]
SET 
sappfname = @sappfname,sappmname = @sappmname,sapplname = @sapplname, sappssn = @sappssn,sappdob = @sappdob Where pappid = @pappid,

End
Else 

Begin
INSERT INTO [a_abacus].[dbo].[primaryapplicant]
VALUES(
@pappfname,@pappmname,@papplname,@pappwedding,@pappstateresidence,@pappstreet,@pappcity,@pappstate,
@pappzip,@papphomephone,@pappfax,@pappaltstreet,@pappaltcity,@pappaltstate)
SET @pappid = SCOPE_IDENTITY()
(couple more insert statements but same concept)

END
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Commit Transaction

Personalquestionaire.aspx

<head runat="server">
    <title></title>
    <link href="Styles/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div class="wrapper">
    <div id="header">
    PERSONAL QUESTIONNAIRE
    </div>
    <div id="orclogo">
        <asp:Image ID="Image1" runat="server" Height="120px" 
            ImageUrl="~/Images/ORClogo_color.jpg" Width="250px" />
    </div>
    <br />
    <div id="content">
    <div id="applicantsname">
        <asp:Label ID="Label1" CssClass="labels" runat="server" Text="Applicant's Name:"></asp:Label> 
        <asp:TextBox ID="pqapplicantfname" CssClass="textbox" runat="server"></asp:TextBox>
         <asp:TextBox ID="pqapplicantlname" CssClass="textbox" runat="server"></asp:TextBox>
(lot more textboxes)

<asp:CheckBoxList ID="chkboxinterest" runat="server" RepeatColumns="3" 
            Width="650px">
       <asp:ListItem class="chkbox" runat="server" Text="Aerobics" Value="1" />
       <asp:ListItem class="chkbox" runat="server" Text="Antiques & Collectibles" Value="2" />
       <asp:ListItem class="chkbox" runat="server" Text="Arts & Crafts" Value="3" />
       <asp:ListItem class="chkbox" runat="server" Text="Astronomy" Value="4" />
       <asp:ListItem class="chkbox" runat="server" Text="Auto Racing" Value="5" />
       <asp:ListItem class="chkbox" runat="server" Text="Bicycling" Value="6" />
       <asp:ListItem class="chkbox" runat="server" Text="Billiards" Value="7" />
       <asp:ListItem class="chkbox" runat="server" Text="Board Games" Value="8" />
(There are about 54 more checkboxes same concept though)

CODE BEHIND PERSONALQUESTIONAIRE.VB

Protected Sub cmdsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdsubmit.Click

        Dim strConnString As String = WebConfigurationManager.ConnectionStrings("a_abacus").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand("usp_insertquestionnarie", con) 'references connectionstring and calls store procedure
        cmd.CommandType = Data.CommandType.StoredProcedure 'sets default to be storeprocedure

        cmd.Parameters.Add("@depfname", Data.SqlDbType.NVarChar).Value = pqfname.Text
        cmd.Parameters.Add("@deplname", Data.SqlDbType.NVarChar).Value = pqlname.Text
        cmd.Parameters.Add("@depsex", Data.SqlDbType.NVarChar).Value = Convert.IsDBNull(rbpqsex.Text)
        cmd.Parameters.Add("@depdob", Data.SqlDbType.Date).Value = pqdob.Text 
  
con.Open()
        cmd.Parameters.Add("@interestid", SqlDbType.Int)
        Dim Item As ListItem
        lblmessage.Text = ""
        For Each Item In chkboxinterest.Items
            If Item.Selected = True Then
                cmd.Parameters("@interestid").Value = CInt(Item.Value)
                cmd.ExecuteNonQuery() 'for updating database    
                'cmd.Parameters.Clear()
            Else
            End If
        Next
        Try
            cmd.ExecuteNonQuery() 'for updating database
            lblmessage.Text = "Group Info was Inputed Successfully"
            Response.Redirect("ThankYouPage.aspx")
        Catch ex As SqlException
            lblmessage.Text = "Error inserting record."
            lblmessage.Text &= ex.Message
            lblmessage.ForeColor = Drawing.Color.DarkRed
        Finally
            con.Close()
        End Try
    End Sub

StoredProcedure for PersonalQuestionaire.vb

ALTER PROCEDURE [dbo].[usp_insertquestionnarie] (
I declared about 20 variables here including these 2 @otherinterest varchar(200),
@interestid int)


As


begin
Declare @pappid int
Declare @depid int
IF EXISTS (SELECT @pappid FROM [a_abacus].[dbo].[primaryapplicant]WHERE pappid = @pappid)
INSERT INTO [a_abacus].[dbo].[appquestionnaire]
Values
(@depfname,@deplname,@depsex,@depdob,@depchargepriv,@depschool,
@depemail,@depmarried,@depfname1,@deplname1,@depsex1,
@depdob1,@depchargepriv1,@depschool1,@depemail1,@depmarried1,
@depfname2,@deplname2,@depsex2,@depdob2,@depchargepriv2,
@depschool2,@depemail2,@depmarried2,@depfname3,@deplname3,
@depsex3,@depdob3,@depchargepriv3,@depschool3,@depemail3,
@depmarried3,@depfname4,@deplname4,@depsex4,@depdob4,@depchargepriv4,
@depschool4,@depemail4,@depmarried4,@depfname5,@deplname5,
@depsex5,@depdob5,@depchargepriv5,@depschool5,@depemail5,
@depmarried5,@depotherinfo,@ownboat,@boatname,@boattype,
@boatlength,@boatbeam,@ownairplane,@airplanetype,@airplanetailnum,
@playgolf,@handicaps,@winepref,@winetype,@otherinterest,@pappid)
SET @depid = SCOPE_IDENTITY()
End

Begin
IF EXISTS (SELECT @pappid FROM [a_abacus].[dbo].[primaryapplicant]WHERE pappid = @pappid)****i tried this trying to get the pappid from the primaryapplicant table which did not work cause it does not know what pappid is*******

INSERT INTO [a_abacus].[dbo].[joininterest]
values(@interestid,@pappid)
end

 

also the pappid=scope_identity() that got created when the first stored procedure was executed I am not sure how to get that to pass to this form since it is 2 different stored procedures it needs to be one record.

Also i really appreciate your help hopefully one day I will learn this so i can return the favor to the next person that starts learning this like me. And I do read books and search for the answer a lot however sometimes i just confuse myself more because there are so many ways to approach something that i start getting off track.

Thanks Again

[CodeASP.NET Administrator]
Please do not modify your previous posts. This makes the conversation not understandable and desynchronized.

Hi Jay,

I see your code has changed a lot from the first post to the last one with all the code snippets you've sent us. This can easily misslead the helper in the forums, so Im not yet sure which is your last version.

Anyway, you don't need to add the checked checkboxes into an array. You just need to create SQL query string inside the foreach cycle.

Let me give you one simple example, so that you can make your own with your own logic.

Lets say I have the checkbox list as in your example and I want to make insert query which will add all the values similar as in your Stored Procedure example on the first blog post.

Here is the complete code with comments

        'you can add the whole query into Stored Procedure and add @whereClause stored procedure parameter
        'which will accept the where clause built dynamically in code-behind and will append it to the stored procedure
        'however, you can use it like this as well ;)
        Dim procedureSqlCommand As String = "INSERT INTO [a_abacus].[dbo].[joininterest] SELECT interestid from appinterests " 'where clause will follow

        Dim whereClause As String = "WHERE interestname IN (" 'we will use interestname IN (item1, item2, item3)'
        Dim numOfSelectedItems As Integer = 0

        For Each item As ListItem In chkboxinterest.Items
            If item.Selected Then
                whereClause += "'" & item.Text & "', "

                'Im setting this so if its 0 after the foreach
                'I won't execute the command at all
                numOfSelectedItems = numOfSelectedItems + 1

            End If
        Next

        whereClause += " '')"

        If (numOfSelectedItems > 0) Then
            'appending whereClause to procedureSqlCommand
            'Im making this separated to make the code more clean and more readable
            procedureSqlCommand = procedureSqlCommand + whereClause

            Dim strConnString As String = WebConfigurationManager.ConnectionStrings("a_abacus").ConnectionString
            Dim con As New SqlConnection(strConnString)
            Dim cmd As New SqlCommand(procedureSqlCommand, con) 'references connectionstring and calls the procedureSqlCommand

            'By using the Sql Connection
            Using con
                con.Open() 'Open the connection
                Dim result As Integer = cmd.ExecuteNonQuery() 'Execute it and put the result in result Integer
                If result <> -1 Then 'If result is not -1, then the command has completed successfully
                    Response.Write("Successfully!") 'Print the success message
                End If
            End Using

        End If

Read it carefully and you will understand it ;)

For example, If I have checked: Aerobics, Antiques & Collectibles, Billards and Board Games, the Sql Query for execution generated by the code will be:

INSERT INTO [a_abacus].[dbo].[joininterest] SELECT interestid from appinterests WHERE interestname IN ('Aerobics', 'Antiques & Collectibles', 'Billiards', 'Board Games',  '')

Hope this helps,
Hajan

Thanks for this I understand what you are doing finally all of your comments really help and i appreicate it as I would have never thought of approaching it this way. Also the code snippet i showed you was not me modifying the original code I was simply trying it on a test page to see if I could get it to work that way, The orginal code is still the same sorry for the confusion.

 

I do have a simple question since I have other parameters that do not involve the checkboxes that need to get submitted is it good practice to run the

cmd.ExecuteNonQuery()

twice on one form? Once to see if there are any checkboxes and another to insert the dependents.

 

Last as this is the second form. What approach would you recommend to retreive the pappid that was inserted after the first form was submitted?  The first form has a pappid= @@Scope_identity() in the insert stored procedure.

Thanks again. I will try to not get off track and throw more code out there I just wanted to attempt it myself to see if I could get it before you helped me. Then i was going to compare what i did with yours to see if it was any where near the same :) which was way off...

 

Jay Akhtar said:

 

Thanks for this I understand what you are doing finally all of your comments really help and i appreicate it as I would have never thought of approaching it this way. Also the code snippet i showed you was not me modifying the original code I was simply trying it on a test page to see if I could get it to work that way, The orginal code is still the same sorry for the confusion.

 

I do have a simple question since I have other parameters that do not involve the checkboxes that need to get submitted is it good practice to run the

 

cmd.ExecuteNonQuery()

 

twice on one form? Once to see if there are any checkboxes and another to insert the dependents.

 

Yes, you can run ExecuteNonQuery() multiple times all until you have different SQL queries that you need to perform in your DB from your application.

 

Jay Akhtar said:

 

Last as this is the second form. What approach would you recommend to retreive the pappid that was inserted after the first form was submitted?  The first form has a pappid= @@Scope_identity() in the insert stored procedure.

Thanks again. I will try to not get off track and throw more code out there I just wanted to attempt it myself to see if I could get it before you helped me. Then i was going to compare what i did with yours to see if it was any where near the same :) which was way off...

 

To get the latest inserted id, you can use ExecuteScalar

 

                If result <> -1 Then
                    cmd.CommandText = "select scope_identity()"
                    Dim identityVal = cmd.ExecuteScalar()
                End If

Hope this helps.

 

Hello Hajan,

 

Really appreciate your help is there any way that i could donate a little money towards this website show a little appreciation im a student so im not rich but you did not have to help me as much as you did and i really appreciate it.

 

Thanks