Top ASP 101 Stories An Overview of ASP.NET Connections, Commands, And Procedures What is ASP? QUICK TIP: Basic Debugging in Classic ASP Show All Tips >>  ASP 101 Updates

Quick Tips

Did you know that your ASP scripts can do a lot more then just retreive data from your database? Writing a page that will create or delete tables is really no harder then writing a page to run a query.

You need to make sure you've got the appropriate permissions, but, assuming you do, you can easily create, delete, or modify tables in your database. Take a look at this sample code:

<%
Dim strSQL
Dim cnnTest
strSQL = strSQL & "CREATE TABLE tblTest " & vbCrLf
strSQL = strSQL & "(" & vbCrLf
strSQL = strSQL & "ID int IDENTITY NOT NULL " & vbCrLf
strSQL = strSQL & "  constraint PK_tblTestID PRIMARY KEY, " & vbCrLf
strSQL = strSQL & "FirstName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "LastName varchar (30) NOT NULL, " & vbCrLf
strSQL = strSQL & "DateOfBirth datetime NULL" & vbCrLf
strSQL = strSQL & ")" & vbCrLf
Set cnnTest = Server.CreateObject("ADODB.Connection")
cnnTest.Open "Provider=SQLOLEDB;Data Source=localhost;" _
     & "Initial Catalog=test;User Id=sa;Password=;" _
     & "Connect Timeout=15;Network Library=dbmssocn;"
cnnTest.Execute strSQL
cnnTest.Close
Set cnnTest = Nothing
%>

The magic is all in the SQL... besides that it looks just like any other DB-based ASP file. The next time you're looking for a way to make a small change to your database, this type of thing might just do the trick. Be careful though... with all this power, one wrong step and you could lose your tables and their data.

That's the problem with computers... they tend to do what you tell them to, not necessarily what you want them to! ;)

If you have a tip you would like to submit, please send it to: webmaster@asp101.com.