If table exists...

ASP Free Forums Sponsor:

April 23rd, 2004, 02:22 PM

shamrog12

Newton's Apple Wizard

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

If table exists...

Using ASP I'm trying to determine whether or not a table exists already. If it exists I want to send the user to another page. How can I determind if a table exists?

__________________
If you found a post of mine helpful, please click on the on my post to add to my reputation.

April 23rd, 2004, 02:27 PM

Memnoch

Unholy Moderator

Join Date: Oct 2003

Location: In hell, where did you think?

Posts: 11,781

Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470

In SQL Server

Code:

SELECT name
FROM sysObjects
WHERE name = Tablename

April 23rd, 2004, 02:32 PM

shamrog12

Newton's Apple Wizard

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

What kind of value does this return? If I am using ASP would I just create a connection and a recordset, then open the recordset with:

rs.Open "SELECT name FROM sysObjects WHERE name = Tablename", myConn

How will it notify me if it exists or not?

April 23rd, 2004, 02:36 PM

Memnoch

Unholy Moderator

Join Date: Oct 2003

Location: In hell, where did you think?

Posts: 11,781

Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470

try something like this

Code:

tableName = "MyTable"

Set rs = Conn.Execute("SELECT COUNT(*) As 'tableCount' FROM sysObjects WHERE name = '" & tableName & "'")

if rs("tableCount") <> 0 Then
   The table already exists
else
   The table doesn't exist
end if

April 23rd, 2004, 04:07 PM

shamrog12

Newton's Apple Wizard

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

Before I begin, thank you for your help. When I run this Subroutine I don't get to execute all the code because an error occurs and I have On Error Resume Next set for all the pages. This is the code I use to determine whether a table exists:

Code:

Sub endIfTableExists
	varTableName = CStr(Request.Form("tableName"))
	
	Dim conTestExist
	Dim rsTestExist
	'--Set the connection
	Set conTestExist = Server.CreateObject("ADODB.Connection")
	conTestExist.Open "myDSN"
	
	'--Set the recordset
	'Set rsTestExist = Server.CreateObject("ADODB.Recordset")
	'rsTestExist.Open "SELECT * FROM " & varTableName, conTestExist
	
	Set rsTestExist = conTestExist.Execute("SELECT COUNT(*) As 'tableCount' FROM sysObjects WHERE name = '" & varTableName & "'")

	if rsTestExist("tableCount") <> 0 Then
	   'The table already exists, send to this URL
	   Response.Redirect("categoryAlreadyExists.asp")
	end if

	Set rsTestExist = Nothing
	Set conTestExist = Nothing

End Sub

This is the error I get:

-2147217865
[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'sysObjects'. Make sure it exists and that its name is spelled correctly.

I don't know whether or not it matters that I'm using MySQL

April 23rd, 2004, 05:36 PM

sbaxter

Moderator: Access, SQL

Join Date: Oct 2003

Posts: 5,126

Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 13

Yes is does matter.

You will have to find which table (hidden) in mySQL all the tables names are stored in. I don't know

SQL Server uses sysObjects
MS Acess uses MSysObjects
mySQL uses ????

S-

April 23rd, 2004, 05:43 PM

shamrog12

Newton's Apple Wizard

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

Thanks for all your help. Much appreciated

April 23rd, 2004, 05:51 PM

shamrog12

Newton's Apple Wizard

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

I switched from MySQL to Access and used what you proposed. My issues are different now. The MSysObjects seems to be restricted from running me running

Code:

Set rsTestExist = conTestExist.Execute("SELECT COUNT(*) As 'tableCount' FROM MSysObjects WHERE name = '" & varTableName & "'")

against it.

Before this escapes your mind too much I wanted to ask about what to do with this Access error...

[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'

Any ideas?

April 23rd, 2004, 06:09 PM

shamrog12

Newton's Apple Wizard

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

I fixed that upon further reading. That was an Access issue. I now get:

3265
Item cannot be found in the collection corresponding to the requested name or ordinal.

When executing

Set rsTestExist = conTestExist.Execute("SELECT COUNT(*) AS 'tableCount' FROM MSysObjects WHERE NAME = '" & varTableName & "'")

I know that the value in varTableName is the same as the name of aan existing table.

#10

April 23rd, 2004, 06:50 PM

Memnoch

Unholy Moderator

Join Date: Oct 2003

Location: In hell, where did you think?

Posts: 11,781

Time spent in forums: 3 Weeks 5 Days 8 h 45 m 55 sec
Reputation Power: 470

try this. My original code was for Sql Server.

Code:

Set rsTestExist = conTestExist.Execute("SELECT COUNT(*) AS tableCount FROM MSysObjects WHERE [NAME] = [" & varTableName & "]")

#11

April 23rd, 2004, 08:33 PM

shamrog12

Newton's Apple Wizard

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

I guess we're getting closer. I now get:

-2147217904
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

#12

April 24th, 2004, 11:53 PM

shamrog12

Newton's Apple Wizard

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

I've been playing with this for a bit but can't get it to work still.

This is what I have:

Code:

Sub endIfTableExists
	varTableName = CStr(Request.Form("tableName"))
	
	Dim conTestExist
	Dim rsTestExist
	'--Set the connection
	Set conTestExist = Server.CreateObject("ADODB.Connection")
	conTestExist.Open "myDSN"
	
	Set rsTestExist = conTestExist.Execute("SELECT COUNT(*) AS tableCount FROM MSysObjects WHERE [NAME] = [" & varTableName & "]")

	if rsTestExist("tableCount") <> 0 Then
	   'The table already exists
	   Response.Redirect("categoryAlreadyExists.asp")
	end if

	Set rsTestExist = Nothing
	Set conTestExist = Nothing
	
End Sub

I still get error -2147217904

#13

April 25th, 2004, 12:54 AM

shamrog12

Newton's Apple Wizard

Join Date: Nov 2003

Location: Los Angeles

Posts: 1,661

Time spent in forums: 3 Weeks 2 Days 2 h 39 m 22 sec
Reputation Power: 35

Found a solution here:

No clue why it works though...

http://forums.devshed.com/showthread.php?t=137168

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode