Default value in tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Default value in tables

Author

Topic

Lamune
Starting Member

3 Posts

Posted - 2010-06-11 : 14:48:54

Hello...

I'm not sure if this is something that can be done on the database or if the problem is on the web page, but I wanted to ask someone who actually knew what they were doing, because I don't.

We use an .aspx web page set up that lets a few of our staff add/edit an SQL2005 database as part of a simple online catalog system. I've noticed that if any value is left blank and a null entry is stored it can no longer be edited/deleted on the web page.

Manually entering a . in blank fields fixes that problem, so easy fix, I thought, was to just insert a default 'placeholder' value in the database. (Since I figured that would be easier than figuring out the web code.)

I've got '.' as a default value for the column (I think...) but it doesn't seem to apply. If I allow null values in the column a blank on the web page still inserts a null. If I set it to no null, it returns an error that "Cannot insert the value NULL into column 'Group', table 'RotatingCollection.dbo.Adult'; column does not allow nulls. INSERT fails."

Can I set a default on this column on the database, or the Insert method the web site is using going to make that not possible?

This is what I'm using now:

USE [RotatingCollection]
GO
/****** Object:  Table [dbo].[Adult]    Script Date: 06/11/2010 14:33:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Adult](
	[Author] [nvarchar](255) NULL DEFAULT ('.'),
) ON [PRIMARY]

(There are other tables, just removed them to shorten the copy.)
(Also used NOT NULL there, which just returns the error above.)

I've tried just right-clicking on the column in the SQL Management Studio and modifying the default value to '.' which adds a CONSTRAINT to the query, but that doesn't seem to make any difference either.

This looks like it should be really simple in the tutorials but I'm not getting it apparently.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-11 : 15:01:04

A default is only used if you tell it to use it or you exclude that column from the column list. So you will need to look at the code in order to determine if you can use a default value.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamune
Starting Member

3 Posts

Posted - 2010-06-11 : 15:16:14

Hurm. Ok.

I guess I'll just keep it at no null and have the two or three staff that have access to this catalog remember to fill in all the fields on the web page. That's going to be a lot better use of time than trying to figure out the web end of this. Works for me.

Thank you! At least I know I can stop prodding at the database now. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources