1.11M Members

How do I create a SQL database & table in VB.NET?

 
0
 

I'm trying to create my own Database and Tables in SQL Server 2005 using VB.NET 2005. I get an error when I try ExecuteNonQuery, which says that the connection is not initialized. I'm not sure if I'm doing any of this right.

Any help would be greatly appreciated!
Sheryl

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim obj As New SqlCommand
        ' am I supposed to leave the database blank here?
        Dim conStr As String = "Server=DIMENSION9100;Database=;Trusted_Connection = yes"
        Dim objCon As New SqlConnection(conStr)

        Try
            objCon.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try

        obj.CommandText = "CREATE DATABASE MyDataBase"
        Try
           ' this is where I get the error
            obj.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try

        ' is this syntax correct?
        ' creat Table People
        obj.CommandText = "CREATE TABLE MyDataBase, People " & _
                          "(" & _
                          "Id        int NOT NULL PRIMARY KEY " & _
                          "LastName  VARCHAR(30) " & _
                          "FirstName VARCHAR(20) " & _
                          "Address   VARCHAR(50) " & _
                          ") IN MyDatabase"
        obj.ExecuteNonQuery()
    End Sub
 
0
 

Can't say if your connection string is right or wrong.

However, this is how you create database:

Dim conStr As String = "Server=DIMENSION9100;Database=;Trusted_Connection = yes"
Dim objCon As New SqlConnection(conStr)
Dim obj As SqlCommand
Dim strSQL as String

obj = objConn.CreateCommand()
strSQL = "CREATE DATABASE " & DatabaseName
' Execute
obj.CommandText = strSQL
obj.ExecuteNonQuery()

And table:

Dim conStr As String = "Server=DIMENSION9100;Database=;Trusted_Connection = yes"
Dim objCon As New SqlConnection(conStr)
Dim obj As SqlCommand
Dim strSQL as String

obj = objConn.CreateCommand()
strSQL = "CREATE TABLE "  & DatabaseName & ". People (" & _
  "Id int NOT NULL PRIMARY KEY, " & _
  "LastName  VARCHAR(30), " & _
  "FirstName VARCHAR(20), " & _
  "Address   VARCHAR(50) " & _
  ") "
' Execute
obj.CommandText = strSQL
obj.ExecuteNonQuery()

If you get incorrect syntax from this SQL statement, you may put database name (drop & DatabaseName & ". -part from strSQL) in the connection string, since you already created it.
And this is SQL Server syntax. Some other DB might use a slightly different syntax.

 
0
 

Thank you so much for submiting your code. I was able to get it to work with a few changes. I kept getting a syntax error message when I tried to create the table, so I changed that code to add just a few simple columns. I also had to re-open the connection with the new connection string showing the name of my new database.

So, here's my code for anyone else wanting to know how to do this.

~ Sheryl

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim conStr As String = "Server=DIMENSION9100;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 People"
        ' Execute
        obj.CommandText = strSQL
        obj.ExecuteNonQuery()

        ' Create a table
        conStr = "Server=DIMENSION9100;Database=People;Trusted_Connection = yes"
        objCon = New SqlConnection(conStr)
        objCon.Open()
        obj = objCon.CreateCommand()
        strSQL = "CREATE TABLE Names (LastName VARCHAR(30), FirstName VARCHAR (30))"

        ' Execute
        obj.CommandText = strSQL
        Try
            obj.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        
        objCon.Close()
        objCon = Nothing
    End Sub
Question Answered as of 5 Years Ago by Teme64
 
0
 

how i start creating sql database in vb 2008 express edition for my payroll system project without using ms access

 
0
 

please help to create dabase in our project in school

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article