How to that Sql Database and Table is Exists or not.

Question

0.00/5 (No votes)

See more:

Sir,

I am creating sql database and tables at runtime in vb.net appliaction
its created but when i reopen the application it shows that sql database
already exists as well as tables.

Please help me, if already exists(Sql database and Tables) then how can
it will go to next step.

Indranil

Posted 31-Jan-13 0:29am

hspl

Add a Solution

Comments

bbirajdar 31-Jan-13 6:37am

Check if table exists before creating...

3 solutions

Solution 1

Try below code

bool exists;

try
{
    // ANSI SQL way.  Works in PostgreSQL, MSSQL, MySQL.  
    var cmd = new OdbcCommand(
      "select case when exists((select * from information_schema.tables where table_name = '" + tableName + "' or Table_catalog = '" + dbName + "')) then 1 else 0 end");

    exists = (int)cmd.ExecuteScalar() == 1;
}
catch
{
   
}

Posted 31-Jan-13 0:54am

Sandesh M Patil

Updated 31-Jan-13 0:55am

v3

Solution 2

As you already aware how to run SQL query from VB.net , I am giving you two queries for checking table existance

SQL

select * FROM sys.databases WHERE name='DB1' -- Replace your database name with DB1

SQL

SELECT * FROM sys.tables WHERE name='TB1' -- Replace your table name with TB1

Below are codes for VB.Net

VB

'CREATING DATABASE......................... 
 Dim conStr As String = "Server=.;Database=;Trusted_Connection = yes;MultipleActiveResultSets=True;" 
 Dim objCon As New SqlConnection(conStr) 
 Dim obj As SqlCommand 
 Dim strSQL As String 
 Dim myReader As SqlDataReader 

  ' Create the database 
 objCon.Open() 
 obj = objCon.CreateCommand() 
  
Try 
 strSQL = "SELECT * FROM sys.databases WHERE name='INDRA'"
 obj.CommandText = strSQL 
 SET myReader=obj.ExecuteReader()
  IF NOT myReader.Read()
'obj.Close()
   strSQL = "CREATE DATABASE INDRA" 
  ' Execute 
   obj.CommandText = strSQL 
  obj.ExecuteNonQuery() 
  MsgBox("Database Created Successfully...", MsgBoxStyle.Information, "Indranil Chatterjee..") 
  END IF 
  Catch ex As Exception 
  MessageBox.Show(ex.Message) 
End Try 

'objCon.Close()

Do the same thing for table creation

Hope this helps if yes then accept and vote this solution otherwise revert back with your queries
--RDB

Posted 31-Jan-13 1:08am

RDBurmon

Updated 3-Feb-13 19:37pm

v8

Comments

hspl 31-Jan-13 9:53am

Thanks for your answer but i could not find where i put these queiries in below form1_load event :-

'CREATING DATABASE.........................
Dim conStr As String = "Server=.;Database=;Trusted_Connection = yes"
Dim objCon As New SqlConnection(conStr)
Dim obj As SqlCommand
Dim strSQL As String
' Create the database
objCon.Open()
obj = objCon.CreateCommand()
strSQL = "CREATE DATABASE INDRA"
' Execute
obj.CommandText = strSQL
Try
obj.ExecuteNonQuery()
MsgBox("Database Created Successfully...", MsgBoxStyle.Information, "Indranil Chatterjee..")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
objCon.Close()

'CREATING TABLE............
Dim conStr1 As String = "Server=.;Database=INDRA;Trusted_Connection = yes"
Dim objCon1 As New SqlConnection(conStr1)
Dim obj1 As SqlCommand
Dim strSQL1 As String
objCon1.Open()
obj1 = objCon1.CreateCommand()
strSQL1 = "CREATE TABLE Indra (ID INT IDENTITY PRIMARY KEY,FirstName VARCHAR(30), LastName VARCHAR (30),Email VARCHAR(30),PhoneNo VARCHAR (15))"
' Execute
obj1.CommandText = strSQL1
Try
obj1.ExecuteNonQuery()
MsgBox("Table Created Successfully...", MsgBoxStyle.Information, "Indranil Chatterjee..")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
objCon1.Close()
objCon1 = Nothing
Me.Hide()
Form2.Show()

Please tell me where i put the above said quieries in the form.
Please help me sir.......

Indranil

RDBurmon 31-Jan-13 10:30am

I have updated my answer . Try it.
I am online for next an hour.

hspl 1-Feb-13 1:34am

Sorry to say that after putting the codes in Form_Load event and run it, it will not
create any database in sql server.

RDBurmon 1-Feb-13 1:46am

Updated my solution . Try now

hspl 1-Feb-13 1:59am

A message shows that "There is already an open DataReader associated with this Command which must be closed first"

hspl 1-Feb-13 5:25am

Sir waiting for your suggestion on my last comment....

RDBurmon 1-Feb-13 5:28am

See my updated solution

hspl 1-Feb-13 7:07am

Sir
Here " SET " word said that its nolonger supported
And obj.Close() here said Close is not a member of 'System.Data.SqlClient.SqlCommand'

RDBurmon 1-Feb-13 7:32am

Ok remove SET and run it . If you get any error then paste your code and tell me which line throwing error.

hspl 1-Feb-13 9:15am

Its throwing an error here:-

obj.Close() <--"Close is not a member of System.Data.SqlClient.SqlCommand"

RDBurmon 3-Feb-13 23:12pm

I have updated solutions. Add MultipleActiveResultSets=True; in connection string and try again

hspl 4-Feb-13 1:32am

Same error shows that Close is not a member of System.Data.SqlClient.SqlCommand

RDBurmon 4-Feb-13 1:46am

I have commented obj.close in my solution. Try now

hspl 4-Feb-13 7:02am

Sir
Again same message shows that...
There is already an open DataReader associated with this Command which must be closed first

RDBurmon 4-Feb-13 23:58pm

Post your code here and mark the error line.

hspl 5-Feb-13 1:19am

Sir,
I myself solved the solution, and submit it also.Please see....

RDBurmon 5-Feb-13 1:21am

Gr8 then please accept my solution and vote it

hspl 5-Feb-13 1:44am

Sir,
But i want to say that you did not give me full solution, it always shows an error.
Which i told you thru my comments....

RDBurmon 5-Feb-13 1:46am

It will increase my points

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

hspl · Accepted Answer · 2013-02-04T19:00:00

I SOLVED THE PROBLEM AFTER CHANGING THE CODES/LINES SHOWING IN BOLD.....!

'CREATING DATABASE.........................
Dim conStr As String = "Server=.;Database=;Trusted_Connection = yes;MultipleActiveResultSets=True;"
Dim objCon As New SqlConnection(conStr)
Dim obj As SqlCommand
Dim strSQL As String
Dim myReader As SqlDataReader

' Create the database
objCon.Open()
obj = objCon.CreateCommand()

Try
strSQL = "SELECT * FROM sys.databases WHERE name='INDRA'"
obj.CommandText = strSQL
myReader=obj.ExecuteReader()
IF NOT myReader.Read()
myReader.Close()

strSQL = "CREATE DATABASE INDRA"
' Execute
obj.CommandText = strSQL
obj.ExecuteNonQuery()
MsgBox("Database Created Successfully...", MsgBoxStyle.Information, "Indranil Chatterjee..")
END IF
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

objCon.Close()