Hi,

I'm currently using VB2008 to access SQL Server 2005 instance on my machine. I'm trying to write code to create a new database and then create tables within the database.

I'm running into a strange problem right now, which is the fact that I was able to create a new database ok on either the regular SQL Server instance, or the SQLEXPRESS instance. However, when I attempted to create a table, I got an error message saying that there is already a table of the same name.

When I tried to connect to the new database via SQL Server Management Studio, I could see the database. However, there are no tables in it! So, theoretically, I'm creating a brand new blank DB, so there should not be anything in this DB, right? and I should be able to create any tables I want in it!

Here is the code that I have so far:

Module DBInitUtilities

    'Dim strConnect As String = "Data Source=D3J46DC1;Initial Catalog=DTEMDB;Integrated Security=True"
    'Dim strConnect As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DTEMDB;Integrated Security=True"
    Dim strConnect As String = "Server=D3J46DC1;Integrated Security=True"
    Dim connSQL As SqlClient.SqlConnection = Nothing
    Dim cmdSQL As SqlClient.SqlCommand = Nothing

    Public Sub ExecuteSQLStmt(ByVal strSQL As String)
        '
        ' Execute a SQL statement
        '

        ' Open the connection
        If connSQL.State <> ConnectionState.Open Then
            connSQL.Open()
        End If

        'Define the SQL command
        cmdSQL = New SqlClient.SqlCommand(strSQL, connSQL)

        Try
            'Attempt to execute the SQL command
            cmdSQL.ExecuteNonQuery()

        Catch ae As SqlClient.SqlException
            MessageBox.Show(ae.Message.ToString())
        End Try

    End Sub 'ExecuteSQLStmt

    Public Sub CreateAllDBs()

        CreateDTDataDB()

    End Sub

    Public Sub CreateDTDataDB()

        ' Create a connection
        connSQL = New SqlClient.SqlConnection(strConnect)

        ' Open the connection
        If connSQL.State <> ConnectionState.Open Then
            connSQL.Open()
        End If

        Dim strSQL As String = "CREATE DATABASE DTData ON PRIMARY" + _
                               "(NAME = DTData, FILENAME = 'C:\dbs\DTData.mdf', SIZE = 2048KB," + "MAXSIZE = UNLIMITED, FILEGROWTH = 10%)LOG ON" + _
                               "(NAME = DTData_log, FILENAME = 'C:\dbs\DTData_log.ldf', SIZE = 1024KB," + "MAXSIZE = 4096KB, FILEGROWTH = 1%)"


        ExecuteSQLStmt(strSQL)

        CreateGroupsTable()
        CreateLocationHistoryTable()
        CreateLocationsTable()
        CreateSNDetailTable()

        MsgBox("New DB has been created!", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Process Completed Successfully!")

    End Sub

    Public Sub CreateGroupsTable()

        Dim strSQL As String

        strSQL = "CREATE TABLE Groups" + _
                 "(GroupName NCHAR(64) CONSTRAINT PKeyGroupName PRIMARY KEY, " + _
                 "Parent NCHAR(64), " + _
                 "IcondIndex SMALLINT, " + _
                 "GroupImage IMAGE, " + _
                 "ImageStretchMode SMALLINT)"

        ExecuteSQLStmt(strSQL)

    End Sub

    Public Sub CreateLocationHistoryTable()

        Dim strSQL As String

        strSQL = "CREATE TABLE LocationHistory" + _
                 "(Location NCHAR(64), " + _
                 "Parent NCHAR(64), " + _
                 "CreationDate DATETIME, " + _
                 "LocParIDX BIGINT CONSTRAINT PKeyLocParIDX PRIMARY KEY, " + _
                 "SourceSN NCHAR(16))"

        ExecuteSQLStmt(strSQL)

    End Sub

    Public Sub CreateLocationsTable()

        Dim strSQL As String

        strSQL = "CREATE TABLE Locations" + _
                 "(Location NCHAR(64) CONSTRAINT PKeyLocation PRIMARY KEY, " + _
                 "Parent NCHAR(64), " + _
                 "DataType SMALLINT, " + _
                 "CurrentDataValue REAL, " + _
                 "SourceSN NCHAR(16), " + _
                 "AlarmStatus SMALLINT, " + _
                 "CurrentTimeStamp DATETIME, " + _
                 "CommStatus SMALLINT, " + _
                 "LocParIDX BIGINT, " + _
                 "Image_X FLOAT, " + _
                 "Image_Y FLOAT, " + _
                 "IconIndex SMALLINT, " + _
                 "BackgndColor BIGINT)"

        ExecuteSQLStmt(strSQL)

    End Sub

    Public Sub CreateSNDetailTable()

        Dim strSQL As String

        strSQL = "CREATE TABLE SNDetail" + _
                 "(SN NCHAR(16) CONSTRAINT PKeySN PRIMARY KEY, " + _
                 "TracerType SMALLINT, " + _
                 "CommStatus SMALLINT, " + _
                 "CurrentInterval SMALLINT, " + _
                 "Modulo SMALLINT, " + _
                 "InstallDate SMALLDATETIME, " + _
                 "LocPar1 BIGINT, " + _
                 "LocPar2 BIGINT, " + _
                 "LocPar3 BIGINT, " + _
                 "LocPar4 BIGINT)"

        ExecuteSQLStmt(strSQL)

    End Sub

End Module


I just have one simple form with a button on it. The click event on this button calls the CreateAllDBs() routine.

I'm not sure what I'm doing wrong here. I really appreciate any help on this. Thank you very much.

Khoi Nguyen

__________________
Khoi Nguyen

Never done this, but...

It seems to me that SQL Server would have NO IDEA which database to create those tables in!

You have created a database, yes (or at least I assume that part works), but then your connection is still to SQL Server, as a whole, an *NOT* to any specific database therein.

Perhaps you just need a
    USE [db name]
before doing the CREATE TABLEs??? Or perhaps you need to close the conneciton and reopen it specifically to the newly created DB???

Hi,

Thank you for your help. I see it now... it was not apparent to me yesterday. I'll give it a try.

Khoi Nguyen