I think in SQL key fields of type varchar are always trimmed independent of the ANSI_PADDING option.
If ANSI_PADDING is ON (the default) non key varchar fields will allow trailing spaces. -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Christopher Reed Sent: Tuesday, October 25, 2005 1:05 PM To: [email protected] Subject: Re: [ADVANCED-DOTNET] Primary Key Bug It just dawned on me what's probably happening. If the column length is fixed (type Char), then all data is padded to fit the column. Thus, "key1" and "key1 " become the same. Likewise, if the column length is variable (type VarChar), then all data is trimmed, so "key1 " becomes "key1". Thus, extra spaces at the end of a string becomes irrelevant. Christopher Reed Web Applications Supervisor Information Technology City of Lubbock [EMAIL PROTECTED] "The oxen are slow, but the earth is patient." >>> [EMAIL PROTECTED] 11:44:46 am 10/25/2005 >>> >From a data perspective, what makes it different? It adds nothing to your data. For a primary key, the first four characters are equivalent. The space is irrelevant from a data perspective. The space adds nothing to your data. Thus, the two values are equivalent with respect to the primary key. I experimented in SQL Server with a table that has a column called KeyField that's a Char(10) and set as the primary key. In the first row, I entered "key1" while in the second row I tried to enter "key1 ". I received a duplicate key error. So, if this is a bug, then it's in SQL Server as well. As a data purist, one should never have any form of ambiguity with respect to your primary key. The values should be clearly distinct. Then again, primary key values should never be controlled externally to begin with, but that's a whole other esoteric discussion for academia. Christopher Reed Web Applications Supervisor Information Technology City of Lubbock [EMAIL PROTECTED] "The oxen are slow, but the earth is patient." >>> [EMAIL PROTECTED] 10:17:37 am 10/25/2005 >>> No....that's just another BS excuse. If XML doesn't recognize them as different then XML is wrong too. The data is different and no software tool should ever been making assumptions about the data. IT IS A BUG!!!!!!!!!!!!!! Message from Christopher Reed <[EMAIL PROTECTED]>@DISCUSS.DEVELOP.COM received on 10/25/2005 09:51 AM 10/25/2005 09:51 AM Christopher Reed <[EMAIL PROTECTED]>@DISCUSS.DEVELOP.COM Please respond to "Discussion of advanced .NET topics." <[email protected]> Sent by "Discussion of advanced .NET topics." <[email protected]> To: [email protected] cc: Subject: Re: [ADVANCED-DOTNET] Primary Key Bug Let's look at this from another perspective. <?xml version="1.0" standalone="yes"?> <NewDataSet> <Table1> <Key>key1</Key> </Table1> <Table1> <Key>key1 </Key> </Table1> </NewDataSet> The above is the XML representation of your datatable (in a dataset). The point is that if you tried to make the Key element unique in the above XML, it would complain because the spacing is not relevant to an XML document. Thus, you should always view data within ADO.NET as if it was represented by a XML file. This is not a bug. This is by design of XML. Hope this helps! Christopher Reed Web Applications Supervisor Information Technology City of Lubbock [EMAIL PROTECTED] "The oxen are slow, but the earth is patient." >>> [EMAIL PROTECTED] 5:00:33 pm 10/24/2005 >>> Anybody noticed that this code errors? Is this fixed in 2.0? Module Module1 Sub Main() Dim DT As New DataTable DT.Columns.Add("Key", GetType(String)) DT.Rows.Add(New Object() {"key1"}) DT.Rows.Add(New Object() {"key1 "}) DT.PrimaryKey = New DataColumn() {DT.Columns("Key")} End Sub End Module =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
