VB2017 Ado.net SqlExpress. Problem with Table Creation, no error but does'nt work

[Manny Somarriba]

I have two weeks to develop this little program. The program will create an SQL Database and a table. It does not show any error, but when I compile or debug, it does nothing. I appreciate your help in advance about this matter. Here is the code.

Imports System.Data.SqlClient

Public Class Form1
Private objCon As Object

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub

Private Sub btnCreateDatabase_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles BtnCreatedatabase.Click
Dim str As String

Dim myConn As SqlConnection = New SqlConnection("Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True; ")


str = "CREATE DATABASE visitas ON PRIMARY " &
"(NAME = MyDatabase_Data, " &
" FILENAME = 'C:\Project\visitas.mdf', " &
" SIZE = 2MB, " &
" MAXSIZE = 10MB, " &
" FILEGROWTH = 10%)" &
" LOG ON " &
"(NAME = MyDatabase_Log, " &
" FILENAME = 'C:\Project\visitasLog.ldf', " &
" SIZE = 1MB, " &
" MAXSIZE = 5MB, " &
" FILEGROWTH = 10%)"

Dim myCommand As SqlCommand = New SqlCommand(str, myConn)

Try
myConn.Open()
myCommand.ExecuteNonQuery()
MessageBox.Show("Database is created successfully",
"MyProgram", MessageBoxButtons.OK,
MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
If (myConn.State = ConnectionState.Open) Then
myConn.Close()
End If
End Try




'Create a table


' = "Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True; "
myConn = New SqlConnection(constr)
myConn.Open()
myCommand = objCon.CreateCommand()
Dim strSQL As String
strSQL = "CREATE TABLE Names (LastName VARCHAR(30), FirstName VARCHAR (30))"



' Execute
myCommand.CommandText = strSQL
Try
myCommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

myConn.Close()
myConn = Nothing
End Sub

Private Function constr() As String
Throw New NotImplementedException()
End Function

Private Function strcoon() As String
Throw New NotImplementedException()
End Function
End Class

[Zvoni]

1) You're a member since 2001. You should know by now how to use Code-Tags
2) "Does nothing" is not an "Error-Description". What exactly is NOT happening? The Creation of the Database? The Creation of the Table?
3) Why are you closing the connection after creating the Database? Leave it open
4) In your Creation of Table Code, in your connection-object you use variable/function "constr" which is not implemented?
5) you try to execute the CREATE Table WITHOUT qualifying the Schemaname to which the table belongs
6) IIRC, to create a Database or Table you need "dba"-Role/Rights. Maybe something like that?

[kareninstructor]

Here is an example done in a console project that will work in a forms project. What is missing? A check to see if the database exists and same for the table.

• Note the connection string against your connection string.
• NuGet package Spectre.Console enhanced console writes.

Imports Microsoft.Data.SqlClient
Imports Spectre.Console

Module Program
    Sub Main(args As String())

        Dim connectionString = 
                "Data Source=.\SQLEXPRESS;Initial Catalog=master;" & 
                "Integrated Security=True;Encrypt=False"
        
        Dim createDatabaseQuery = "CREATE DATABASE SampleDatabase"

        Dim createTableQuery = "
            USE SampleDatabase;
            CREATE TABLE [dbo].[Customers](
	          [Id] [INT] IDENTITY(1,1) NOT NULL,
	          [FirstName] [NVARCHAR](MAX) NOT NULL,
	          [LastName] [NVARCHAR](MAX) NOT NULL,
	          [Address] [NVARCHAR](MAX) NULL,
	          [Phone] [NVARCHAR](MAX) NULL,
	          [Email] [NVARCHAR](MAX) NULL,
            CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
            (
	            [Id] ASC
            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
            ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"


        Using connection As New SqlConnection(connectionString)
            connection.Open()

            Using command As New SqlCommand(createDatabaseQuery, connection)
                command.ExecuteNonQuery()
                AnsiConsole.MarkupLine("[cyan]Database 'SampleDatabase' created successfully.[/]")
            End Using
        End Using
        
        connectionString = "Server=.\SQLEXPRESS;Database=SampleDatabase;" & 
                           "Integrated Security=True;Encrypt=False"

        Using connection As New SqlConnection(connectionString)
            connection.Open()

            Using command As New SqlCommand(createTableQuery, connection)
                command.ExecuteNonQuery()
                AnsiConsole.MarkupLine("[green]Table 'Customers' created successfully in 'SampleDatabase'.[/]")
            End Using
        End Using

        AnsiConsole.MarkupLine("[bold green]Press any key to exit...[/]")
        Console.ReadLine()

    End Sub
End Module

Have you considered using EF Core? EF Core DbContext has

Database.EnsureDeleted - if database exists remove it
Database.EnsureCreated - creates database and table(s)