How can I can create a new table in a database already made empty. VB 20017 SQL Express

Question

I am learning Visual Basic 2017, and I created an empty database without a table. So, now I would like to learn how to add a table to the new database. The database that I created is SQL Express, and I am using ADO.net. There is somebody in here who can help me. I really appreciate it in advance. Thank you. I wanna do it programmatically I mean code because my database is code.

Answer 1

Hi MannySo,

Thanks for reaching out to Microsoft Q&A.

To create a new table programmatically in an SQL Express db using ADO.NET with VB 2017, follow these steps:

Step 1: Set up your project

1. Open Visual Studio and create a Windows Forms App (or Console App) in VB.
2. Make sure you have a reference to System.Data and System.Data.SqlClient in your project for ADO.NET support.

Step 2: Write the code to create a table

Below is a sample code that demonstrates how to connect to your SQL Express database and create a new table:

##VB

Imports System.Data.SqlClient
Module Module1
    Sub Main()
        ' Define your connection string (update this based on your server and database)
        Dim connectionString As String = "Server=.\SQLEXPRESS;Database=YourDatabaseName;Trusted_Connection=True;"
        
        ' Define the SQL command to create a new table
        Dim createTableQuery As String = "
            CREATE TABLE Employees (
                EmployeeID INT PRIMARY KEY IDENTITY,
                FirstName NVARCHAR(50),
                LastName NVARCHAR(50),
                BirthDate DATE
            )"
        ' Create and open a connection to SQL Express
        Using connection As New SqlConnection(connectionString)
            Try
                ' Open the connection
                connection.Open()
                ' Create a command to execute the SQL query
                Using command As New SqlCommand(createTableQuery, connection)
                    ' Execute the command
                    command.ExecuteNonQuery()
                    Console.WriteLine("Table 'Employees' created successfully.")
                End Using
            Catch ex As Exception
                ' Handle any errors that may have occurred
                Console.WriteLine("Error: " & ex.Message)
            Finally
                ' Close the connection
                connection.Close()
            End Try
        End Using
        ' Pause the console so you can see the result (only if you're using a Console App)
        Console.ReadLine()
    End Sub
End Module

• Connection String: You need to replace "YourDatabaseName" with the actual name of your database and ensure that SQL Express is correctly installed and running on your machine. You might also need to modify the Server=.\SQLEXPRESS part of the connection string depending on your SQL Server configuration.
• SQL Query: The CREATE TABLE SQL statement defines the structure of the new table (Employees in this case) with columns like EmployeeID, FirstName, LastName, and BirthDate.
• ADO.NET Objects:
  • SqlConnection: Used to connect to your SQL Server instance.
  • SqlCommand: Executes the SQL query to create the table.
  • ExecuteNonQuery(): Executes the query that does not return a result set, ideal for CREATE, INSERT, UPDATE, and DELETE operations.

Step 3: Test the code

1. Compile and run your program.
2. If everything is set up correctly, you should see the message: Table 'Employees' created successfully. in the console or output.

This code provides a basic way to create tables in your SQL express db using ADO.NET. Let me know if you need help with additional functionality, such as inserting data into the table.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.