Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
Jerry
I have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:

> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
JerryI have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:
> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

Changing field length changes AllowNulls=True

(If this is in the wrong newsgroup, let me know and I'll move it.)
Using SQLDMO from a VB app to modify an existing field in a SQL 2000
database.
All the fields in my database have AllowNulls = False.
After changing a field size in the manner below, I look at the table in
Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
Here's the code:
Dim oTable As sqldmo.Table
Dim oColumn As New sqldmo.Column
'skip the Set statements..
oTable.BeginAlter
oColumn.Length = pSize
oTable.DoAlter
I do not believe the version of SQLDMO matters as this happens on older as
well as newer versions.
Any ideas?
Thanks,
JerryI have not used DMO objects much, but in scripting when altering a field
size, if you don't specify not null in the alter it defaults to null. So in
your DMO could maybe set the field to not null before updating it.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jerry J" wrote:

> (If this is in the wrong newsgroup, let me know and I'll move it.)
> Using SQLDMO from a VB app to modify an existing field in a SQL 2000
> database.
> All the fields in my database have AllowNulls = False.
> After changing a field size in the manner below, I look at the table in
> Enterprise Manager and Allow Nulls is now True! This is fully repeatable.
> Here's the code:
> Dim oTable As sqldmo.Table
> Dim oColumn As New sqldmo.Column
> 'skip the Set statements..
> oTable.BeginAlter
> oColumn.Length = pSize
> oTable.DoAlter
> I do not believe the version of SQLDMO matters as this happens on older as
> well as newer versions.
> Any ideas?
> Thanks,
> Jerry
>
>

Changing Database Structures Through Code

I am having a hard time finding materials on this subject. I am guessing I am using the wrong keywords to search. Basically, I want to be able to modify database tables through a web form. They can add columns and delete columns through the form. I would just want to default the type of column and the length. I am sure it has been done, I was just wondering if anyone had some resources they could throw my way. I would appreciate it. Thanks.

Hi, I wrote a stored procedure to alter table, share with you:)

create proc sp_alterTbl @.TblName sysname,@.ColName sysname,@.ColType varchar(50)=null,
@.ColLen int=null,@.Op varchar(6)='Add'
as
begin
declare @.cmd varchar(500),@.TypeLenStr varchar(50)
if object_id(@.TblName) is null
Raiserror('Table does not exist!',16,1)
else
if not exists (select * from systypes where name= @.ColType)
Raiserror('Type does not exists!',16,1)
else
begin

select @.TypeLenStr=CASE @.ColLen WHEN null Then ' '
Else '('+convert(varchar(6),@.ColLen)+')'
END
select @.cmd= CASE @.Op when 'Add' Then 'alter table'+@.TblName+''+@.Op+''+@.ColName+' '
+@.ColType+ @.TypeLenStr
WHEN 'Drop' Then 'alter table'+@.TblName+''+@.Op+''+@.ColName+' '
END
EXEC(@.cmd)
END
END