Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stringbuilder with ALTER TABLE 1

Status
Not open for further replies.

Lladros

Programmer
Feb 21, 2008
18
US
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????
 
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)
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top