Stringbuilder with ALTER TABLE 1

[Lladros]

This maybe a stupid questions, but I need to ask.......
I am trying to use a string builder to alter, update a table in sql server 2000. On sql server this statement works:
ALTER TABLE lessons ADD oldId int DEFAULT(0)
GO
However, when I insert that into my string builder It gives me this error:
Incorrect syntax near 'GO'.
If I take out the GO, this is the error:
Invalid column name 'oldId'. Invalid column name 'oldId'. Invalid column name 'oldId'. Invalid column name 'oldId'.

Any help????

 

[chrissie1]

Code please.

Christiaan Baes
Belgium

My Blog

 

[Lladros]

Sorry!

Dim sb as New StringBuilder

sb.Append("ALTER TABLE lessons ADD old_Id int DEFAULT(0) ")
sbSQL.Append("GO ")
sbSQL.Append("UPDATE lessons SET old_Id = lesson_Id WHERE lesson_schlyr = ").Append(intPrevYr)
sbSQL.Append("INSERT INTO lessons (lesson_title, lesson_desc, lesson_inst, lesson_schlyr, old_Id) ")
sbSQL.Append("SELECT lesson_title, lesson_desc, lesson_inst, ").Append(intSchlYr).Append(", old_Id FROM lessons ")
sbSQL.Append("WHERE lesson_schlyr = ").Append(intPrevYr)
sbSQL.Append(" INSERT INTO assignments (assign_title, assign_text, assign_lesson_id, assign_schlyr, old_Id) ")
sbSQL.Append("SELECT assign_title, assign_text, assign_lesson_id , ").Append(intSchlYr).Append(", old_Id FROM lessons ")
sbSQL.Append("WHERE assign_schlyr = ").Append(intPrevYr)
sbSQL.Append(" UPDATE assign SET assignments.assign_lesson_id = lessons.lesson_id ")
sbSQL.Append("FROM assignments INNER JOIN lesson ON assignments.assign_less_id = lessons.old_Id ")
sbSQL.Append("WHERE assignments.assign_schlyr = ").Append(intSchlYr).Append(" AND lessons.lesson_schlyr = ").Append(intSchlYr)

 

[chrissie1]

I see a stringbuilder called sb and then you use sbSQL.

And how do you plan on executing these?

You should execute them all apart but in a transaction.



Christiaan Baes
Belgium

My Blog