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

  • Question

  • User1979939751 posted

    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();
      }
          catch (Exception ex)
          {
           LabelMessage.Text = "Error at the catch: " + ex.Message.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 occured. 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?

    Saturday, August 3, 2019 4:03 PM

Answers

  • User-821857111 posted

    Your finally block will always execute. It sets the text of LabelMessage to Success regardless if there was an error. Use the debugger to see what's actually going on when your code executes. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 3, 2019 8:54 PM
  • User1979939751 posted

    Thank you for your help, and it is solved. I moved the LabelMessage out of the finally and the error was that there was a comma (,) missing in the query between [ProfileGroup] and [CompanyID]. That's what happens after working 8-to hrs M-S, and try to work at home as well. Thanks

    The try-catch-finally block now looks like this

    try
                                {
                                    // Perform data insertion
                                    connection.Open();
                                    insertNewUser.ExecuteNonQuery();
                                    //var recordsAffected = insertNewUser.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    LabelMessage.Text = "Error at the catch: " + ex.Message.ToString();
                                    PanelResultSection.Visible = true;
                                    PanelResultSection.CssClass = "alert-warning";
                                    return;
                                }
                                finally
                                {
                                    // Close connection
                                    connection.Close();
                                }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 3, 2019 11:10 PM

All replies

  • User-821857111 posted

    Your finally block will always execute. It sets the text of LabelMessage to Success regardless if there was an error. Use the debugger to see what's actually going on when your code executes. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 3, 2019 8:54 PM
  • User1979939751 posted

    Thank you for your help, and it is solved. I moved the LabelMessage out of the finally and the error was that there was a comma (,) missing in the query between [ProfileGroup] and [CompanyID]. That's what happens after working 8-to hrs M-S, and try to work at home as well. Thanks

    The try-catch-finally block now looks like this

    try
                                {
                                    // Perform data insertion
                                    connection.Open();
                                    insertNewUser.ExecuteNonQuery();
                                    //var recordsAffected = insertNewUser.ExecuteNonQuery();
                                }
                                catch (Exception ex)
                                {
                                    LabelMessage.Text = "Error at the catch: " + ex.Message.ToString();
                                    PanelResultSection.Visible = true;
                                    PanelResultSection.CssClass = "alert-warning";
                                    return;
                                }
                                finally
                                {
                                    // Close connection
                                    connection.Close();
                                }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 3, 2019 11:10 PM