How to create dynamic table/ fields?

[necta]

I want to create dynamic table and its fileds by getting the field name from request.form. Can anyone show me how?
Thanks

 

[tlhawkins]

Hey Necta,

Here is the details on the Create Table statement (Jet SQL)

CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1]
[, field2 type [(size)] [NOT NULL] [index2] [, ...]]
[, CONSTRAINT multifieldindex [, ...]])

So a simple example would be:

CREATE TABLE Contact (FirstName TEXT (20), LastName TEXT (20) NOT NULL, Address TEXT (20))

to create the field names from a Form would be as simple as adding the form field content into the SQL.

SQL = "CREATE TABLE TableName (" & request.form("Field1") & " TEXT (20)," & request.form("Field2") & " TEXT (20))"

I must add that I don't recommend allowing people to create tables in your database... but I'm sure you've got a good reason.

have fun.

Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

 

[necta]

Thanks Thawkins. Its meant for the system administrator only..
Compare the two code snippets below, the 'NOT NULL' present in the first but the second one. Any difference between them?

CREATE TABLE Contact (FirstName TEXT (20), LastName TEXT (20) NOT NULL, Address TEXT (20))

SQL = "CREATE TABLE TableName (" & request.form("Field1") & " TEXT (20)," & request.form("Field2") & " TEXT (20))"

 

[tlhawkins]

The "Not Null" is an optional switch that will allow or disallow a field to be left empty. Sometimes you want it, sometimes you don't. I was just trying to show some options. For each field you have basically 3 options. Type, Size, and if Null values are allowed. By default Null fields are allowed. If you define it as NOT NULL then obviously they are not allowed.

-Have fun.

Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

 

[necta]

Hi, Tlhawkins
Thanks for the tips, now I can create table with field. However, is it posssible to add subsequent field and to delete a field from the created table? How do I do that?
Thanks.


Necta

 

[tlhawkins]

Sure,

use:

ALTER TABLE TableName ADD COLUMN ColumnName Type

and:

ALTER TABLE TableName DROP COLUMN ColumnName



Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

 

[necta]

Hi Travis,

"ALTER Table Subject ADD COLUMN ( " & request.form("subjID") & " text (20))"
The above code contains syntax error:
"Syntax error in field definition"
What I have done wrong?

another question:
How do I detect if a table already exist?

Thanks again, Travis

Necta

 

[tlhawkins]

Syntax error is probably because of extra ( ) in the SQL
try:
"ALTER Table Subject ADD COLUMN " & request.form("subjID") & " TEXT (20)"

As far as checking for an existing Table you should be able to use this:

SELECT MSysObjects.Name FROM MSysObjects
WHERE (((MSysObjects.Flags)=0) AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name

That SQL will return an alphabetized list of tables in your database... then just check for the one you're looking for. Or Add into the query to look specifically for the one you want and then check what was returned.

-have fun


Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

 

[necta]

Thanks for the tips but hv the following errors:

When drop column:
The database engine could not lock table 'Subject' because it is already in use by another person or process.

When check if table exist:
Record(s) cannot be read; no read permission on 'MSysObjects'.

Where should i correct the errors?

Thanks Travis

Necta

 

[tlhawkins]

Hmm...
I guess I'm going to finally have to ask.

What database are you using?

'MSysObjects' is only available in Access i think. Other databases do it differently.

Some systems are setup to not allow deletes (which includes table drops), so you may have to develop a means of marking a table as deleted...

Both problems could be caused by permissions being set incorrectly.

so let me know what database you're using and I'll see if I can help.

Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

 

[necta]

I am using Access as my database. Tha drop can be executed now but not the MSysObject.

 

[tlhawkins]

Hey,

Sorry it took me so long to get back on this:

Here is another means to the same end. I'm sure it works on Access.

<%
  ' Create a connection object
  Set Conn = Server.CreateObject ("ADODB.Connection")

  ' Open the connection to the database. I use a system DSN here, but
  ' you can use whatever method you wish
  Conn.Open "DSN=Library"

  ' Open the database schema to query the list of tables. Extract the
  ' list in a Recordset object
  Set Rs = Conn.OpenSchema (adSchemaTables)

  ' Loop through the list and print the table names
  Do While Not Rs.EOF
    Response.Write "<BR>" & Rs ("TABLE_NAME")
    Rs.MoveNext
  Loop

  ' Close and destroy the recordset and connection objects
  loRs.Close
  Set loRs = Nothing

  loConn.Close
  Set loConn = Nothing
%>

I found this code in a library someplace. So a big shout of thanks to the original author!

Connect to your database and then add in the above code... it should list all of the tables and other objects in your database.

Hope it helps.


Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com