dataAdapter.Update / SQL PasswordHash NULL problem

[BobLewiston]

Some of you may have seen my earlier thread “PasswordHash NULL problem”. I’ve started a new thread because investigation has shown that the problem is actually quite different than I previously stated. Also please note that this is unrelated to another of my previous threads, “dataAdapter.Update problem”, which incidentally has been resolved.

I’m learning SQL. I’m accessing database SQL2008 AdventureWorks, table Person.Contact, which has a column PasswordHash, of type varchar (128). Here’s the code I’m using to save records:

// here dataTable and dataSet have been declared at the class-wide level as
// private DataTable dataTable;
// private DataSet dataSet;
 
DataRow row = dataTable.Rows [currRec];
 
row.BeginEdit ();
row ["Title"]        = txtTitle.Text;
row ["FirstName"]    = txtFirstName.Text;
row ["MiddleName"]   = txtMiddleName.Text;
row ["LastName"]     = txtLastName.Text;
row ["Suffix"]       = txtSuffix.Text;
row ["Phone"]        = txtPhone.Text;
row ["EmailAddress"] = txtEmailAddress.Text;
row.EndEdit ();
 
try { dataAdapter.Update (dataSet, "Person.Contact"); }
catch (System.Runtime.InteropServices.ExternalException exc)
{
    MessageBox.Show (exc.Message + "\n\n" + currRec + "\n\"" + 
        dataSet.Tables ["Person.Contact"].Rows [currRec] ["PasswordHash"].ToString () + "\"", 
        "System.Runtime.InteropServices.ExternalException");
}
catch (Exception exc) { MessageBox.Show (exc.Message, "Exception"); }
 
try { dataSet.AcceptChanges (); }
catch (Exception exc) { MessageBox.Show (exc.Message, "dataSet.AcceptChanges ();"); }
 

When I edit and save an existing record (which already has a PasswordHash) to the locally resident DataSet and then to the database, it works fine (which of course means that the AdventureWorks sample database is not read-only). But when I try to save a new (inserted) record, even if I include a statement

row ["PasswordHash"] = "GylyRwiKnyNPKbC1r4FSqA5YN9shIgsNik5ADyqStZc=";

in the above Edit, 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 get this message despite the fact that the PasswordHash is displayed in the MessageBox as being in the Dataset, and the statement

dataSet.AcceptChanges ();

throws no exception!

Using SQL Server 2008 Management Studio Express, I can find no property of column PasswordHash that would account for this.

Can anybody tell me: how can I write to the PasswordHash column? (This is the most important question in this post, and what distinquishes it from my previous thread.)

Next question (for when I have the above issue resolved): although "GylyRwiKnyNPKbC1r4FSqA5YN9shIgsNik5ADyqStZc=" is a real password hash that I lifted from an already existing record (see above), I’m pretty sure I can’t just plunk it into a new record, because I notice that every record has a different password hash, which means every record has a different password. This seems very strange to me. What purpose could this serve? And more importantly: how can I generate valid password hashes for new records that have not (yet) had passwords associated with them?

Or perhaps I can make SQL Server Management Studio allow NULL in the PasswordHash column. I discovered this would be possible 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, under two possible scenarios:

Firstly, 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 secondly, I have received the following warning about allowing null password hashes:

These tables are usually related to one another. There are referential and domain integrity checks within the tables across the database. Although you might be able to change some of these, some of the relationships and checks might be broken because of your update.

In other words, the writer speculates that allowing null password hashes might compromise the relationships between tables. For that matter, I suppose the same might be said about using “fake” password hashes, as described above.

On the other hand, wouldn't a password hash be relevant only to applications that use passwords? All I want MY application to do is to edit, insert and delete records. It won't require any passwords. Is it really likely that relationships between tables could be compromised if null or “fake” password hashes are used?

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

[ck9663]

When I edit and save an existing record (which already has a PasswordHash) to the locally resident DataSet and then to the database, it works fine (which of course means that the AdventureWorks sample database is not read-only). But when I try to save a new (inserted) record, even if I include a statement

The error "INSERT fails." means you're inserting a new record, which you are. This happens when you're trying to specifically insert a NULL value on a column that does not allow NULL.

something like:

INSERT INTO Person.Contact  (FirstName,  LastName, PasswordHash)
values ('CLARK', 'KENT', NULL)
 

The values could be coming from a SELECT statement as well.

or it could be that you missed that column. Something like:

INSERT INTO Person.Contact  (FirstName,  LastName,)
values ('CLARK', 'KENT')
 

Try posting here your INSERT statement and let's see what we can do.

On the other hand, wouldn't a password hash be relevant only to applications that use passwords? All I want MY application to do is to edit, insert and delete records. It won't require any passwords. Is it really likely that relationships between tables could be compromised if null or “fake” password hashes are used?

The relationship is more of related to whatever keys join these tables. That means if you delete a record from Person.Contact it should be deleted to all other tables that are related to it. That's a little challenging to find it. Since ContactID is an IDENTITY, you will not be able to reuse the value. Which means even if you insert it back, the relationship of the rows is already broken.

Firstly, 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.

I'm not 100% sure. Why don't you create a new (workdb) database, copy the table that you want from AdventureWorks to that workdb and you can do anything you want with it.

-- CK

[BobLewiston]

ck9663:

Try posting here your INSERT statement and let's see what we can do.

Yeah, the INSERT statement was the issue. Problem resolved. Thanks for your help.