NULL Values vs empty string vs space

How do I define a field to have the default value = ''. Not NULL but not a space either in SQL Server 2005?

Use the following in the field (default value) property

('')

Regards

|||I tried that and it didn't work.|||Just to get your point, did you mean that you want to place a default empty string?|||Smiling is correct, that is how you do it. When you say "it didn't work", perhaps you mean it didn't do what you wanted/expected it to, but that is how you define a default of a zero-length string (from within management studio).|||

That's what I thought too. I tried it again and it does work. I think what was going on is I had the table definition open and saved it but it really did not save. So I closed the defintion window and tried it and it works.

Thanks

|||Whats even more interesting it does NOT work when using the FormView control in ASP.NET 2.0 but if I enter the data directly into the table using the server explorer it does work.

???|||Does it show NULL value though?|||

YES, when viewing the data in the database server explorer the value is NULL even though the default value is ''.

Like I said entering data directly in the server explorer it works, when using a FormView control it does not. When using the formview all code is generated by VisualStudio.

Here is the SQLDatasource code generated by Visual Studio...

<asp:SqlDataSource ID="SqlDataSourceUserProfile" runat="server" ConnectionString="<%$ ConnectionStrings:AEISITConnectionString %>"
DeleteCommand="DELETE FROM [UserProfile] WHERE [UserProfile_RecID] = @.UserProfile_RecID"
InsertCommand="INSERT INTO [UserProfile] ([UserID], [UserRole], [FirstName], [LastName],, [CampusID], [DistrictID], [RegionID], [SessionID]) VALUES (@.UserID, @.UserRole, @.FirstName, @.LastName, @.Email, @.CampusID, @.DistrictID, @.RegionID, @.SessionID)"
SelectCommand="SELECT UserProfile_RecID, UserID, UserRole, FirstName, LastName, Email, CampusID, DistrictID, RegionID, SessionID FROM UserProfile WHERE (UserProfile_RecID = @.UserProfile_RecID)"
UpdateCommand="UPDATE [UserProfile] SET [UserID] = @.UserID, [UserRole] = @.UserRole, [FirstName] = @.FirstName, [LastName] = @.LastName, = @.Email, [CampusID] = @.CampusID, [DistrictID] = @.DistrictID, [RegionID] = @.RegionID, [SessionID] = @.SessionID WHERE [UserProfile_RecID] = @.UserProfile_RecID">
<DeleteParameters>
<asp:Parameter Name="UserProfile_RecID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="UserRole" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="CampusID" Type="String" />
<asp:Parameter Name="DistrictID" Type="String" />
<asp:Parameter Name="RegionID" Type="String" />
<asp:Parameter Name="SessionID" Type="String" />
<asp:Parameter Name="UserProfile_RecID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="UserListBox" Name="UserProfile_RecID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="UserID" Type="String" />
<asp:Parameter Name="UserRole" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="CampusID" Type="String" />
<asp:Parameter Name="DistrictID" Type="String" />
<asp:Parameter Name="RegionID" Type="String" />
<asp:Parameter Name="SessionID" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

|||

That would explain it.

If you want the default value on insert, remove all references to the field in the insertcommand. Alternatively, change the parameter's convertemptystringtonull property to false.

|||

Yes, whatMotley said is correct.

Just change the convertemptystringtonull property to false.