Create Database

[sonia sardana]

hey frnds, I m creating the database at page load,I m able to do dat.I just want to do check that if the table is already existing,then do not create the table.below code create table-

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient; 

public partial class _Default : System.Web.UI.Page 
{
    SqlCommand cmd;
    string Query;
    SqlConnection conn;

       protected void Page_Load(object sender, EventArgs e)
    {
           try
           {
             if (!IsPostBack)
             {
             OpenSQLConnection();
             Query = "USE sonia";
             cmd=new SqlCommand (Query ,conn) ;
             cmd.ExecuteNonQuery ();
             Query = "CREATE TABLE EmpInfo(CompCode VARCHAR(100),CompName VARCHAR(100),Address VARCHAR(100),ESINo VARCHAR(100),RegNo VARCHAR(100),TDS VARCHAR(100),Pan1 VARCHAR(100),Pan2 VARCHAR(100),RCN VARCHAR(100),ECN VARCHAR(100),PFRegNo VARCHAR(100),PFGrpCode VARCHAR(100),TAN VARCHAR(100),Sonof VARCHAR(100),FiscalID VARCHAR(100),Phone VARCHAR(100),EMail VARCHAR(100),Website VARCHAR(100))";
             cmd = new SqlCommand(Query,conn);
             cmd.ExecuteNonQuery();
             }
           }
           catch (Exception  ex)
           {
               lblStatus .Text =ex.Message .ToString ();
           }
    }

    private void OpenSQLConnection()
    {
        try
        {
            conn = new SqlConnection("Data Source=SONIA-B408A4159\\SQLEXPRESS;Initial catalog=sonia;Integrated Security=true;");
            conn.Open();
        }
        catch (Exception ex)
        {
            lblStatus.Text = ex.Message.ToString();
        }

    }
}

[sknake]

Sonia: This is an SQL question you happen to have the query embedded in an ASP.NET application. Please post these question to the SQL forums in the future.

To answer your question:

IF OBJECT_ID('TestTable123', 'U') IS NULL
CREATE TABLE TestTable123
(
  Field varchar(10)
)

OBJECT_ID('<name>', 'U') returns the table id. If the value is null the table does not exist. 'U' is for table, 'FN' for function, etc.

[sonia sardana]

soory from the next time i rember,But sir its not working.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient; 

public partial class _Default : System.Web.UI.Page 
{
    SqlCommand cmd;
    string Query;
    SqlConnection conn;

       protected void Page_Load(object sender, EventArgs e)
    {
           try
           {
             if (!IsPostBack)
             {
             OpenSQLConnection();
             Query = "USE sonia";
             cmd=new SqlCommand (Query ,conn) ;
             cmd.ExecuteNonQuery ();
               IF (OBJECT_ID('EmpInfo','U') is null )
                  {
                   Query = "CREATE TABLE EmpInfo(CompCode VARCHAR(100),CompName VARCHAR(100),Address VARCHAR(100),ESINo VARCHAR(100),RegNo VARCHAR(100),TDS VARCHAR(100),Pan1 VARCHAR(100),Pan2 VARCHAR(100),RCN VARCHAR(100),ECN VARCHAR(100),PFRegNo VARCHAR(100),PFGrpCode VARCHAR(100),TAN VARCHAR(100),Sonof VARCHAR(100),FiscalID VARCHAR(100),Phone VARCHAR(100),EMail VARCHAR(100),Website VARCHAR(100))";
                    cmd = new SqlCommand(Query,conn);
                    cmd.ExecuteNonQuery();
                 }
             }
           }
           catch (Exception  ex)
           {
               lblStatus .Text =ex.Message .ToString ();
           }
    }

    private void OpenSQLConnection()
    {
        try
        {
            conn = new SqlConnection("Data Source=SONIA-B408A4159\\SQLEXPRESS;Initial catalog=sonia;Integrated Security=true;");
            conn.Open();
        }
        catch (Exception ex)
        {
            lblStatus.Text = ex.Message.ToString();
        }

    }
}

ERROR- Too many characters in character literal int this line ( IF (OBJECT_ID('EmpInfo','U') is null ))

[sknake]

Because OBJECT_ID should be part of the query, not C# code.

Query = "IF OBJECT_ID('EmpInfo', 'U') IS NULL CREATE TABLE EmpInfo(CompCode VARCHAR(100),CompName VARCHAR(100),Address VARCHAR(100),ESINo VARCHAR(100),RegNo VARCHAR(100),TDS VARCHAR(100),Pan1 VARCHAR(100),Pan2 VARCHAR(100),RCN VARCHAR(100),ECN VARCHAR(100),PFRegNo VARCHAR(100),PFGrpCode VARCHAR(100),TAN VARCHAR(100),Sonof VARCHAR(100),FiscalID VARCHAR(100),Phone VARCHAR(100),EMail VARCHAR(100),Website VARCHAR(100))";

[sonia sardana]

thx owesome...hey tell me one thing if i want to check that database already exists or not, 'U' for table,'F' for function ..wats for database????

[sknake]

OBJECT_ID() is for database objects as the name implies, and not the database itself. For that you will use DB_ID()

IF DB_ID('NAME') IS NULL
  Print 'Database does not exist'

You can call OBJECT_NAME() and DB_NAME() to get the name of the objects from the ID.
Please do not forget to mark this thread as solved if you have found a solution to your problem.

[sonia sardana]

hello sir tell me one thing more,is it posible to create d datbase at page load in ASP.net....cz if no database exists..then how we open connection..& without opening connection,how can we craete database...

protected void Page_Load(object sender, EventArgs e)
    {
           try
           {
             if (!IsPostBack)
             {
             OpenSQLConnection();
             Query = "IF DB_ID('Sonia') IS NULL CREATE DATABASE Sonia";
             cmd = new SqlCommand(Query, conn);
             cmd.ExecuteNonQuery();
             Query = "IF OBJECT_ID('EmpInfo', 'U') IS NULL CREATE TABLE EmpInfo(CompCode VARCHAR(100),CompName VARCHAR(100),Address VARCHAR(100),ESINo VARCHAR(100),RegNo VARCHAR(100),TDS VARCHAR(100),Pan1 VARCHAR(100),Pan2 VARCHAR(100),RCN VARCHAR(100),ECN VARCHAR(100),PFRegNo VARCHAR(100),PFGrpCode VARCHAR(100),TAN VARCHAR(100),Sonof VARCHAR(100),FiscalID VARCHAR(100),Phone VARCHAR(100),EMail VARCHAR(100),Website VARCHAR(100))";               
            cmd = new SqlCommand(Query,conn);
             cmd.ExecuteNonQuery();
             }
           }
           catch (Exception  ex)
           {
               lblStatus .Text =ex.Message .ToString ();
           }
    }

    private void OpenSQLConnection()
    {
        try
        {
            conn = new SqlConnection("Data Source=SONIA-B408A4159\\SQLEXPRESS;Initial catalog=Sonia;Integrated Security=true;");
            conn.Open();
        }
        catch (Exception ex)
        {
            lblStatus.Text = ex.Message.ToString();
        }

    }

ERRORS- conn = new SqlConnection("Data Source=SONIA-B408A4159\\SQLEXPRESS;Initial catalog=Sonia;Integrated Security=true;");
Error is there no databse exists

[sknake]

How many questions are you going to ask in a single thread? Mark this as solved, start a new one with the proper subject, and I will be glad to help you

[sonia sardana]

sorry...