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 John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help w/ sql ALTER using asp 1

Status
Not open for further replies.

twofive

Programmer
Jan 9, 2007
27
US
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:

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!
 
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
 
And... To see the error messages, you can do this.

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 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
 
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.
 
Woot! Thanks for the handy output. I've since gotten it to work!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top