Solved

Does table exist

Posted on 2006-07-21
17
569 Views
Last Modified: 2008-01-09
Hi,

I am posting this in this section as I need to know how to respond to the condition, in ASP.NET

Basically all I want to do is , if a table exists in the Sql Database, then write "EXISTS!" to a span I have on the page

Thanks in advance

Andrew
0
Comment
Question by:REA_ANDREW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 17152743
Hi REA_ANDREW,

assuming your using SQL Server you can use this query to check if the table exists:

SELECT [Name] AS [Table Name]
FROM sysObjects
WHERE xType = 'U' AND [Name] = 'YourTableName'
ORDER BY [Name]

 and just check if something is returned

Apresto
0
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 250 total points
ID: 17152751
Hi REA_ANDREW,

Select Case When (Select Count(*) from dbo.sysobjects where name ='Users' and xtype = 'U') = 1 Then 'Exists' Else 'Doesn''t Exist' End As IstItThere

For example, tests for the existence of a table called Users.

Tim Cottee
0
 
LVL 20

Author Comment

by:REA_ANDREW
ID: 17152752
hi Apresto, thanks you are a reliable help.  

Can you confirm that I could use an ExecuteScalar() with this SQL String?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Accepted Solution

by:
apresto earned 250 total points
ID: 17152755
in c# sometinhg like this:

string tableName = "YourTable";
string sql = "SELECT count(*) as TableCount FROM sysObjects  WHERE xType = 'U' [Name] = " + tableName + "ORDER BY [Name]";

SqlCommand command = new SqlCommand( sql );
command.Connection = YourConnection;
...etc etc...

int tableCount = command.ExecuteScalar();

if ( tableCount > 0 )
{
   //EXISTS
}
0
 
LVL 20

Author Comment

by:REA_ANDREW
ID: 17152756
Aha I post to slow, thanks also TimCottee
0
 
LVL 23

Expert Comment

by:apresto
ID: 17152760
hi there, well spotted, if you are just trying to see if it exists you could just return a count and check its not 0 (obviously it wont ever be more than one either :o)
0
 
LVL 23

Expert Comment

by:apresto
ID: 17152763
apologies, didnt spot your post Tim Cottee - didnt mean to duplicate
0
 
LVL 20

Author Comment

by:REA_ANDREW
ID: 17152768
So how do I implement, the correct one in C#, i.e. is this the one to use?

Select Case When (Select Count(*) from dbo.sysobjects where name ='Users' and xtype = 'U') = 1 Then 'Exists' Else 'Doesn''t Exist' End As IstItThere

Andrew
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 17152770
apresto,

No problem.

Tim
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 17152775
REA_ANDREW,

Either really, all mine does is wrap the "Exists" into the sql statement so you can executescalar on it straight to your span. apresto showed how to use the version that simply returns the count to do the same thing.

Tim
0
 
LVL 20

Author Comment

by:REA_ANDREW
ID: 17152780
Thanks guys, Big Help
0
 
LVL 23

Expert Comment

by:apresto
ID: 17152786
Glad to have helped

Good Luck

Apresto
0
 
LVL 20

Author Comment

by:REA_ANDREW
ID: 17152791
Just one more quick one, is this a correct implemntation as I seem to remeber that a null reference is thrown in the execute scalar if nout is in there,  I like using the one line implementation, not sure of the name, but could either of you please confirm this is correct

int tableCount = command.ExecuteScalar() = null ? 0 : command.ExecuteScalar();

Thanks

Andrew
0
 
LVL 2

Expert Comment

by:SKumar_1981
ID: 17152795
Try this
Declare Isexist int
SELECT COUNT(*) [Name] AS [Table Name
FROM  information_schema.tables
WHERE xType = 'U' AND [Name] = 'YourTableName'
ORDER BY [Name])

if @Isexist >0
begin
      SET @Exists = 1
end
else
      SET @Exists=0

Regards,
skumar


Regards,
skumar
0
 
LVL 23

Expert Comment

by:apresto
ID: 17152797
because you are using a count() you will always get a value, a 0 if nothing is found
0
 
LVL 20

Author Comment

by:REA_ANDREW
ID: 17152800
Excellent thanks
0
 
LVL 23

Expert Comment

by:apresto
ID: 17152804
No Problemo :o)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question