Adding column problem

22 Oct 2005 2:12 AM

Karolus

I consulted the SQL2K help for adding a table and copied the code and
substituted my own variables, but I get an error message about incorrect
syntax regarding the AS in line 1. I'm a newbie and am bummed that the code
provided in the help doesn't work--I'm assuming there's something I don't
know here. Here's the code:
--------------------------------------------
Dim t_AssessmentItem As SQLDMO.Table

Dim OutputAssessmentLevelID As New SQLDMO.Column
OutputAssessmentLevelID.Name = "ShelfLife"
OutputAssessmentLevelID.Datatype = "CodeID"
OutputAssessmentLevelID.AllowNulls = False
OutputAssessmentLevelID.DRIDefault.Int = "AssessmentLevelID"

Set t_AssessmentItem = _
oSQLServer.Databases("PowerSuasionDevLocal").Tables("t_AssessmentItem")

t_AssessmentItem.BeginAlter

t_AssessmentItem.Columns.Add OutputAssessmentLevelID

t_AssessmentItem.DoAlter
---------------------------
Thanks for your help,
karolus

22 Oct 2005 3:06 PM

Dan Guzman

> OutputAssessmentLevelID.DRIDefault.Int = "AssessmentLevelID"

I don't see an Int property for the DRIDefault object. You might try
specifying a default constraint name and value instead. For example:

OutputAssessmentLevelID.DRIDefault.Name = "DF_t_AssessmentItem_ShelfLife"
OutputAssessmentLevelID.DRIDefault.Text = "0"

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Karolus" <Karo***@discussions.microsoft.com> wrote in message
news:DA68ACB7-098B-4282-92C6-23A24E024C1B@microsoft.com...
>I consulted the SQL2K help for adding a table and copied the code and
> substituted my own variables, but I get an error message about incorrect
> syntax regarding the AS in line 1. I'm a newbie and am bummed that the
> code
> provided in the help doesn't work--I'm assuming there's something I don't
> know here. Here's the code:
> --------------------------------------------
> Dim t_AssessmentItem As SQLDMO.Table
>
> Dim OutputAssessmentLevelID As New SQLDMO.Column
> OutputAssessmentLevelID.Name = "ShelfLife"
> OutputAssessmentLevelID.Datatype = "CodeID"
> OutputAssessmentLevelID.AllowNulls = False
> OutputAssessmentLevelID.DRIDefault.Int = "AssessmentLevelID"
>
> Set t_AssessmentItem = _
> oSQLServer.Databases("PowerSuasionDevLocal").Tables("t_AssessmentItem")
>
> t_AssessmentItem.BeginAlter
>
> t_AssessmentItem.Columns.Add OutputAssessmentLevelID
>
> t_AssessmentItem.DoAlter
> ---------------------------
> Thanks for your help,
> karolus

22 Oct 2005 4:26 PM

Chris2

Show quote

"Karolus" <Karo***@discussions.microsoft.com> wrote in message
news:DA68ACB7-098B-4282-92C6-23A24E024C1B@microsoft.com...
> I consulted the SQL2K help for adding a table and copied the code
and
> substituted my own variables, but I get an error message about
incorrect
> syntax regarding the AS in line 1. I'm a newbie and am bummed that
the code
> provided in the help doesn't work--I'm assuming there's something I
don't
> know here. Here's the code:
> --------------------------------------------
> Dim t_AssessmentItem As SQLDMO.Table
>
> Dim OutputAssessmentLevelID As New SQLDMO.Column
> OutputAssessmentLevelID.Name = "ShelfLife"
> OutputAssessmentLevelID.Datatype = "CodeID"
> OutputAssessmentLevelID.AllowNulls = False
> OutputAssessmentLevelID.DRIDefault.Int = "AssessmentLevelID"
>
> Set t_AssessmentItem = _
>

oSQLServer.Databases("PowerSuasionDevLocal").Tables("t_AssessmentItem"
)

>
> t_AssessmentItem.BeginAlter
>
> t_AssessmentItem.Columns.Add OutputAssessmentLevelID
>
> t_AssessmentItem.DoAlter
> ---------------------------
> Thanks for your help,
> karolus

Karolus,

You say your problem is about "incorrect syntax regard the AS in line
1".

It looks like you are using VB/VBA. Have you set a reference to the
"Microsoft SQLDMO Object Library"?

If you haven't, then "SQLDMO.Column" will not work.

Check:

VBA = Tools > References
VB = Project > References

Sincerely,

Chris O.