alter table add column through Dreamweaver ... need to get this to work ASAP! :(

[GLT]

Hi,

Im trying to work out a way for users to be able to add new columns to tables in an SQL Server database through Dreamweaver.

I have tried using stored procedures but i keep getting errors. The user will choose which table they wish to add a column then give that column a name and datatype. I just cant get it to work so i tried to go back to basics and not use stored procedures but just code the SQL stating the tablename, columnname and datatype. This seems to work :-

<%
set addcolumn = Server.CreateObject("ADODB.Command")
addcolumn.ActiveConnection = MM_Silverwingdatabase_STRING
addcolumn.CommandText = "ALTER TABLE dbo.customer ADD newcolumn VarChar(50)"
addcolumn.Execute()

%>

how can I change this so the user can choose the table and then give the column a name and datatype? or even my next step... how can i change this so the user can just enter the tablename?

any ideas would be great!!!!! Ive been working on this for a long time and need to get it to work ASAP!

Thanks!
GLT

[GLT]

Sorted it myself! well... the table bit anyway but i think the rest will be quite straight forward now. I'll post this so anyone else who is having this problem can solve it.

You have to pass the variables from a form on another page like you would creating search page and results page.

code for form:-

<form name="form1" method="post" action="TESTaddcolumn.asp">
                <table width="50%"  border="1">
                  <tr>
                    <td>Table Name </td>
                    <td><input type="text" name="texttablename"></td>
                    <td><input type="submit" name="insert" value="insert"></td>
                  </tr>
                </table>
              </form>

form action points to the page with the asp command and stored procedure.

code for command:-

<%

Dim cmd_addcolumnTEST__tablename
cmd_addcolumnTEST__tablename = "1"
if(Request("texttablename") <> "") then cmd_addcolumnTEST__tablename = Request("texttablename")

%>
<%

set cmd_addcolumnTEST = Server.CreateObject("ADODB.Command")
cmd_addcolumnTEST.ActiveConnection = MM_Silverwingdatabase_STRING
cmd_addcolumnTEST.CommandText = "dbo.sp_addcolumnTEST"
cmd_addcolumnTEST.CommandType = 4
cmd_addcolumnTEST.CommandTimeout = 0
cmd_addcolumnTEST.Prepared = true
cmd_addcolumnTEST.Parameters.Append cmd_addcolumnTEST.CreateParameter("@RETURN_VALUE", 3, 4)
cmd_addcolumnTEST.Parameters.Append cmd_addcolumnTEST.CreateParameter("@tablename", 200, 1,50,cmd_addcolumnTEST__tablename)
cmd_addcolumnTEST.Execute()

There may be other ways of doing this but after much frustration i found this way worked.