Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Nov 2008
    Thanked 0 Times in 0 Posts

    PasswordHash NULL problem

    When I try to save a new (inserted) record in an SQL database, I get the following System.Runtime.InteropServices.ExternalException message:

    Cannot insert the value NULL into column 'PasswordHash', table 'AdventureWorks.Person.Contact'; column does not allow nulls.
    INSERT fails.
    The statement has been terminated.
    I have to either find out how to insert an appropriate value into the PasswordHash column OR make SQL Server Management Studio allow NULL in the PasswordHash column.

    I discovered I could do the latter in SQL Server Management Studio via:

    expand table | expand Columns | right-click PasswordHash column | click Modify | in lower right frame: toggle Allow Nulls from No to Yes

    On doing so and then attempting to exit SQL Server Management Studio, I got a dialog box saying:

    Save changes to the following items?
    SQL Server Objects
    <Server name>.AdventureWorks - Person.Contact
    Clicking Yes elicited the following message:

    Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

    Contact (Person)
    SQL Server Management Studio's onboard Help says I can override the "Prevent saving changes that require the table to be re-created" setting via:

    Tools | Options | Designers | Table and Database Designers | Prevent saving changes that require table re-creation

    I can try this, but I wonder if it might be dangerous. If for whatever reason the table can't be re-created, could I possibly destroy the original table in the process and then have to reinstall the AdventureWorks database? I don't want to have to do that, since for some unknown reason I had a very difficult time installing it the first time.

    And ultimately, I don't want to sacrifice encryption, as I suspect might be the case if I allowed PasswordHash to be NULL.

    So here are my two questions:
    1. Could it be dangerous to try to re-create the table?
    2. How do I get an appropriate value to put in the PasswordHash column?

    For what it's worth, I'm working in a 32-bit environment with the following software:

    SQL Server 2008 Express with Advanced Services
    database: SQL2008 AdventureWorks (schema.table: Person.Contact)
    SQL Server 2008 Management Studio Express
    Visual C# 2008 Express

  2. #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 Posts
    Not to ask a silly question, but why not just put a zero into the passwordHash field??

    Zero almost surely isn't a good passwordHash value, so it shouldn't hurt any of the password checking stuff. But it should get you past the SQL problem.

    No idea why SQL Server thinks it has to rebuild the table just because of that change, by the way. Seems dirt silly to me.

    I can think of hacky ways around it, but why not try the zero value trick, first? (Or look at the current values, and if none of them are--say--negative, then use -1 instead of zero. Or or or...)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts