CODEWITHSUNDEEP
c#sqlsql-servervisual-studio-2012
Adnan Al-Husain
Adnan Al-Husain

Reputation: 110

Checking Table Existance: Table doesn't exist while it exists

public bool CheckTblExist(string TblName)
    {
        try
        {  
            string cmTxt = "select case when exists" 
            + "((select * from information_schema.tables " 
            + "where table_name = '" + TblName + "')) then 1 else 0 end";

            var cmd = new OdbcCommand(cmTxt);
            if ((int)cmd.ExecuteScalar() == 1) exists = true;
            MessageBox.Show(TblName + " table Exists.");
        }
        catch
        {
            exists = false;
            MessageBox.Show(TblName + " table does not Exist.");
        }
        return exists;
    }

Using VS2012,C# I've created manually mdf file named Tasoo1.mdf inside App_Data with connection named con. Tasoo.mdf already has 1 table named '1010' created using 

string cmdText = "CREATE TABLE [" + tblname + "]" 
                 + "(column_name1 int,column_name2 int,column_name3 int)";

Excauting the code, gave me- table doesn't exist? any idea how to solve this..thanks alot in advance.

Upvotes: 1

Views: 3306

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

var cmd = new OdbcCommand(cmTxt);

This command is never associated with a connection object of any kind (let alone an open one)

catch
    {
        exists = false;
        MessageBox.Show(TblName + " table does not Exist.");
    }

This code ignores whatever exception has occurred, and just reports that the table doesn't exist. If you'd actually specified a variable to contain the exception, and examined it, you'd have probably found out about the top issue I mentioned.

Even once you fix those, of course, your code (as written) shouldn't throw an exception anyway when a table doesn't exist. It will just have a different result value when you call ExecuteScalar.

With so many issues, this feels like a homework problem.

You can re-write the later part as:

if ((int)cmd.ExecuteScalar() == 1)
{
   MessageBox.Show(TblName + " table exists.");
   return true;
}
else
{
   MessageBox.Show(TblName + " table does not exist.");
   return false;
}

Upvotes: 2

Paul Mitchell
Paul Mitchell

Reputation: 3281

When you say it gives table doesn't exist, I assume you mean the second message box is showing? This is caused by an exception being thrown. You really need to catch the exception object being thrown and it will tell you what your problem is.

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176956

Easy way to try 

bool tabex;
try
{ 
    var cmd = new OdbcCommand(
      "select case when exists((select * from information_schema.tables where table_name = '" + tableName + "')) then 1 else 0 end");

    tabex= (int)cmd.ExecuteScalar() == 1;
}
catch
{
    try
    {
        tabex = true;
        var cmdOthers = new OdbcCommand("select 1 from " + tableName + " where 1 = 0");
        cmdOthers.ExecuteNonQuery();
    }
    catch
    {
        tabex = false;
    }
}

Check this also : Using C#, how do you check if a table exists in SQL?

Upvotes: 0

Habib
Habib

Reputation: 223402

You have only one statement under your if statement, i.e. exists = true. Your MessageBox.Show is outside the if statement. So even if the if statement returns false, you will always get the message that the table exists. Enclose it in {}. 

if ((int)cmd.ExecuteScalar() == 1) 
{    
     exists = true;
     MessageBox.Show(TblName + " table Exists.");
}

Your current code:

if ((int)cmd.ExecuteScalar() == 1) exists = true;
MessageBox.Show(TblName + " table Exists."); // this is irrespective of the if

Excauting the code, gave me- table doesn't exist?

You are showing that in your catch block. That means you are getting some exception. Its not a good idea to have an empty catch block. Catch the exception and see what is going wrong. 

catch(SqlException ex)
{
   MessageBox.Show(ex.Message);
   //handle exception
}

Upvotes: 2

Related Questions