title: Looping through ListBox to add multiple selected values to a single database field
body: <p>I am trying to add the multiple selected values from the ListBox into the Database. Multiple test names are to be selected for each patient (depending upon the requirements) and they are to be inserted into the column named <code>TestName</code>.</p>
  
  <p>For example: If I select <code>ANA, CBC</code> for a patient, it should add BOTH values in the TESTNAME column against that patient name. 
  I have tried a few things suggested on the internet, but I have been unsuccessful. I am posting my related ASP.NET code along with C# code (my latest try). </p>
  
  <p>With the latest try, it throws an error saying: </p>
  
  <blockquote>
    <p>The variable name '@TestName' has already been declared. Variable names must be unique within a query batch or stored procedure.</p>
  </blockquote>
  
  <p>ASP.NET code for the gridview:</p>
  
  <pre><code>&lt;div id="mbody"&gt;
      &lt;div class="gview"&gt;
          &lt;asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CssClass="gview" DataSourceID="SqlDataSource1" DataKeyNames="PID"&gt;
              &lt;Columns&gt;
                  &lt;asp:CommandField ShowDeleteButton="True" /&gt;
                  &lt;asp:BoundField DataField="PID" HeaderText="PID" InsertVisible="False" ReadOnly="True" SortExpression="PID" /&gt;
                  &lt;asp:BoundField DataField="Pname" HeaderText="Pname" SortExpression="Pname" /&gt;
                  &lt;asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" /&gt;
                  &lt;asp:BoundField DataField="Consultant" HeaderText="Consultant" SortExpression="Consultant" /&gt;
                  &lt;asp:BoundField DataField="TestName" HeaderText="TestName" SortExpression="TestName" /&gt;
                  &lt;asp:BoundField DataField="RequestDate" HeaderText="RequestDate" SortExpression="RequestDate" /&gt;
                  &lt;asp:BoundField DataField="ReportDate" HeaderText="ReportDate" SortExpression="ReportDate" /&gt;
              &lt;/Columns&gt;
  
          &lt;/asp:GridView&gt;
          &lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="&lt;%$ ConnectionStrings:SMCConnectionString %&gt;" SelectCommand="SELECT [PID], [Pname], [Gender], [Consultant], [TestName], [RequestDate], [ReportDate] FROM [Patient]" DeleteCommand="DELETE FROM [Patient] WHERE [PID] = @PID" InsertCommand="INSERT INTO [Patient] ([Pname], [Gender], [Consultant], [TestName], [RequestDate], [ReportDate]) VALUES (@Pname, @Gender, @Consultant, @TestName, @RequestDate, @ReportDate)" UpdateCommand="UPDATE [Patient] SET [Pname] = @Pname, [Gender] = @Gender, [Consultant] = @Consultant, [TestName] = @TestName, [RequestDate] = @RequestDate, [ReportDate] = @ReportDate WHERE [PID] = @PID"&gt;
              &lt;DeleteParameters&gt;
                  &lt;asp:Parameter Name="PID" Type="Int32" /&gt;
              &lt;/DeleteParameters&gt;
              &lt;InsertParameters&gt;
                  &lt;asp:Parameter Name="Pname" Type="String" /&gt;
                  &lt;asp:Parameter Name="Gender" Type="String" /&gt;
                  &lt;asp:Parameter Name="Consultant" Type="String" /&gt;
                  &lt;asp:Parameter Name="TestName" Type="String" /&gt;
                  &lt;asp:Parameter Name="RequestDate" Type="String" /&gt;
                  &lt;asp:Parameter Name="ReportDate" Type="String" /&gt;
              &lt;/InsertParameters&gt;
              &lt;UpdateParameters&gt;
                  &lt;asp:Parameter Name="Pname" Type="String" /&gt;
                  &lt;asp:Parameter Name="Gender" Type="String" /&gt;
                  &lt;asp:Parameter Name="Consultant" Type="String" /&gt;
                  &lt;asp:Parameter Name="TestName" Type="String" /&gt;
                  &lt;asp:Parameter Name="RequestDate" Type="String" /&gt;
                  &lt;asp:Parameter Name="ReportDate" Type="String" /&gt;
                  &lt;asp:Parameter Name="PID" Type="Int32" /&gt;
              &lt;/UpdateParameters&gt;
          &lt;/asp:SqlDataSource&gt;
      &lt;/div&gt;
  &lt;/div&gt;
  </code></pre>
  
  <p>ListBox.aspx:</p>
  
  <pre><code>&lt;asp:ListBox ID="ListBox1" runat="server" SelectionMode="Multiple" Height="57px" Width="270px"&gt;
      &lt;asp:ListItem&gt;ANA&lt;/asp:ListItem&gt;
      &lt;asp:ListItem&gt;ASMA&lt;/asp:ListItem&gt;
      &lt;asp:ListItem&gt;ASO-titres&lt;/asp:ListItem&gt;
      &lt;asp:ListItem&gt;ESR&lt;/asp:ListItem&gt;
      &lt;asp:ListItem&gt;CBC&lt;/asp:ListItem&gt;
      &lt;asp:ListItem&gt;Anti-double Stranded DNA ab&lt;/asp:ListItem&gt;
  &lt;/asp:ListBox&gt;
  </code></pre>
  
  <p>ListBox.aspx.cs:</p>
  
  <pre><code>protected void Button3_Click(object sender, EventArgs e)
      {
          string str = "update Patient set TestName=@TestName, RequestDate=@RequestDate ,ReportDate=@ReportDate, Consultant=@Consultant where PID = '" + TextBox7.Text + "'";
          cmd = new SqlCommand(str, con);
          foreach (ListItem li in ListBox1.Items)
          {
              if (li.Selected)
              {
                  cmd.Parameters.AddWithValue("@TestName", ListBox1.SelectedItem.Text);
              }
          }
          cmd.Parameters.AddWithValue("@RequestDate", TextBox4.Text.ToString());
          cmd.Parameters.AddWithValue("@ReportDate", TextBox5.Text.ToString());
          cmd.Parameters.AddWithValue("@Consultant", TextBox6.Text);
          con.Open();
          int flag = cmd.ExecuteNonQuery();
          if (flag == 1)    //On successful updation, shows a popup message
          {
              string msg = "Operation Successful";
              System.Text.StringBuilder sb = new System.Text.StringBuilder();
              sb.Append("&lt;script type = 'text/javascript'&gt;");
              sb.Append("window.onload=function(){");
              sb.Append("alert('");
              sb.Append(msg);
              sb.Append("')};");
              sb.Append("&lt;/script&gt;");
              ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", sb.ToString());
          }
          else if (flag == 0)
          {
              string msg1 = "Operation Unsuccessful";
              System.Text.StringBuilder sb1 = new System.Text.StringBuilder();
              sb1.Append("&lt;script type = 'text/javascript'&gt;");
              sb1.Append("window.onload=function(){");
              sb1.Append("alert('");
              sb1.Append(msg1);
              sb1.Append("')};");
              sb1.Append("&lt;/script&gt;");
              ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", sb1.ToString());
          }
          con.Close();
      }
  </code></pre>
  
  <p>Maybe I need to append or concatenate or something, but I am not sure how to do that.</p>