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