Store multiple values in database where checked

Question

I have a checkboxlist.

<asp:CheckBoxList ID="cbBowelSounds" runat="server">
    <asp:ListItem Text="&nbsp;&nbsp;<span style=font-weight:normal;>1 Quadrant</span>" Value="1 Quadrant" />
    <asp:ListItem Text="&nbsp;&nbsp;<span style=font-weight:normal;>2 Quadrant</span>" Value="2 Quadrant" />
    <asp:ListItem Text="&nbsp;&nbsp;<span style=font-weight:normal;>Hypo</span>" Value="Hypo" />
    <asp:ListItem Text="&nbsp;&nbsp;<span style=font-weight:normal;>Hyper</span>" Value="Hyper" />
    <asp:ListItem Text="&nbsp;&nbsp;<span style=font-weight:normal;>Normal</span>" Value="Normal" />
</asp:CheckBoxList>

You can select multiple values. I want to store those values in a SQL Server database.

At first I thought I would create 5 columns in the SQL Server table and I would add each selected value to the correct column. That didn't work.

Then I found this snippet:

var s = cbBowelSounds.SelectedValue;
string[] values = s.Split(',');

foreach (ListItem item in cbBowelSounds.Items) 
     item.Selected = values.Contains(item.Value);

That was great... until the database reported this for the answer: System.String[]

So, what can I do here? whether it's one column with multiple values or 5 columns where the first listitem writes to column 1, etc., It doesn't matter me. I just need the values selected to be sent to the DB.

Thank you

I'm using this for the SQL code

  private void InsertPatientNote()
  {
     var patId = txtPatId.Text;
     string selectedValue = rbVitalsTaken.SelectedValue;

     var s = cbBowelSounds.SelectedValue;
     string[] values = s.Split(',');
     bool quadOne = values.Contains("1 Quadrant");
     bool quadTwo = values.Contains("2 Quadrant");
     bool hypo = values.Contains("Hypo");
     bool hyper = values.Contains("Hyper");
     bool normal = values.Contains("Normal");

     var strConnString = ConfigurationManager.ConnectionStrings["HBMConnectionString"].ConnectionString;

     SqlConnection con = new SqlConnection(strConnString);

     SqlCommand command = new SqlCommand();
     command.CommandType = CommandType.StoredProcedure;
     command.CommandText = "uspInsertPatientNote";

     command.Parameters.Add("@patId", SqlDbType.VarChar).Value = patId;
     command.Parameters.Add("@rnId", SqlDbType.VarChar).Value = txtNurseId.Text.Trim();
     command.Parameters.Add("@PhysName", SqlDbType.VarChar).Value = txtPhysName.Text.Trim();
     command.Parameters.Add("@visitDate", SqlDbType.VarChar).Value = tbVisitDate.Text.Trim();
     command.Parameters.Add("@visitTimeInHr", SqlDbType.VarChar).Value = txtHourIn.Text.Trim();
     command.Parameters.Add("@visitTimeInMin", SqlDbType.VarChar).Value = txtMinuteIn.Text.Trim();
     command.Parameters.Add("@visitTimeInAmPm", SqlDbType.VarChar).Value = txtAmPmIn.Text.Trim();
     command.Parameters.Add("@visitTimeOutHr", SqlDbType.VarChar).Value = txtTimeOutHr.Text.Trim();
     command.Parameters.Add("@visitTimeOutMin", SqlDbType.VarChar).Value = txtTimeOutMin.Text.Trim();
     command.Parameters.Add("@visitTimeOutAmPm", SqlDbType.VarChar).Value = txtAmPmOut.Text.Trim();
     command.Parameters.Add("@extraPay", SqlDbType.VarChar).Value = rbExtraPayAmount.SelectedValue;
     command.Parameters.Add("@extraPayAmt", SqlDbType.VarChar).Value = txtExtraPayAmount.Text.Trim();
     command.Parameters.Add("@patIdentify", SqlDbType.VarChar).Value = rbPatIdent.SelectedValue;
     command.Parameters.Add("@careGiverAvail", SqlDbType.VarChar).Value = rbCaregiverAvail.SelectedValue;
     command.Parameters.Add("@careGiverAssist", SqlDbType.VarChar).Value = txtCaregiverHelper.Text.Trim();
     command.Parameters.Add("@VitalsTaken", SqlDbType.VarChar).Value = selectedValue;
     command.Parameters.Add("@BodyTemp", SqlDbType.VarChar).Value = txtVitalBodyTemp.Text.Trim(); //Label290.Text;
     command.Parameters.Add("@PulseRate", SqlDbType.VarChar).Value = txtVitalPusleRate.Text.Trim();
     command.Parameters.Add("@RespRate", SqlDbType.VarChar).Value = txtVitalRespRate.Text.Trim();
     command.Parameters.Add("@BloodPressure", SqlDbType.VarChar).Value = txtVitalBloodPress.Text.Trim();
     command.Parameters.Add("@WtTaken", SqlDbType.VarChar).Value = rbWeightTaken.SelectedValue;
     command.Parameters.Add("@ActaulWt", SqlDbType.VarChar).Value = txtInputActualWeigth.Text.Trim();
     command.Parameters.Add("@StatedWt", SqlDbType.VarChar).Value = txtInputStatedWeight.Text.Trim();
     command.Parameters.Add("@Stability", SqlDbType.VarChar).Value = rbStabilityWeight.SelectedValue;
     command.Parameters.Add("@VitalComments", SqlDbType.VarChar).Value = txtVitalComments.Text.Trim();
     command.Parameters.Add("@LungSounds", SqlDbType.VarChar).Value = rbLungsSound.SelectedValue;
     command.Parameters.Add("@LungSoundsOther", SqlDbType.VarChar).Value = txtOthersLungSound.Text.Trim();
     command.Parameters.Add("@Sob", SqlDbType.VarChar).Value = rbShortnessBreath.SelectedValue;
     command.Parameters.Add("@SobDescribe", SqlDbType.VarChar).Value = rbSobDesc.SelectedValue;
     command.Parameters.Add("@SobOther", SqlDbType.VarChar).Value = txtSobOther.Text.Trim();
     command.Parameters.Add("@Sputum", SqlDbType.VarChar).Value = txtSputum.Text.Trim();
     command.Parameters.Add("@Nebulizer", SqlDbType.VarChar).Value = txtNebDrug.Text.Trim();
     command.Parameters.Add("@Oxygen", SqlDbType.VarChar).Value = txtOxygen.Text.Trim();
     command.Parameters.Add("@Continuous", SqlDbType.VarChar).Value = rbOxyContinuous.SelectedValue;
     command.Parameters.Add("@Prn", SqlDbType.VarChar).Value = rbOxyPrn.SelectedValue;
     command.Parameters.Add("@Nasal", SqlDbType.VarChar).Value = rbOxyNasal.SelectedValue;
     command.Parameters.Add("@Trach", SqlDbType.VarChar).Value = rbOxyTrach.SelectedValue;
     command.Parameters.Add("@RespComments", SqlDbType.VarChar).Value = txtRespComments.Text.Trim();
     command.Parameters.Add("@Rhythm", SqlDbType.VarChar).Value = rbCardioRhythm.SelectedValue;
     command.Parameters.Add("@Symptoms", SqlDbType.VarChar).Value = rbCardioSymp.SelectedValue;
     command.Parameters.Add("@SymptomsOther", SqlDbType.VarChar).Value = txtOtherCardioSymp.Text.Trim();
     command.Parameters.Add("@Edema", SqlDbType.VarChar).Value = rbEdema.SelectedValue;
     command.Parameters.Add("@Rle", SqlDbType.VarChar).Value = txtRle.Text.Trim();
     command.Parameters.Add("@Rue", SqlDbType.VarChar).Value = txtRue.Text.Trim();
     command.Parameters.Add("@Lle", SqlDbType.VarChar).Value = txtLle.Text.Trim();
     command.Parameters.Add("@Lue", SqlDbType.VarChar).Value = txtLue.Text.Trim();
     command.Parameters.Add("@EdemaComments", SqlDbType.VarChar).Value = txtEdemaComments.Text.Trim();
     command.Parameters.Add("@BowelMovements", SqlDbType.VarChar).Value = rbBowelMove.SelectedValue;
     command.Parameters.Add("@LastBowelMove", SqlDbType.VarChar).Value = txtLastBowelMove.Text.ToString();
     command.Parameters.Add("@BowelSounds1", SqlDbType.VarChar).Value = quadOne;
     command.Parameters.Add("@BowelSounds2", SqlDbType.VarChar).Value = quadTwo;
     command.Parameters.Add("@BowelSounds3", SqlDbType.VarChar).Value = hypo;
     command.Parameters.Add("@BowelSounds4", SqlDbType.VarChar).Value = hyper;
     command.Parameters.Add("@BowelSounds5", SqlDbType.VarChar).Value = normal;
     command.Parameters.Add("@Dialysis", SqlDbType.VarChar).Value = rbDialysisYn.SelectedValue;
     command.Parameters.Add("@Treatment", SqlDbType.VarChar).Value = rbDialysis.SelectedValue;
     command.Parameters.Add("@Urine", SqlDbType.VarChar).Value = rbUrine.SelectedValue;
     command.Parameters.Add("@UrineOther", SqlDbType.VarChar).Value = tbUrine.Text.Trim();
     command.Parameters.Add("@BowelComments", SqlDbType.VarChar).Value = tbEmulationComments.Text.Trim();

     command.Connection = con;

     try
     {
        con.Open();
        command.ExecuteNonQuery();
        // lblMessage.Text = "Record inserted successfully";
     }
     catch (Exception ex)
     {
        throw ex;
     }
     finally
     {
        con.Close();
        con.Dispose();
     }
  }

The database is showing these values in the table

BowelSounds1 True
BowelSounds2 False
BowelSounds3 False
BowelSounds4 False
BowelSounds5 False

Yet, 1 (1 Quadrant), 3 (Hypo), and 5 (Normal) are checked on the page.

What approach are you using to commit this data to the DB where the database returned system.string[]? Please provide an example of your DB code and how you're passing these values? Your solution will vary depending on if you're using an ORM or old school ADO.NET to manually pass parameters to a stored proc you authored... — Dinglemeyer NeverGonnaGiveUUp, Feb 01 '17 at 23:28
You missed the most important bit for your SQL example, the parameter that is handling your collection. — Jon P, Feb 02 '17 at 00:36

Dinglemeyer NeverGonnaGiveUUp · Answer 1 · 2017-02-02T22:06:36.573

0

I could see this being very simple where it's using the block you provided earlier to get the values into a string array:

var s = cbBowelSounds.SelectedValue;
string[] values = s.Split(',');

bool 1Quadrant = values.Contains("1 Quadrant");
bool 2Quadrant = values.Contains("2 Quadrant"); // Edit to fix this, was previously 1 Quadrant
bool hypo = values.Contains("Hypo");
bool hyper = values.Contains("Hyper");
bool normal = values.Contains("Normal");

command.Parameters.Add("@1Quadrant", SqlDbType.Boolean).Value = 1Quadrant;         
command.Parameters.Add("@2Quadrant", SqlDbType.Boolean).Value = 2Quadrant;
command.Parameters.Add("@Hypo", SqlDbType.Boolean).Value = hypo;
 ...etc...

This may meet your needs? Good luck!

edited Feb 02 '17 at 22:06

answered Feb 01 '17 at 23:59

Dinglemeyer NeverGonnaGiveUUp

703
1
10
23

1

No, it doesn't seem to do what it should based on how you wrote it. It works perfectly in the first value (bool 1Quadrant), but it sets every other value as FALSE regardless of the check. Let me update the post with the changed code to see if that helps you help me :) – RazorSharp Feb 02 '17 at 01:04
I would suggest going into debug and hovering your cursor over the values string array to see if that truly contains what you expect it to?Also, after reviewing this I made a typo in my code that may have your variables misspelled, I've since corrected my answer to reflect this for you! Hopefully this approach allows you to more explicitly plug those values into your SQL to update your database! – Dinglemeyer NeverGonnaGiveUUp Feb 02 '17 at 15:15
Also, you may want to check that cbBowelSounds.SelectedValue is returning an actual array as you'd expect, it's possible that s.Split(",") isn't doing anything if that s variable is only populated with one thing? – Dinglemeyer NeverGonnaGiveUUp Feb 02 '17 at 15:18
It could be advisable to attempt another way of getting those values from the Checkboxlist as discussed at http://stackoverflow.com/questions/18924147/how-to-get-values-of-selected-items-in-checkboxlist-with-foreach-in-asp-net-c – Dinglemeyer NeverGonnaGiveUUp Feb 02 '17 at 15:28

Store multiple values in database where checked

1 Answers1