add a new table to the database or add a new column to an existing table.

[GLT]

Hey guys!

I was wondering if anyone could help me. I am a student on my work placement year and I am developing a web based database. I have been asked to design it so users can if need be add a new table to the database or add a new column to an existing table. I have never done this before, how would I go about doing this?? I was thinking that I would have to use some kind of stored procedure and a form the users could fill in? but I have no idea where to start.

I am using Dreamweaver MX and Access, I will be using SQL Server but I am using Access at the minute to create a practice dummy site.

Thanks!
Any ideas would be greatly appreciated!

GLT

(I have asked this same question in the ASP forum but getting no responses)

[peter_budo]

Create table

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
)

The problem that you facing here is

1. Table is always same so you just run your prepared statement
2. Tables can be different, you must provide a way/interface through which user can declare table name, column types, you just add the magic words CREATE TABLE and then execute it

Add column

ALTER TABLE table_name 
ADD column_name datatype

Remove column

ALTER TABLE table_name 
DROP COLUMN column_name

[GLT]

Hi thanks for replying!

I do know the SQL for creating and altering tables but I was wondering if anyone had any idea how I would get information from a form or something in dreamweaver to then create the table. The users dont know much about programming or database's so I was hoping to make a simple form or something for them to fill out. Is this possible??

ideas would be greatly appreciated!
Thanks!
GLT

[peter_budo]

Ofcourse it is possible, you just have to tell us which technology you plannig to use. PHP/JSP/ASP/ Ruby on Rails? Also so proper scenario to explain situation would help.

[GLT]

Thank you,

I'm using ASP with SQL Server 2005. The web based database that I am developing will hold details of the companies products and customers, their orders and enquiries. I have developed a database structure for this but I am only here for a few more months (I am a student on placement), the company want me to include a way so they can add new columns to existing tables if they need to store extra info and so they can add new tables if need be.

GLT

[peter_budo]

For ASP development I would recommend to use Visual Studio, that you can get for free from Microsoft website .

Back to your project...
You been given task that is nearly extensive as final year project. I would recommend you have look on few SQL Browser interfaces such as phpMyadmin, MySQL Query Browser, SQL Server Management Studio Express or any other so you get idea what you need to provide for user.
Do not go extensive on it, given them just basic options so they do not mess up anything. You should just offer option to select in which database new table should be created, text field for table name, then text fields for column names (make sure that table and variables names appropriate not special characters such as comas, dots, slash etc), drop down options for variable types (just work out what are most common variable types they use, you do not need all of them) and possible initial values (for this I would recommend you have good look at phpMyadmin).
Adding new collumn approach is somewhat similar to creating new table, but this time you have to select database and table.

[GLT]

Hi, Thanks for you help!

This is what ive got so far... it may be completely wrong but am I on the right lines??

stored procedure:-

CREATE PROCEDURE sp_createtable	
AS
CREATE TABLE @txttablename
	(
@txtcolumn1 adText adParamInput txtcolumn1,
@txtselect1 adText adParamInput txtselect1,
@txtcolumn2 adText adParamInput txtcolumn2,
@txtselect2 adText adParamInput txtselect2,
@txtcolumn3 adText adParamInput txtcolumn3,
@txtselect3 adText adParamInput txtselect3,
	)

asp:-

' Declare variables
<%
Dim cmdcreatetable
Dim txttablename
Dim txtcolumn1
Dim txtselect1
Dim txtcolumn2
Dim txtselect2
Dim txtcolumn3
Dim txtselect3

' Get values from form
txttablename = Request.Form("txttablename")
txtcolumn1 = Request.Form("txtcolumn1")
txtselect1 = Request.Form("txtselect1")
txtcolumn2 = Request.Form("txtcolumn2")
txtselect2 = Request.Form("txtselect2")
txtcolumn3 = Request.Form("txtcolumn3")
txtselect3 = Request.Form("txtselect3")

' Set Command
Set cmdcreatetable = Server.CreatObject("ADODB.Command")
cmdcreatetable.ActiveConnection = strConn
cmdcreatetable.CommandText = sp_createtable
cmdcreatetable.CommandType = adCmdStoredProc

' Add Parameters
cmdcreatetable.Parameters.Append.CreateParameter("@txttablename", adText, adParamInput, txttablename)
cmdcreatetable.Parameters.Append.CreateParameter("@txtcolumn1", adText, adParamInput, txtcolumn1)
cmdcreatetable.Parameters.Append.CreateParameter("@txtselect1", adText, adParamInput, txtselect1)
cmdcreatetable.Parameters.Append.CreateParameter("@txtcolumn2", adText, adParamInput, txtcolumn2)
cmdcreatetable.Parameters.Append.CreateParameter("@txtselect2", adText, adParamInput, txtselect2)
cmdcreatetable.Parameters.Append.CreateParameter("@txtcolumn3", adText, adParamInput, txtcolumn3)
cmdcreatetable.Parameters.Append.CreateParameter("@txtselect3", adText, adParamInput, txtselect3)

' Execute the command
cmdcreatetable.Execute
%>

SQL Server doesnt like the @ symbol but I dont know how else to do this.
the asp code - the select text boxes are for the user to input datatype.

Thanks for all you help! all ideas greatly appreciated!

GLT

[GLT]

Ive been having aply with my code and trying to find information on this. It looks like I have to use dynamic SQL which I have never used before. Here is the SQL code I have now:-

CREATE PROCEDURE sp_createtable
(
@txttablename as varchar(50),
@column1 as varchar(50),
@select1 as varchar (50),
@column2 as varchar(50),
@select2 as varchar(50),
@column3 as varchar(50),
@select3 as varchar(50)
)
AS
DECLARE @SQL varchar(2000)
SELECT @SQL = 'create Table' + @txttablename+')'
SELECT @SQL = @SQL + 'ID identity NOT NULL PRIMARY KEY, @txtcolumn1 @txtselect1, @txtcolumn2 @txtselect2, @txtcolumn3 @txtselect3)'
Exec(@SQL)

is this right? my SQL Server is down at the minute so I cant test it. I am trying to insert the tablename, column names and datatype from as asp form.

ideas would be great!
Thanks GLT

[peter_budo]

Sorry I can't advice in regards of last 2 posts as I'm more Java and MySQL oriented. The SQL statement that you wrote is different from what I normaly use. Currently I'm also looking into C#, maybe you will find useful chap.21&22 from C# for Programmers, Second Edition that is from Deitel Developer Series

I can discuse designer issues in regards of your project, however for practical problem solving you better post your questions in ASP.NET section of this forum