Help w/ sql ALTER using asp 1

[twofive]

Hello, I rec'd help here earlier and need help with another MS-SQL Server 2k script (OSS experience only). I'm trying to add a column to an existing table using the SQL ALTER statement. Problem is, (1) the ALTER statement is not executing (or executing successfully), and (2) I have no VB skills to code it so that I get back a meaningful error message. Here's my code:

<%
on error resume next

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=sqloledb;Data Source=<server>;Initial Catalog=<dbname>;User Id=<dbuser>;Password=<dbpass>;"

Set RS = Conn.Execute("ALTER TABLE product_tmp ADD COLUMN prod_file_for_download varchar(100) NULL")

... ? Error check / Confirmation using Response.Write ? ...

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
%>

Thanks in advance!

 

[gmmastros]

You need to remove the word column

Set RS = Conn.Execute("ALTER TABLE product_tmp ADD [!]COLUMN[/!] prod_file_for_download varchar(100) NULL")


-George

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

 

[gmmastros]

And... To see the error messages, you can do this.

<%
on error resume next

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=sqloledb;Data Source=<server>;Initial Catalog=<dbname>;User Id=<dbuser>;Password=<dbpass>;"

Set RS = Conn.Execute("ALTER TABLE product_tmp ADD prod_file_for_download varchar(100) NULL")

[!]
if err.number <> 0 Then
    Response.Write(err.description)
End If
[/!]

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
%>

-George

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

 

[twofive]

Thanks gmmastros. COLUMN is optional in mysql--didn't know about ms-sql server 2k; just added out of habit. However, I removed COLUMN and the ALTER command still isn't doing anything If someone could spoonfeed me some vb error/output code around this statement that may help. I appreciate your courteous responses.

 

[twofive]

Woot! Thanks for the handy output. I've since gotten it to work!