|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
November 5th, 2009, 06:30 PM
|
|||
|
|||
|
JScript - Database - General - Drop Table IF Exist
How do I drop a table if exist using ASP Javascript. It is probably something simple but I have been staring at the monitor for awhile with out a solution.
 Buy a beer for whoever answers first.
|
|
#2
November 6th, 2009, 07:47 AM
|
||||
|
||||
|
Quote:
Hi, and welcome to the forums, what type of database are you using, as I think this will dictate the syntax of your query? heres an example using sqlserver.
|
|
#3
November 6th, 2009, 09:48 AM
|
|||
|
|||
|
Good morning sync_or_swim,
I looked the example previously and this what I came up with: [code] <!--- MS Access Connection ---> <!--#include file="Connections/access.asp"--> <% var conn, sql set conn = Server.CreateObject("ADODB.Command"); conn.ActiveConnection = MM_access_STRING; sql = "if EXISTS (SELECT * FROM MCN_Read) BEGIN DROP TABLE MCN_Read END"; conn.Execute %> [code] The error that I get back is "Command text was not set for the command object." Thank you ahead of time for your help on this.
|
|
#4
November 7th, 2009, 02:36 AM
|
||||||
|
||||||
|
You are not sql to the command object.
Code:
var conn, sql
set conn = Server.CreateObject("ADODB.Command");
conn.ActiveConnection = MM_access_STRING;
sql = "if EXISTS (SELECT * FROM MCN_Read) BEGIN DROP TABLE MCN_Read END";
conn.CommandText = sql
conn.CommandType=adCmdText
conn.Execute
http://www.asp101.com/articles/wrox/asp30/26100902.asp
|
|
#5
November 9th, 2009, 10:43 AM
|
|||
|
|||
|
@Sync_Or_System: MS Access right now before being transitioned to MS SQL 2005
@Micky: When I run the following code: var conn, sql set conn = Server.CreateObject("ADODB.Command"); conn.ActiveConnection = MM_access_STRING; sql = "if EXISTS (SELECT * FROM MCN_Read) BEGIN DROP TABLE MCN_Read END"; conn.CommandText = sql; conn.CommandType=adCmdText; conn.Execute; It comes up with the following error: 'adCmdText' is undefined When I did research in other forums and the stated that if I bracketed the table it would execute. No success. I tried to drop the conn.Command=adCmdText and tried to see what happens and I get this error: Invalid SQL State; Expected Delete, Insert, Procedure, select, or update Doing research so I don't sound like a dofus on this and for better understanding, the adCmdTxt is trying to evaluate CommandText (which in this case, "conn.CommandText = sql") has a textual definition of a command or stored procedure call. Am I correct? Or way of base?
|
|
#6
November 9th, 2009, 10:56 AM
|
|||
|
|||
|
Quote:
Code:
var conn, sql
set conn = Server.CreateObject("ADODB.Command");
conn.ActiveConnection = MM_access_STRING;
sql = "if EXISTS (SELECT * FROM MCN_Read) BEGIN DROP TABLE MCN_Read END";
conn.Execute sql
|
|
#7
November 9th, 2009, 11:10 AM
|
|||
|
|||
|
Still comes with the error:
"Command text was not set for the command object" Quote:
|
|
#8
November 10th, 2009, 02:17 AM
|
||||||
|
||||||
|
Quote:
You can have such code in your asp page to define its value. Code:
Dim adCmdText adCmdText = 1
|
|
#9
November 11th, 2009, 01:03 PM
|
|||
|
|||
Sorry that I did not reply sooner. Had Tropical Lynda going though my backyard.
 Micky's Version Code:
var conn, sql
set conn = Server.CreateObject("ADODB.Command");
conn.ActiveConnection = MM_access_STRING;
sql = "if EXISTS (SELECT * FROM MCN_Read) BEGIN DROP TABLE MCN_Read END";
conn.CommandText = sql
conn.CommandType=adCmdText
conn.Execute
Jonathan8146's Version Code:
var conn, sql
set conn = Server.CreateObject("ADODB.Command");
conn.ActiveConnection = MM_access_STRING;
sql = "if EXISTS (SELECT * FROM MCN_Read) BEGIN DROP TABLE MCN_Read END";
conn.Execute sql
Austin's version Code:
var adCmdText = 1
var conn, sql
conn = Server.CreateObject("ADODB.Command");
conn.ActiveConnection = MM_access_string;
sql = "if EXISTS (SELECT * FROM MCN_Read) BEGIN DROP TABLE MCN_Read END";
conn.CommandText = sql;
conn.CommandType = adCmdText;
conn.Execute;
My version get's the following error: Microsoft OLE DB Provider for ODBC Driver '8004e14' [Microsoft][ODBC Microsoft Access Driver] Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE','SELECT' or 'UPDATE'. The code stops on the conn.Execute. I tried to run the SQL statement in Access 2007 and came up with the same error. Am I trying to force Access something that it can not do?
|
|
#10
November 12th, 2009, 02:23 AM
|
||||||
|
||||||
|
I am not too sure if Access 2007 can do it.
Have a look at this link which has code to use IF EXISTS http://www.dmxzone.com/go?4615
|
|
#11
November 12th, 2009, 03:34 AM
|
|||
|
|||
|
Not sure if this is the best way to do this, but when using Access I check one of the "hidden/system" tables to see if a table exists
Code:
strSQL = "SELECT Name "_ & "FROM MSysObjects "_ & "WHERE NAME = 'tablename';"
|
|
#12
November 12th, 2009, 02:27 PM
|
|||
|
|||
|
Coming closure to a solution
Code:
<%
var sql =("Select * From MCN_Read")
var adCmdText = 1
var conn = Server.CreateObject("ADODB.Command")
conn.ActiveConnection = MM_access_STRING;
if (sql = true)
{
sql = "DROP TABLE_Read";
conn.CommandText = sql;
conn.CommandType = adCmdText;
conn.Execute;
}
else (sql = false)
{
sql = ("Select NULL")
conn.CommandText = sql;
conn.CommandType = adCmdText;
conn.Execute;
}
%>
It finds MCN_Read if exists and drops it. What I am having difficulty with now is if the MCN_Read doesn't exist, continue on with the execution of thre rest of the . The error code that I get back 'Table 'MCN_Read' does not exist.
|
|
#14
November 18th, 2009, 10:03 AM
|
|||
|
|||
|
Update
Code:
<%
var sql = "Select * From MCN_Read"
var adCmdText = 1
var conn = Server.CreateObject("ADODB.Command")
conn.ActiveConnection = MM_access_STRING;
conn.CommandText = sql;
conn.CommandType = adCmdText;
var oRS = conn.Execute;
if (!oRS.EOF)
{
sql = "DROP TABLE_Read";
conn.CommandText = sql;
conn.CommandType = adCmdText;
conn.Execute;
}
%>
The error that I receive is an MS Access error "The database engine could not lock table 'MCN_Read' because it is already in use by another person or process". Code stops to execute on line "conn.Execute;". I look forward to a response and I will continue search for a solution. My understanding is that EOF is locking up the table when it should be releasing it after the "conn.Execute". Am I correct or way off base?
|
|
#15
November 18th, 2009, 10:29 AM
|
|||
|
|||
|
Quote:
I think you may need to close the connection, i.e. oRS.Close, after checking if EOF
|
|
| Viewing: ASP Free Forums > Programming > ASP Development > JScript - Database - General - Drop Table IF Exist |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
