ALTER a table/field using an ASP page 2

[riley3]

Hello,
I have a question about the syntax using an ASP page to alter a field in a SQL2K table. My code looks like this;

strSQL = "ALTER TABLE press "
strSQL = strSQL & "MODIFY COLUMN rdesc VARCHAR(512) NULL "

I get this error message when I attempt to run the code by launching the page in a browser is as follows;

Incorrect syntax near the keyword 'COLUMN'.

The field is 456 and I want to increase it to 512. I've tried many different ways but no luck. I can add a field and drop a field but cannot seem to modify one. Thank you for any help you might be able to give me.
Riley

 

[travisbrown]

You don't need the word COLUMN in the SQL, just the column name

MODIFY rdesc VARCHAR(512) NULL

 

[DotNetGnat]

shouldnt it be this:

strSQL = "ALTER TABLE press "
strSQL = strSQL & "ALTER COLUMN rdesc VARCHAR(512) NULL "

-DNG

 

[PGoRule]

The syntax for modify a column says...
ALTER TABLE table_name MODIFY column_name column_type;

Sharing the best from my side...

--Prashant--

 

[riley3]

Thanks for the quick responses. I tried several different suggestions and ended up with a new error message. I've added some additional code from my asp page. Maybe the SQL is right and I'm net executing correctly.
=========================================================
strSQL = "ALTER TABLE press "
strSQL = strSQL & "ALTER COLUMN rdesc VARCHAR (5012) NULL "

rsNCCAA.CursorType = 2
rsNCCAA.LockType = 3

rsNCCAA.Open strSQL, adoCon

rsNCCAA.Update
=========================================================
Error message = Operation is not allowed when the object is closed.
=========================================================
Thanks, Riley

 

[gmmastros]

This sql command does not return records, so you shouldn't be using a recordset object to execute it. Instead, use the connection object's .Execute function.

[tt][blue]
strSQL = "ALTER TABLE press "
strSQL = strSQL & "ALTER COLUMN rdesc VARCHAR (5012) NULL "

ConnectionObject.Execute(strSQL)
[/blue][/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[riley3]

That got it - thanks for the help. Riley

 

[gmmastros]

You're welcome.

DotNetGnat provided the sql command. He should probably be thanked also.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetGnat]

Thanks George. Your recent posts in SQL Server forum have been really great and very helpful. I am not being very frequent to the forums these days(got married recently ) but whenever i visit tek-tips i try to read all your replies and learn something new from them...

thanks

-DNG