|
Subject:
|
Creating a SQL Server DB through VB code
|
|
Posted By:
|
tknnguyen
|
Post Date:
|
7/1/2008 7:08:03 PM
|
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
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
7/1/2008 8:13:18 PM
|
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???
|
|
Reply By:
|
tknnguyen
|
Reply Date:
|
7/2/2008 11:28:26 AM
|
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
|
|
