Click here to Skip to main content
11,645,087 members (71,350 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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
hspl254
Comments
aspnet_regiis -i at 31-Jan-13 6:37am
   
Check if table exists before creating...
Rate this: bad
good
Please Sign up or sign in to vote.

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
{
   
}
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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

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

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


Below are codes for VB.Net
'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
  Permalink  
v8
Comments
hspl at 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
Rahul Dhoble at 31-Jan-13 10:30am
   
I have updated my answer . Try it.
I am online for next an hour.
hspl at 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.
Rahul Dhoble at 1-Feb-13 1:46am
   
Updated my solution . Try now
hspl at 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 at 1-Feb-13 5:25am
   
Sir waiting for your suggestion on my last comment....
Rahul Dhoble at 1-Feb-13 5:28am
   
See my updated solution
hspl at 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'
Rahul Dhoble at 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 at 1-Feb-13 9:15am
   
Its throwing an error here:-

obj.Close() <--"Close is not a member of System.Data.SqlClient.SqlCommand"
Rahul Dhoble at 3-Feb-13 23:12pm
   
I have updated solutions. Add MultipleActiveResultSets=True; in connection string and try again
hspl at 4-Feb-13 1:32am
   
Same error shows that Close is not a member of System.Data.SqlClient.SqlCommand
Rahul Dhoble at 4-Feb-13 1:46am
   
I have commented obj.close in my solution. Try now
hspl at 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
Rahul Dhoble at 4-Feb-13 23:58pm
   
Post your code here and mark the error line.
hspl at 5-Feb-13 1:19am
   
Sir,
I myself solved the solution, and submit it also.Please see....
Rahul Dhoble at 5-Feb-13 1:21am
   
Gr8 then please accept my solution and vote it
hspl at 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....
Rahul Dhoble at 5-Feb-13 1:46am
   
It will increase my points
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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()
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 700
1 jyo.net 494
2 Sergey Alexandrovich Kryukov 439
3 Afzaal Ahmad Zeeshan 404
4 CPallini 386
0 OriginalGriff 1,065
1 Sergey Alexandrovich Kryukov 683
2 DamithSL 681
3 Afzaal Ahmad Zeeshan 662
4 CPallini 555


Advertise | Privacy | Mobile
Web02 | 2.8.150731.1 | Last Updated 5 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100