title: How to insert the selected type of the suggestion into the database?
body: <p>I am developing a simple suggestion web-based application using ASP.NET with C#. It works well except one thing. I have the following database design:</p>
  
  <p>Table <strong>SafetySuggestionsLog</strong></p>
  
  <pre><code>ID, Title, Description, Username, DateSubmitted, TypeID, OtherTypes
  </code></pre>
  
  <p>Table <strong>SafetySuggestionsType</strong></p>
  
  <pre><code>ID, Type
  </code></pre>
  
  <p>The user will be able to fill the form and submit it. I could be able to insert the username, title, description and datesubmitted to the database. But I have to insert the type either it is one of the types in the database or a new one. I am struggling with this part and I don't know who resolve it. <strong>So could you please help me in this? How I can insert the value of type into the database?</strong></p>
  
  <p>ASP.NET Code:</p>
  
  <pre class="lang-html prettyprint-override"><code>&lt;div ID="contactform"&gt;
    &lt;ol&gt;
      &lt;li&gt;
        &lt;label for="subject"&gt;Type&lt;/label&gt;
        &lt;asp:DropDownList ID="DropDownList" runat="server"
         DataSourceID="SqlDataSource1" Width="155px" Font-Bold="True"
         ForeColor="#006666" AppendDataBoundItems="false" DataTextField="Type"
         DataValueField="ID" AutoPostBack="true"
         OnDataBound="DropDownList_DataBound"/&gt;
        &lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" 
         ConnectionString="&lt;%$ ConnectionStrings:testConnectionString %&gt;" 
         SelectCommand="SELECT * FROM [SafetySuggestionsType]"/&gt;
        &lt;asp:TextBox ID="TextBox1" runat="server" CssClass="text"/&gt;&lt;br /&gt;
        &lt;asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" 
         ControlToValidate="dropdownlist"
         ErrorMessage="Please select a type ... or choose Others"/&gt;
        &lt;asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" 
         ControlToValidate="TextBox1" 
         ErrorMessage="Please enter a type for your suggestion"/&gt;
      &lt;/li&gt;
      &lt;li&gt;
        &lt;label for="subject"&gt;Subject&lt;/label&gt;
        &lt;asp:TextBox ID="txtSubject" runat="server" CssClass="text"/&gt;&lt;br /&gt;
        &lt;asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
         ControlToValidate="txtSubject" 
         ErrorMessage="Please enter a subject/title for your suggestion"/&gt;
      &lt;/li&gt;
      &lt;%--The following hidden field is for inserting the date--%&gt;
      &lt;li&gt;
        &lt;asp:TextBox ID="dateSubmitted" runat="server" CssClass="text"
         Visible="false"/&gt;&lt;br /&gt;
      &lt;/li&gt;
      &lt;li&gt;
        &lt;label for="message"&gt;Your Suggestion&lt;/label&gt;
        &lt;asp:TextBox ID="txtSuggestion" runat="server" cols="50"
         CssClass="textarea" rows="6" TextMode="MultiLine"/&gt;&lt;br /&gt;
        &lt;asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" 
         ControlToValidate="txtSuggestion"
         ErrorMessage="Please enter your suggestion"/&gt;
      &lt;/li&gt;
      &lt;li class="buttons"&gt;
        &lt;asp:ImageButton ID="imageField" runat="server"
         ImageURL="images/Send.gif" OnClick="btnSubmit_Click" /&gt;
        &lt;%--&lt;input type="image" name="imageField" id="imageField"
         src="images/Send.gif" /&gt;--%&gt;
      &lt;/li&gt;
    &lt;/ol&gt;
  &lt;/div&gt;
  </code></pre>
  
  <p>Code-Behind:</p>
  
  <pre class="lang-cs prettyprint-override"><code>protected void btnSubmit_Click(object sender, ImageClickEventArgs e)
  {
      SmtpClient sc = new SmtpClient("MAIL.Aramco.com");
      StringBuilder sb = new StringBuilder();
      MailMessage msg = null;
  
      sb.Append("Message from: " + Service.User.Name + "\n");
      sb.Append("Email: " + Service.User.GetProperty("EMP_BINTERMAIL") + "\n");
      sb.Append("Type: ");
      sb.Append((DropDownList.SelectedItem.Text.Equals("Others")
          ? TextBox1.Text : DropDownList.SelectedItem.Text) + "\n");
      sb.Append("Title: " + txtSubject.Text + "\n");
      sb.Append("Suggestion   : " + txtSuggestion.Text + "\n");
  
      try
      {
          msg = new MailMessage(Service.User.GetProperty("EMP_BINTERMAIL"),
              "JOHN.ARNESON@ARAMCO.COM", "PSSP: New Safety Suggestion Box",
              sb.ToString());
          sc.Send(msg);
          MultiView1.SetActiveView(ViewConfirm);
      }
      catch (Exception ex)
      {
          throw ex;
          //Response.Write("Something bad happened!");
      }
      finally
      {
          if (msg != null)
          {
              msg.Dispose();
          }
      }
  
      //For storing the suggestions in the database
      string connString = "Data Source=localhost\\sqlexpress;"
          + "Initial Catalog=psspdbTest;Integrated Security=True";
      string insertCommand = "INSERT INTO SafetySuggestionsLog "
          + "(Title, DateSubmitted, Description, Username) values "
          + "(@Title, @DateSubmitted, @Description, @Username)";
      string username = Service.User.ID;
      using (SqlConnection conn = new SqlConnection(connString))
      {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand(insertCommand, conn))
          {
              cmd.Parameters.Clear(); 
              cmd.Parameters.AddWithValue("@Title", txtSubject.Text);
              cmd.Parameters.AddWithValue("@DateSubmitted",
                  DateTime.Now.ToString());
              cmd.Parameters.AddWithValue("@Description", txtSuggestion.Text);
              cmd.Parameters.AddWithValue("@Username", username);
              cmd.ExecuteNonQuery();
          }
      }
  }
  </code></pre>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>I modified my code for storing the suggestion information:</p>
  
  <pre><code>//For stroing the suggestions in the database
          string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psspdbTest;Integrated Security=True";
          //string insertCommand = "INSERT INTO SafetySuggestionsLog (Title, DateSubmitted, Description, Username) values(@Title, @DateSubmitted, @Description, @Username)";
          string insertCommand = @"insert into SafetySuggestionsLog
                                    ( Title, Description, Username, DateSubmitted, TypeID, OtherTypes ) values
                                    ( @Title, @Description, @Username, @DateSubmitted,
                                      ( select ID from SafetySuggestionsType where Type = @Type ), '?' )";
          string username = Service.User.ID;
          using (SqlConnection conn = new SqlConnection(connString))
          {
              conn.Open();
              using (SqlCommand cmd = new SqlCommand(insertCommand, conn))
              {
                  cmd.Parameters.Clear(); 
                  cmd.Parameters.AddWithValue("@Title", txtSubject.Text);
                  cmd.Parameters.AddWithValue("@DateSubmitted", DateTime.Now.ToString());
                  cmd.Parameters.AddWithValue("@Description", txtSuggestion.Text);
                  cmd.Parameters.AddWithValue("@Username", username);
                  cmd.Parameters.AddWithValue("@Type", DropDownList.SelectedValue);
                  cmd.ExecuteNonQuery();
              }
          }
  </code></pre>
  
  <p>And still I could not be able to store the selected type from the dropdownlist. I got (?) under othertype column in the databast and NULL under the Type column even when I selected one of the predefined types. WHY?</p>