What it wrong with my SQL insert statement C# ASP.NET

Question

I'm using a web page to create users and add them to a table (like an Admin who is creating other users...). My code does not insert data into the table, but I can manually insert data into the table using the same value collected from the ASP form.

This will be for a website where a company will register, then the admin will create users (employees) with their profile.

Here is my table definition

[Id]                      INT            IDENTITY (1, 1) NOT NULL,
[Username]                NVARCHAR (100) NOT NULL,
[Password]                NVARCHAR (100) NOT NULL,
[Profile]                 NVARCHAR (25)  NOT NULL,
[ProfileGroup]            NVARCHAR (25)  NOT NULL,
[CompanyID]               INT            NOT NULL,
[CreateDate]              DATE           NOT NULL,
[LastLogin]               DATE           NOT NULL,
[FirstName]               NVARCHAR (100) NOT NULL,
[LastName]                NVARCHAR (100) NOT NULL,
[Department]              NVARCHAR (100) NULL,
[Phone]                   NVARCHAR (25)  NULL,
[Email]                   NVARCHAR (100) NULL,
[RecoveryEmail]           NVARCHAR (100) NULL,
[Photo]                   NVARCHAR (100) NULL,
[Salt]                    NVARCHAR (25)  NULL,
[LastPasswordChangedDate] DATE           NOT NULL,

Here is my code. Connection string already declared.

if (Page.IsValid)
    {
     string newFName = TextBoxFirstName.Text.Trim();
     string newLName = TextBoxLastName.Text.Trim();
     string newUsername = TextBoxUsername.Text.Trim();
     string newProfile = DropDownListProfile.SelectedValue;
     string newUserEmail = TextBoxEmail.Text.Trim();
     string newRecoveryEmail = TextBoxRecoveryEmail.Text.Trim();
     string newDepartment = TextBoxDepartment.Text.Trim();
     string newPhoneNumber = TextBoxPhone.Text.Trim();
     //string newPhoto = "";
     string newPassword = TextBoxPassword.Text.Trim();
     string theSalt = GetRandomString(12);
     int theCompanyID = Int32.Parse(LabelCompanyID.Text);
     // Hash password: Salt + password
     string hashedPassword = HashPassword(theSalt + newPassword);
     // First verify if the username has already been taken
     string verifyNewuserSql = "SELECT [UserAccount].[Username] FROM [UserAccount] WHERE [UserAccount].[Username] = @uname";
     using (var connection = new SqlConnection(sixConnection))
     {
      connection.Open();
      // Command to execut query connection
      SqlCommand UserComm = new SqlCommand(verifyNewuserSql, connection);
      UserComm.Parameters.AddWithValue("@uname", newUsername);
      SqlDataReader UserDr = UserComm.ExecuteReader();
      if (UserDr.HasRows)
      {
     // User exist
     UserDr.Close();
     connection.Close();
     // Message: user exists
     PanelResultSection.Visible = true;
     PanelResultSection.CssClass = "alert-warning";
     LabelMessage.Text = "username already exists. Nothing has been done. If you see this page by mistake, contact you manager.";
     return;
      }
      else
      {
     // close previous data reader but keep connection opened
     UserDr.Close();
     //connection.Close();
     // Get the Profile group value
     string theProfileGroupe = null;
     string profileGroupSql = "SELECT [UserProfile].[profileGroup] FROM [UserProfile] WHERE [UserProfile].[profile]= @newProfile";
     SqlCommand ReadProfileComm = new SqlCommand(profileGroupSql, connection);
     ReadProfileComm.Parameters.AddWithValue("@newProfile", newProfile);
     SqlDataReader profileDr = ReadProfileComm.ExecuteReader();
     if (profileDr.HasRows)
     {
      while (profileDr.Read())
      {
       theProfileGroupe = profileDr.GetString(0);
      }
      // close previous data reader and do not keep connection opened --> close connection
      profileDr.Close();
      connection.Close();
      // add data to userdb
      string varCreateDate = DateTime.Today.ToString("MM/dd/yyyy");
      string varLastLogin = varCreateDate;
      string varLastPasswordChangedDate = varCreateDate;
      string insertNewUserSql = "INSERT INTO [UserAccount] ( ";
      insertNewUserSql = (insertNewUserSql + "[Username], [Password], [Profile], [ProfileGroup] [CompanyID], [CreateDate], [LastLogin], [FirstName], [LastName], ");
      insertNewUserSql = (insertNewUserSql + "[Department] , [Phone], [Email], [RecoveryEmail], [Photo], [Salt], [LastPasswordChangedDate]");
      insertNewUserSql = (insertNewUserSql + "VALUES (");
      insertNewUserSql = (insertNewUserSql + "@Username, @Password, @Profile, @ProfileGroup, @CompanyID, @CreateDate, @LastLogin, @FirstName, @LastName, ");
      insertNewUserSql = (insertNewUserSql + "@Department, @Phone, @Email, @RecoveryEmail, @Photo, @Salt, @LastPasswordChangedDate)");

      // Passing parameters
      SqlCommand insertNewUser = new SqlCommand(insertNewUserSql, connection);
      insertNewUser.Parameters.AddWithValue("@Username", newUsername);
      insertNewUser.Parameters.AddWithValue("@Password", hashedPassword);
      insertNewUser.Parameters.AddWithValue("@Profile", newProfile);
      insertNewUser.Parameters.AddWithValue("@ProfileGroup", theProfileGroupe);
      insertNewUser.Parameters.AddWithValue("@CompanyID", theCompanyID);
      insertNewUser.Parameters.AddWithValue("@CreateDate", varCreateDate);
      insertNewUser.Parameters.AddWithValue("@LastLogin", varLastLogin);
      insertNewUser.Parameters.AddWithValue("@FirstName", newFName);
      insertNewUser.Parameters.AddWithValue("@LastName", newLName);
      insertNewUser.Parameters.AddWithValue("@Department", newDepartment);
      insertNewUser.Parameters.AddWithValue("@Phone", newPhoneNumber);
      insertNewUser.Parameters.AddWithValue("@Email", newUserEmail);
      insertNewUser.Parameters.AddWithValue("@RecoveryEmail", newRecoveryEmail);
      insertNewUser.Parameters.AddWithValue("@Photo", DBNull.Value);
      insertNewUser.Parameters.AddWithValue("@Salt", theSalt);
      insertNewUser.Parameters.AddWithValue("@LastPasswordChangedDate", varLastPasswordChangedDate);
      try
      {
       // Perform data insertion
       connection.Open();
       insertNewUser.ExecuteNonQuery();
       //var recordsAffected = insertNewUser.ExecuteNonQuery();

      }
      catch (Exception ex)
      {
       LabelMessage.Text = "Error at the catch: " + ex.ToString();
       PanelResultSection.Visible = true;
       PanelResultSection.CssClass = "alert-warning";
       return;
      }
      finally
      {
       // Close connection
       connection.Close();
       // Send email and display link to login
       PanelResultSection.Visible = true;
       PanelResultSection.CssClass = "alert-success";
       LabelMessage.Text = "Success! " + newFName + " " + newLName + " has been added to " + theProfileGroupe + " group.";

      }
     }
     else
     {
      // user does not have profile group
      // Message: user exists
      PanelResultSection.Visible = true;
      PanelResultSection.CssClass = "alert-warning";
      LabelMessage.Text = "An error has occurred. Nothing has been done. If you see this page by mistake, contact you manager.";
          return;
         }

      }
     }
    }

The code runs without error. It shows success, but the data does not show in the table even after refresh. I've working on this for the last three days with no result. Can someone help?

Answer 1

You have an error handling bug, and it's preventing you from seeing the error details after your query fails.

The finally block will run after the catch and so you shouldn't have the "success" logic in the finally block.

Minimally, change to something like:

  try
  {
   // Perform data insertion
   connection.Open();
   insertNewUser.ExecuteNonQuery();
  }
  catch (Exception ex)
  {
   LabelMessage.Text = "Error at the catch: " + ex.ToString();
   PanelResultSection.Visible = true;
   PanelResultSection.CssClass = "alert-warning";
   return;
  }
  finally
  {
   // Close connection
   connection.Close();
  }
 // Send email and display link to login
 PanelResultSection.Visible = true;
 PanelResultSection.CssClass = "alert-success";
 LabelMessage.Text = "Success! " + newFName + " " + newLName + " has been added to " + theProfileGroupe + " group.";