1

Please pardon me for my English grammar.

I'm currently coding a system and I was wondering if you could establish a new connection after you created a database in a server.

This is the Connection String:

Dim DBCon As SqlConnection = New SQLConnection("Data Source=(localdb)\DbLocal;Integrated Security=True")

If I want to create a database I use a command - the database name is bounded after a textbox, it goes like:

Dim dbName As String = txtdbName.Text
myCommand = "CREATE database " & dbName

The database gets created, but after I start a query that creates a table - the table does not save in the created database. So with my beginner skills in VB.Net and MSSQL, I deduced it was because of my Connection String, so I tried messing with it:

Dim myConnectionString As SqlConnection = New SqlConnection("Data Source=(localdb)\DbLocal;Database=" & dbName & ";Integrated Security=True;Pooling=False")

I wrote the above code before the create a table query, but after I run it, the tables I created in the query didn't go to the database. I was wondering if there's a 'right' way to do this. I tried mixing different codes that I found online, but they produce the same result.

Edit: My create table query:

myCommand = "CREATE TABLE tblPerson (PersonID int, LastName varchar(300), FirstName varchar(300), Address varchar(300), City varchar(300))"
Rhail
  • 13
  • 4

1 Answers1

2

The way I would do this is to add a USE statement before the CREATE TABLE. So the CREATE TABLE command would look like this:

"USE  " & dbName & ";
GO

CREATE TABLE ..."

EDIT: As pointed out in the comments, the GO separator cannot be used in a .NET SQL Command.

Instead one can use three-part naming to specify the database like this:

"CREATE TABLE   " & dbName & ".dbo.MyTable ( ..."

Or use SMO which does allow one to use the GO separator. This technique is thoroughly described in the accepted answer to this question.

Community
  • 1
  • 1
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Since the OP is doing everything via sql command the GO keyword is not going to work here. That is the default batch separator in SSMS and not a sql command. You can however use three part naming to create the new table. – Sean Lange Mar 13 '17 at 14:37
  • @SeanLange what is 'three part naming' to create a table, where can I found information about it? – Rhail Mar 13 '17 at 14:48
  • In this case it would be "Create " + dbname + ".dbo.tblPerson..." This way you can state you want to create the table in the given database and schema. As long as they are on the same server this is perfectly valid. – Sean Lange Mar 13 '17 at 14:51
  • Thanks Sean, it had been a while and I forgot that you couldn't use the GO separator in a SQL command. Three part naming will work, as you mentioned, and so should SMO, as described extensively here: http://stackoverflow.com/questions/40814/execute-a-large-sql-script-with-go-commands – Tab Alleman Mar 13 '17 at 15:00