SqlDataSource/DataField Bug in 2.0 ?

• Pete Wojtkowiak

  Did you read my post Here's the Solution:

  When building your Select statement use aliases for the fieldnames that has spaces. E.g.

  Select [First Name] as FirstName, [Last Name] as Last_Name From Customers

  Now to bind the control simply use the alias instead of the fieldname. E.g.

  <asp:Textbox ID="txt" Text='<%# Bind("FirstName") %>' runat=server />

  I hope this helps.

  Junnark

  
  

• Olas

  Adamus Turner wrote:

  I'd just like to regurgitate the deja-vu. There's no such thing as a bug for bad programming practice. Adamus

  Hi Adamus,

  Reguritating doesn't really change anything insofar as you may or may not have the ability to change the DB systems - especially in a business environment.

  Best regards,

  Peter

  
  

• PhilipRieck

  Thank you, it worked :)
  

• HenrikStaunPoulsen

  I just tried the following in VWD;

  1. Create new website "DataFieldSpaceTest"
  2. Add new Item App-Data->Sql Database "Database.mdf"
  3. Database->Add Table "DatafieldTest"
  4. Database->Tables->Add Column id, bigint, identity yes, Primary Key
  5. Database->Tables->Add Column Caption, nchar(10), nullable
  6. Database->Tables->Show Data->add column with caption Test
  7. Drag SqlDataSource to Default.aspx design view
  8. SqlDatasource->QuickTask->Configure Data->"Database.mdf", Save connection, Specify Columns, *, Advanced->Generate & Optimistic checked.
  9. Drag Formview to Default.aspx design view
  10. Formview1->Quicktask->Choose Datasource "SqlDatasource1"
  11. Hit Run/F5, Click Edit, change Caption to Test1, click update to verify it works.

  To break it;

  • Delete Default.aspx->SqlDataSource
  • Delete Default.aspx->Formview1
  • Database->Tables->Add Column My Space Test, nvarchar(50), nullable - (Notice how the editor adds square brackets around the name, which personally led me to believe that Spaces in DB names were properly handled ;) )
  • Drag SqlDataSource to Default.aspx design view
  • SqlDatasource->QuickTask->Configure Data->"Connection String"Specify Columns, *, Advanced->Generate & Optimistic checked.
  • Drag Formview to Default.aspx design view
  • Formview1->Quicktask->Choose Datasource "SqlDatasource1"
  • Hit Run/F5, Click Edit, change Caption to Test1, click update to verify that it is now broken.
  • New is broken as well, ie, the insert statement.

  You should get the following stack trace;

  Server Error in '/DataFieldSpaceTest' Application.

• cmtytest2

  Thanks for your response- I believe your solution is what I have already tried to do-

  Here is some of my code-

  SelectCommand="SELECT * FROM [CoreBusiness] ORDER BY [ID] DESC" UpdateCommand="UPDATE [CoreBusiness] SET [BldgSqFt] = @BldgSqFt, [BldgType] = @BldgType, [Business Mgr] = @Business_Mgr, [Business Owner] = @Business_Owner, [BusinessName] = @BusinessName, [BusinessType] = @BusinessType, [City] = @City, [ComplexName] = @ComplexName, [Fax Number] = @Fax_Number, [FinalCOissue] = @FinalCOissue, [FireDistrict] = @FireDistrict, [LastChangeDate] = @LastChangeDate, [NumOfEmpe] = @NumOfEmpe, [OccuGroup] = @OccuGroup, [Phone] = @Phone, [PIN] = @PIN, [State] = @State, [StreetName] = @StreetName, [StreetNmbr] = @StreetNmbr, [Suite] = @Suite, [TempCoissue] = @TempCoissue.....

  This is how I bind my data:

  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"

  DataSourceID="SqlDataSource1" Style="z-index: 100; left: 40px; position: absolute;

  top: 104px" DataMember="DefaultView" AllowSorting="True" AutoGenerateEditButton="True">

  <Columns>

  <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

  <asp:BoundField DataField="BldgSqFt" HeaderText="BldgSqFt" SortExpression="BldgSqFt" />

  <asp:BoundField DataField="BldgType" HeaderText="BldgType" SortExpression="BldgType" />

  <asp:BoundField DataField="Business Mgr" HeaderText="Business Mgr" SortExpression="Business Mgr" />

  <asp:BoundField DataField="Business Owner" HeaderText="Business Owner" SortExpression="Business Owner" />

  <asp:BoundField DataField="BusinessName" HeaderText="BusinessName" SortExpression="BusinessName" />

  <asp:BoundField DataField="BusinessType" HeaderText="BusinessType" SortExpression="BusinessType" />

  <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />

  <asp:BoundField DataField="ComplexName" HeaderText="ComplexName" SortExpression="ComplexName" />

  <asp:BoundField DataField="Fax Number" HeaderText="Fax Number" SortExpression="Fax Number" />...............

  How would I bind that data using the alias to the grid view Just an FYI: I'm using Visual web developer 2005.

  
  

• Feret

  I am coming up with the same problem. Our database has column names with spaces in it, but I have used alias's without spaces, for example:

  UPDATE [CoreBusiness] SET [BldgSqFt] = @BldgSqFt, [BldgType] = @BldgType, [Business Mgr] = @Business_Mgr, [Business Owner] = @Business_Owner, [BusinessName] = @BusinessName, [BusinessType] = @BusinessType, [City] = @City, [ComplexName].....

  But I am still receiving this error. Any assistance would be very much appreciated

  
  

• chr15athome

  Suggestion: When creating Fieldnames don't use spaces, e.g. FirstName. Also, if your company has a very minute plan of moving to Oracle in the future, use only upper case letters for your fieldnames, e.g. FIRST_NAME.

  However, if the database that you're working on was done by someone else and you cannot avoid spaces then here's the work around.

  Yes there's a workaround. When retrieving your columns using the Select columnName, make sure to alias your columNames. e.g.

  Select [First Name] as First_Name, [Last Name] as LastName From Customers

  When binding on the Aspx Form simply put: Bind("First_Name")

  I ran into this because I'm working on an ASPX Webforms Generator and Am trying to come up on the different ways people create their fieldnames. Again, I don't recommend spaces on your fieldnames.

  I hope this helps.

  
  

• FlyingHorse

  Hello. Next time, please be more specific on what kind of control you're trying to bind to, e.g. GridView, FormView, DetailsView. The solution is still the same, but the binding is a little different from the formview/detailsview and the gridview. Please follow the example.

  To be more explicit here's what you do.

  - Don't ever use a Select *, instead Select each of of the field and alias the ones that have spaces. E.g.

  Rather than: SELECT * FROM [CoreBusiness] use:

  Select [BldgSqFt], [Business Mgr] As BusinessMgr ........ From [CoreBusiness]

  make sure to list all your fields. Notice that since BldgSqFt does not have spaces you don't need to give it another name.

  To bind your GridView use the alias names instead of the fieldnames: For example:

  <asp:BoundField DataField="BusinessMgr" HeaderText="Business Mgr" SortExpression="BusinessMgr" />

  Do this to all your fields that has spaces.

  Junnark

  
  

• ThomasC22

  Yes, I'm sure it's 2005. It's a GridView.
  
  <asp:BoundField DataField="[Game Developer]" HeaderText="Developer" SortExpression="Developer" />
  
  That's the exact line.
  
  It says it cannot find [Game Developer] in the SqlDataSource.
  
  If I change it to:
  
  <asp:BoundField DataField="Game Developer" HeaderText="Developer" SortExpression="Developer" />
  
  I get a HttpWebException saying invalid data near nvarchar. This makes sense since the column name is [Game Developer] .
  
  So it's like a Catch 22. The DataField property cannot find [Game Developer] in the SqlDataSource, although its explicitly there, and the if you remove the braces, you're pointing to a database column that doesn't exist.
  
  
  
  
  

• TRS-Bo

  You're welcome.
  

• Jack_Software

  I could not reproduce this. I created a table like so:

  create table [spacey man] ([id] int primary key, [field one] nvarchar(255), [field two with [['s inside] nvarchar(255))

  insert into [spacey man] values (1, N'AAAA', N'BBBB')

  insert into [spacey man] values (2, N'AAAA', N'BBBB')

  Then I used the wizard to do all the bindings, everything worked fine in DataGridView and in bound text boxes.

  The wizard generated some code like so to bind the text box:

  //
  // field_two_with__s_insideTextBox
  //
  this.field_two_with__s_insideTextBox.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.spacey_manBindingSource, "field two with [[\'s inside", true));
  

  Are you sure you are using VS.NET 2005, I did not see this DataField property in VS.NET 2005, I think you are using VS.NET 2003 Let me know.

  
  
  

• Matt David

  I totally agree with Peter.

  I've worked with databases where you can't even write a single line of stored procedure because the dba(s) won't let you. I've also worked with databases on the thousands of tables done by a whole lot of programmers that has absolutely no primary keys, no relationships, and all database logic is in code. I can't change all these since the company makes an amazing amount of money from data contained therein.

  Right now I'm working on a Webforms Generator for ASP.Net 2.0 (AspxFormsGen), it's an OR mapper that generates asp.net 2.0 webforms based on the tables on your MS SQl 2005. I have to test for all legal fieldnames allowed by MS SQL 2005. Although I think having spaces for table names and field names is not a good programming practice, you have to look at it in a higher programming level.

  E.g. So why use underscores and all uppercase letters as names for your tables and fieldnames Maybe your company is planning to convert to Oracle in the next 10 years.

  Junnark

  
  

• JAAKARHU

  I'd just like to regurgitate the deja-vu. There's no such thing as a bug for bad programming practice.

  Adamus

  
  
  

• Lee321

  I'm guessing that your database table was created via something like: CREATE TABLE <table name>(... ,[Game Developer] nvarchar(<n>), ...)
  
  In this case, the square brackets are quote characters and are not considered part of the column name, although the space in the name means you must use quote characters whenever you reference it in TSQL. When you read data from the table into ADO.Net, the column name will come across as just "Game Developer" (no quotes), and that is the name to bind to. The HttpWebException about invalid data near nvarchar is probably a separate problem that had been hidden by the first problem.
  
  
  
• SqlDataSource/DataField Bug in 2.0 ?