How to check the table name (dsn) exist

[bataraguru]

Hi..
I want to check the table in my database exist before I create a new one.
How can I do that? And how can i print out the table name using query??
Thanks,
- zaidi -

------
<CFQUERY NAME=&quot;create_table&quot; DATASOURCE=&quot;car_select&quot;>
-->

I want to check if the table name exist or not?
If exist the table name will increase 1 (+1)
If not exist, will create a same name table name.

-->
CREATE TABLE temptbl_#GetCountID.TotalCount#
(
ID INTEGER CONSTRAINT ID PRIMARY KEY,
carmake TEXT,
carmodel TEXT,
class TEXT,
powersteering TEXT,
gear TEXT,
capacity TEXT,
budgetMinimum INTEGER,
budgetMaximum INTEGER,
condition TEXT,
link TEXT);
</CFQUERY>

 

[Sarky78]

If you put a query that is something like this:

SELECT *
FROM yourtablename1

inside of a CFTRY tag if the table doesn't exist then a error will be created. this error can be caught by CFCATCH to catch the error. If an error is created then the table does not exist and you are safe to create it using the code that you have above.

so your code would look something like:

<CFTRY>
<CFQUERY datasource=&quot;car_select&quot; name=&quot;check&quot;>
SELECT *
FROM yourtable1
</CFQUERY>
<CFCATCH TYPE=&quot;Any&quot;>
<CFSET DBError=1>
</CFCATCH>
</CFTRY>

<CFIF isDefined('DBError') IS &quot;Yes&quot;>
Create your table here
<CFELSE>
increment the table name
do the check again
</cfif>

you could put the above in a loop to check for a condition to save doing endless if statements.

hope this helps

 

[pigsie]

IF you are using SQL server (and in access) I believe there are some system tables which hold the name of all tables in the database but I cant remember the table names. It should be noted that using access you should avoid causing intentional errors where possible as it can lead to a corrupted database!

 

[Sarky78]

CFDude you are right you can get the table information that way i had forgotten about that. the table is MsysObjects(in access anyway), but its only accessiable if certain options are on !

in options go to tools -> options -> View

click to turn on hidden and system objects and hey presto a new lot of tables appear.

to access the information about the user defined tables you can use this query:

SELECT MSysObjects.Name FROM MsysObjects
WHERE (Left$([Name],1)<>'~')
AND (Left$([Name],4) <> 'Msys')
AND (MSysObjects.Type)=1
ORDER BY MSysObjects.Name;

I got this query from a custom tag, sorry but i can't remember the name of it or the author !

hope this helps you both !

 

[bataraguru]

hmmm..
how can i display the table name?

etc:
<cfoutput query=&quot;TableName&quot;>
#TableName#
</cfoutput>

thanks...

 

[Sarky78]

close, this is the actual code to output the names of all the tables in your database.

<cfoutput query=&quot;tablename&quot;>
#name#
</cfoutput>

if you have a look in the table mentioned above you will be able to see the other information that is available to you !

 

[bataraguru]

thanks foy your help, but it is not working..
I still trying to find a code that can check whether my table name exist or not.

Any body have any links to an expert websites about this??

Thanks in advanced..
- zack -

 

[Sarky78]

Can you post your code up here so we can have a look with any error message that you are receiving

 

[Silvano]

This tag will give you all the information about your MSAccess database. It will tell you about tables,queries,reports,forms,modules and macros residing inside your database...

http://devex.macromedia.com/develop...6EAA32-24FA-11D5-83EF00508B94F85A&method=Full

Sylvano
dsylvano@hotmail.com