[RESOLVED] [2005] database table creation issues

[BrailleSchool]

i have been searching around and found this thread http://www.vbforums.com/showthread.p...ight=read+.sql which i found interesting and thought I would adopt something like it in my application. only thing is, i get an unhandled exception in my application as soon as it launches. Of course, this information was emailed to me and have all the information. not sure what it means though so thats why I am back. The following form load code is what im using to hopefully execute the sql statements

Imports System.Data.SqlClient
 
Public Class frmMain
    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        'this directory must exist just incase the application needs to do any SQL creation
        If Not System.IO.Directory.Exists("sql") Then
            My.Computer.FileSystem.CreateDirectory("sql")
        End If
 
        'if there is a file called sql.sql present in the \sql\ directory, execute its content
        With My.Computer.FileSystem
            If System.IO.File.Exists(My.Application.Info.DirectoryPath & "\sql\" & "sql.sql") Then
                OpenConnectionIfClosed()
                Dim SQLFile As String = .ReadAllText(My.Application.Info.DirectoryPath & "\sql\" & "sql.sql")
                Dim sqlCommand As New SqlCommand(SQLFile, m_DBCon)
                sqlCommand.ExecuteNonQuery()
                CloseConnectionIfOpen()
                'now that the request has been completed, delete the file
                IO.File.Delete(My.Application.Info.DirectoryPath & "\sql\" & "sql.sql")
            End If
        End With
    End Sub

attached is my sql.sql file (which was exported from my database) --renamed on this thread to .txt because it wouldnt let me upload a .sql file

the exception/errors that im getting as per my e-mail is:

An unhandled error has occurred in the application and the error log is attached for resolution assignment.

Message:
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'dbo'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near 'GO'.
Incorrect syntax near 'dbo'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'dbo'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.

Stack trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at BusinessStudio.frmMain.frmMain_Load(Object sender, EventArgs e) in D:\BusinessStudio\frmMain.vb:line 49
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

any ideas?
line 49 (didnt post all the code in the form_load as its not needed) is

sqlCommand.ExecuteNonQuery()

[BrailleSchool]

ok, now i am checking to see if the table exists and if it doesnt, then to create it but the application doesnt like the SQL.

An unhandled error has occurred in the application and the error log is attached for resolution assignment.

Message:
Incorrect syntax near the keyword 'IF'.
Incorrect syntax near 'Users_Auth'.
Incorrect syntax near 'Users_Profile'.
Incorrect syntax near the keyword 'IF'.
Incorrect syntax near 'Users_History'.

Stack trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at BusinessStudio.frmMain.frmMain_Load(Object sender, EventArgs e) in D:\BusinessStudio\frmMain.vb:line 49
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

USE [BusinessStudio]

CREATE TABLE IF NOT EXISTS [Users_Auth](
[UsersPK] [int] IDENTITY(1,1) NOT NULL,
[Username] [varchar](max) NOT NULL,
[Password] [varchar](max) NOT NULL,
[IsLocked] [int] NOT NULL,
[IsAdmin] [int] NOT NULL,
[LastLoginDate] [datetime] NULL,
[LastPasswordChange] [datetime] NOT NULL,
[LastModifiedBy] [varchar](max) NULL,
[LastModifiedDate] [datetime] NULL,
CONSTRAINT [PK_Users_Auth] PRIMARY KEY CLUSTERED
(
[UsersPK] ASC
CREATE TABLE IF NOT EXISTS [Users_Profile](
[UsersProfilePK] [int] IDENTITY(1,1) NOT NULL,
[UsersPKFK] [int] NULL,
[Employee_Prefix] [varchar](50) NOT NULL,
[Employee_FirstName] [varchar](max) NOT NULL,
[Employee_MiddleInitial] [varchar](2) NULL,
[Employee_LastName] [varchar](max) NOT NULL,
[Employee_Suffix] [varchar](50) NULL,
[Employee_Addr1] [varchar](max) NOT NULL,
[Employee_Addr2] [varchar](max) NULL,
[Employee_Addr3] [varchar](max) NULL,
[Employee_City] [varchar](max) NOT NULL,
[Employee_State] [varchar](14) NOT NULL,
[Employee_ZipCode] [varchar](10) NOT NULL,
[Employee_HomePhone] [varchar](13) NOT NULL,
[Employee_CellPhone] [varchar](13) NULL,
[Employee_Pager] [varchar](13) NULL,
[Employee_Fax] [varchar](13) NULL,
[Employee_BusinessPhone] [varchar](13) NULL,
[Employee_BusinessPhoneExt] [varchar](max) NULL,
[Employee_BusinessEmail] [varchar](max) NULL,
[Employee_PersonalEmail] [varchar](max) NULL,
[Employee_Department] [varchar](max) NULL,
[Employee_Floor] [varchar](max) NULL,
[Employee_RoomSuiteNumber] [varchar](max) NULL,
[Employee_SSN] [varchar](11) NOT NULL,
[Employee_ANumber] [varchar](11) NULL,
[Employee_Gender] [char](5) NOT NULL,
[Employee_DOB] [datetime] NOT NULL,
[Employee_Age] [int] NULL,
[Employee_ID] [varchar](max) NULL,
[Employee_StartDate] [datetime] NOT NULL,
[Employee_TermDate] [datetime] NULL,
[Employee_Wage] [varchar](max) NULL,
[Employee_Minor] [char](3) NOT NULL,
[Employee_FullTime] [char](3) NOT NULL,
[Employee_PartTime] [char](3) NOT NULL,
[Employee_Consultant] [char](3) NOT NULL,
[Employee_Type] [nchar](10) NULL,
[NOK_Prefix] [varchar](50) NOT NULL,
[NOK_FirstName] [varchar](max) NOT NULL,
[NOK_MiddleInitial] [varchar](2) NULL,
[NOK_LastName] [varchar](max) NOT NULL,
[NOK_Suffix] [varchar](50) NULL,
[NOK_Addr1] [varchar](max) NOT NULL,
[NOK_Addr2] [varchar](max) NULL,
[NOK_Addr3] [varchar](max) NULL,
[NOK_City] [varchar](max) NOT NULL,
[NOK_State] [varchar](14) NOT NULL,
[NOK_ZipCode] [varchar](10) NOT NULL,
[NOK_HomePhone] [varchar](13) NOT NULL,
[NOK_CellPhone] [varchar](13) NULL,
[NOK_Pager] [varchar](13) NULL,
[NOK_Fax] [varchar](13) NULL,
[NOK_BusinessPhone] [varchar](13) NULL,
[NOK_BusinessPhoneExt] [varchar](max) NULL,
[NOK_BusinessEmail] [varchar](max) NULL,
[NOK_PersonalEmail] [varchar](max) NULL,
[NOK_Relationship] [varchar](20) NOT NULL,
[LastModifiedBy] [varchar](max) NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Users_Profile] PRIMARY KEY CLUSTERED
(
[UsersProfilePK] ASC

ALTER TABLE [Users_Profile] WITH CHECK ADD CONSTRAINT [AuthProfile] FOREIGN KEY([UsersPKFK])
REFERENCES [Users_Auth] ([UsersPK])
ALTER TABLE [Users_Profile] CHECK CONSTRAINT [AuthProfile]

CREATE TABLE IF NOT EXISTS [Users_History](
[HistoryPK] [int] IDENTITY(1,1) NOT NULL,
[UsersPKFK] [int] NOT NULL,
[EventDate] [datetime] NOT NULL,
[EventDesc] [varchar](max) NOT NULL,
CONSTRAINT [PK_Users_History] PRIMARY KEY CLUSTERED
(
[HistoryPK] ASC

ALTER TABLE [Users_History] WITH CHECK ADD CONSTRAINT [AuthHistory] FOREIGN KEY([UsersPKFK])
REFERENCES [Users_Auth] ([UsersPK])
ALTER TABLE [Users_History] CHECK CONSTRAINT [AuthHistory]

[jmcilhinney]

Try replacing all your "GO" instances with semicolons.

[BrailleSchool]

ok, removed all the [] and tried again. no change.

An unhandled error has occurred in the application and the error log is attached for resolution assignment.

Message:
Incorrect syntax near the keyword 'IF'.
Incorrect syntax near 'Users_Auth'.
Incorrect syntax near 'Users_Profile'.
Incorrect syntax near the keyword 'IF'.
Incorrect syntax near 'Users_History'.

Stack trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at BusinessStudio.frmMain.frmMain_Load(Object sender, EventArgs e) in D:\BusinessStudio\frmMain.vb:line 49
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

[BrailleSchool]

still working at this and now getting

An unhandled error has occurred in the application and the error log is attached for resolution assignment.

Message:
Incorrect syntax near the keyword 'IF'.
Incorrect syntax near 'Users_Auth'.
Incorrect syntax near 'Users_Profile'.
Incorrect syntax near ';'.
Incorrect syntax near the keyword 'IF'.
Incorrect syntax near 'Users_History'.
Incorrect syntax near ';'.

Stack trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at BusinessStudio.frmMain.frmMain_Load(Object sender, EventArgs e) in D:\BusinessStudio\frmMain.vb:line 49
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

USE BusinessStudio

CREATE TABLE IF NOT EXISTS Users_Auth(
UsersPK int IDENTITY(1,1) NOT NULL,
Username varchar(max) NOT NULL,
Password varchar(max) NOT NULL,
IsLocked int NOT NULL,
IsAdmin int NOT NULL,
LastLoginDate datetime NULL,
LastPasswordChange datetime NOT NULL,
LastModifiedBy varchar(max) NULL,
LastModifiedDate datetime NULL,
CONSTRAINT PK_Users_Auth PRIMARY KEY CLUSTERED
( UsersPK ASC;

CREATE TABLE IF NOT EXISTS Users_Profile(
UsersProfilePK int IDENTITY(1,1) NOT NULL,
UsersPKFK int NULL,
Employee_Prefix varchar(50) NOT NULL,
Employee_FirstName varchar(max) NOT NULL,
Employee_MiddleInitial varchar(2) NULL,
Employee_LastName varchar(max) NOT NULL,
Employee_Suffix varchar(50) NULL,
Employee_Addr1 varchar(max) NOT NULL,
Employee_Addr2 varchar(max) NULL,
Employee_Addr3 varchar(max) NULL,
Employee_City varchar(max) NOT NULL,
Employee_State varchar(14) NOT NULL,
Employee_ZipCode varchar(10) NOT NULL,
Employee_HomePhone varchar(13) NOT NULL,
Employee_CellPhone varchar(13) NULL,
Employee_Pager varchar(13) NULL,
Employee_Fax varchar(13) NULL,
Employee_BusinessPhone varchar(13) NULL,
Employee_BusinessPhoneExt varchar(max) NULL,
Employee_BusinessEmail varchar(max) NULL,
Employee_PersonalEmail varchar(max) NULL,
Employee_Department varchar(max) NULL,
Employee_Floor varchar(max) NULL,
Employee_RoomSuiteNumber varchar(max) NULL,
Employee_SSN varchar(11) NOT NULL,
Employee_ANumber varchar(11) NULL,
Employee_Gender char(5) NOT NULL,
Employee_DOB Datetime NOT NULL,
Employee_Age int NULL,
Employee_ID varchar(max) NULL,
Employee_StartDate datetime NOT NULL,
Employee_TermDate datetime NULL,
Employee_Wage varchar(max) NULL,
Employee_Minor char(3) NOT NULL,
Employee_FullTime char(3) NOT NULL,
Employee_PartTime char(3) NOT NULL,
Employee_Consultant char(3) NOT NULL,
Employee_Type nchar(10) NULL,
NOK_Prefix varchar(50) NOT NULL,
NOK_FirstName varchar(max) NOT NULL,
NOK_MiddleInitial varchar(2) NULL,
NOK_LastName varchar(max) NOT NULL,
NOK_Suffix varchar(50) NULL,
NOK_Addr1 varchar(max) NOT NULL,
NOK_Addr2 varchar(max) NULL,
NOK_Addr3 varchar(max) NULL,
NOK_City varchar(max) NOT NULL,
NOK_State varchar(14) NOT NULL,
NOK_ZipCode varchar(10) NOT NULL,
NOK_HomePhone varchar(13) NOT NULL,
NOK_CellPhone varchar(13) NULL,
NOK_Pager varchar(13) NULL,
NOK_Fax varchar(13) NULL,
NOK_BusinessPhone varchar(13) NULL,
NOK_BusinessPhoneExt varchar(max) NULL,
NOK_BusinessEmail varchar(max) NULL,
NOK_PersonalEmail varchar(max) NULL,
NOK_Relationship varchar(20) NOT NULL,
LastModifiedBy varchar(max) NOT NULL,
LastModifiedDate datetime NOT NULL,
CONSTRAINT PK_Users_Profile PRIMARY KEY CLUSTERED
( UsersProfilePK ASC;

ALTER TABLE Users_Profile WITH CHECK ADD CONSTRAINT AuthProfile FOREIGN KEY(UsersPKFK);
REFERENCES Users_Auth (UsersPK);
ALTER TABLE Users_Profile CHECK CONSTRAINT AuthProfile;

CREATE TABLE IF NOT EXISTS Users_History(
HistoryPK int IDENTITY(1,1) NOT NULL,
UsersPKFK int NOT NULL,
EventDate datetime NOT NULL,
EventDesc varchar(max) NOT NULL,
CONSTRAINT PK_Users_History PRIMARY KEY CLUSTERED
( HistoryPK ASC;

ALTER TABLE Users_History WITH CHECK ADD CONSTRAINT AuthHistory FOREIGN KEY(UsersPKFK);
REFERENCES Users_Auth (UsersPK);
ALTER TABLE Users_History CHECK CONSTRAINT AuthHistory;

[BrailleSchool]

getting same error in #5 for the following. dont see whats wrong

USE BusinessStudio;

CREATE TABLE IF NOT EXISTS Users_Auth(
UsersPK int IDENTITY(1,1) NOT NULL,
Username varchar(max) NOT NULL,
Password varchar(max) NOT NULL,
IsLocked int NOT NULL,
IsAdmin int NOT NULL,
LastLoginDate datetime NULL,
LastPasswordChange datetime NOT NULL,
LastModifiedBy varchar(max) NULL,
LastModifiedDate datetime NULL,
CONSTRAINT PK_Users_Auth PRIMARY KEY CLUSTERED
) UsersPK ASC;

CREATE TABLE IF NOT EXISTS Users_Profile(
UsersProfilePK int IDENTITY(1,1) NOT NULL,
UsersPKFK int NULL,
Employee_Prefix varchar(50) NOT NULL,
Employee_FirstName varchar(max) NOT NULL,
Employee_MiddleInitial varchar(2) NULL,
Employee_LastName varchar(max) NOT NULL,
Employee_Suffix varchar(50) NULL,
Employee_Addr1 varchar(max) NOT NULL,
Employee_Addr2 varchar(max) NULL,
Employee_Addr3 varchar(max) NULL,
Employee_City varchar(max) NOT NULL,
Employee_State varchar(14) NOT NULL,
Employee_ZipCode varchar(10) NOT NULL,
Employee_HomePhone varchar(13) NOT NULL,
Employee_CellPhone varchar(13) NULL,
Employee_Pager varchar(13) NULL,
Employee_Fax varchar(13) NULL,
Employee_BusinessPhone varchar(13) NULL,
Employee_BusinessPhoneExt varchar(max) NULL,
Employee_BusinessEmail varchar(max) NULL,
Employee_PersonalEmail varchar(max) NULL,
Employee_Department varchar(max) NULL,
Employee_Floor varchar(max) NULL,
Employee_RoomSuiteNumber varchar(max) NULL,
Employee_SSN varchar(11) NOT NULL,
Employee_ANumber varchar(11) NULL,
Employee_Gender char(5) NOT NULL,
Employee_DOB Datetime NOT NULL,
Employee_Age int NULL,
Employee_ID varchar(max) NULL,
Employee_StartDate datetime NOT NULL,
Employee_TermDate datetime NULL,
Employee_Wage varchar(max) NULL,
Employee_Minor char(3) NOT NULL,
Employee_FullTime char(3) NOT NULL,
Employee_PartTime char(3) NOT NULL,
Employee_Consultant char(3) NOT NULL,
Employee_Type nchar(10) NULL,
NOK_Prefix varchar(50) NOT NULL,
NOK_FirstName varchar(max) NOT NULL,
NOK_MiddleInitial varchar(2) NULL,
NOK_LastName varchar(max) NOT NULL,
NOK_Suffix varchar(50) NULL,
NOK_Addr1 varchar(max) NOT NULL,
NOK_Addr2 varchar(max) NULL,
NOK_Addr3 varchar(max) NULL,
NOK_City varchar(max) NOT NULL,
NOK_State varchar(14) NOT NULL,
NOK_ZipCode varchar(10) NOT NULL,
NOK_HomePhone varchar(13) NOT NULL,
NOK_CellPhone varchar(13) NULL,
NOK_Pager varchar(13) NULL,
NOK_Fax varchar(13) NULL,
NOK_BusinessPhone varchar(13) NULL,
NOK_BusinessPhoneExt varchar(max) NULL,
NOK_BusinessEmail varchar(max) NULL,
NOK_PersonalEmail varchar(max) NULL,
NOK_Relationship varchar(20) NOT NULL,
LastModifiedBy varchar(max) NOT NULL,
LastModifiedDate datetime NOT NULL,
CONSTRAINT PK_Users_Profile PRIMARY KEY CLUSTERED
) UsersProfilePK ASC;

ALTER TABLE Users_Profile WITH CHECK ADD CONSTRAINT AuthProfile FOREIGN KEY(UsersPKFK);
REFERENCES Users_Auth (UsersPK);
ALTER TABLE Users_Profile CHECK CONSTRAINT AuthProfile;

CREATE TABLE IF NOT EXISTS Users_History(
HistoryPK int IDENTITY(1,1) NOT NULL,
UsersPKFK int NOT NULL,
EventDate datetime NOT NULL,
EventDesc varchar(max) NOT NULL,
CONSTRAINT PK_Users_History PRIMARY KEY CLUSTERED
) HistoryPK ASC;

ALTER TABLE Users_History WITH CHECK ADD CONSTRAINT AuthHistory FOREIGN KEY(UsersPKFK);
REFERENCES Users_Auth (UsersPK);
ALTER TABLE Users_History CHECK CONSTRAINT AuthHistory;

[sallushan]

Try reducing your query first. Create simple Query and execute.

For example:

SELECT * FROM sometable

Single line with NO vbCrLf

Sometimes vbCrLf also creates issues.

[BrailleSchool]

Try reducing your query first. Create simple Query and execute.

For example:

SELECT * FROM sometable

Single line with NO vbCrLf

Sometimes vbCrLf also creates issues.

good point. ill try that.

update:
i did as you suggested and it gave exact same error messsage

[sallushan]

I have used the simplest Query and it worked.

Dim objConn As SqlClient.SqlConnection
Dim objCmd As SqlClient.SqlCommand

objConn = New SqlClient.SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=True")
objCmd = New SqlClient.SqlCommand("SELECT * FROM sysusers", objConn)

objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()

objCmd.Dispose()
objConn.Dispose()

Maybe there is some issue with your SQL Server Connectivity, what is the actual exception?

[BrailleSchool]

its not the server connectivity that is the issue. its the format of the SQL within the file i am trying to create the tables from within the database. its not liking the format.

I have used the simplest Query and it worked.

Dim objConn As SqlClient.SqlConnection
Dim objCmd As SqlClient.SqlCommand

objConn = New SqlClient.SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=True")
objCmd = New SqlClient.SqlCommand("SELECT * FROM sysusers", objConn)

objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()

objCmd.Dispose()
objConn.Dispose()

Maybe there is some issue with your SQL Server Connectivity, what is the actual exception?

[sallushan]

Check this line

CREATE TABLE IF NOT EXISTS Users_Auth(

I think this is NOT the syntax, It should be like this

If NOT Exists(SELECT * FROM Tables.....)
Begin
CREATE TABLE .....
End

[GaryMazzone]

Create table If Not Exsist will not work in SQL Server. It is not a standard part of ANSI SQL. If you read at post 15 and down you will see what needs to be done.

[BrailleSchool]

Create table If Not Exsist will not work in SQL Server. It is not a standard part of ANSI SQL. If you read at post 15 and down you will see what needs to be done.

maybe i was thinking mysql. ill check it. thanks

[BrailleSchool]

ok, been working hard and got it finally done. this is the finished sql file for the time being.

USE BusinessStudio
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE Company_Information(
CompanyName text NOT NULL,
AddressLine1 text NOT NULL,
AddressLine2 text NULL,
City text NOT NULL,
State text NOT NULL,
ZipCode varchar(5) NOT NULL,
Country varchar(max) NOT NULL,
FederalTaxID text NOT NULL,
PhoneNumber varchar(15) NOT NULL,
FaxNumber varchar(15) NULL,
EmailAddressFormat varchar(max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF

USE BusinessStudio
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE Users_Auth(
UsersPK int IDENTITY(1,1) NOT NULL,
Username varchar(50) NOT NULL,
Password varchar(50) NOT NULL,
IsAdmin int NOT NULL,
IsLocked int NOT NULL,
IsOnline int NOT NULL,
LastLoginDate datetime NOT NULL,
LastPasswordChange datetime NOT NULL,
LastModifiedBy varchar(max) NULL,
LastModifiedDate varchar(max) NULL,
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED
(
UsersPK ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF

USE BusinessStudio
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE Users_History(
HistoryPK int NOT NULL,
UsersPKFK int NOT NULL,
EventDate datetime NOT NULL,
EventDesc varchar(max) NOT NULL,
CONSTRAINT PK_Users_History PRIMARY KEY CLUSTERED
(
HistoryPK ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE Users_History WITH CHECK ADD CONSTRAINT FK_Users_History_Users_Auth FOREIGN KEY(UsersPKFK)
REFERENCES Users_Auth (UsersPK)
ALTER TABLE Users_History CHECK CONSTRAINT FK_Users_History_Users_Auth

USE BusinessStudio
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE Users_Profile(
UsersProfilePK int IDENTITY(1,1) NOT NULL,
UsersPKFK int NULL,
Employee_Prefix varchar(50) NOT NULL,
Employee_FirstName varchar(max) NOT NULL,
Employee_MiddleInitial varchar(2) NULL,
Employee_LastName varchar(max) NOT NULL,
Employee_Suffix varchar(50) NULL,
Employee_Addr1 varchar(max) NOT NULL,
Employee_Addr2 varchar(max) NULL,
Employee_Addr3 varchar(max) NULL,
Employee_City varchar(max) NOT NULL,
Employee_State varchar(14) NOT NULL,
Employee_ZipCode varchar(10) NOT NULL,
Employee_HomePhone varchar(13) NOT NULL,
Employee_CellPhone varchar(13) NULL,
Employee_Pager varchar(13) NULL,
Employee_Fax varchar(13) NULL,
Employee_BusinessPhone varchar(13) NULL,
Employee_BusinessPhoneExt varchar(max) NULL,
Employee_BusinessEmail varchar(max) NULL,
Employee_PersonalEmail varchar(max) NULL,
Employee_Department varchar(max) NULL,
Employee_Floor varchar(max) NULL,
Employee_RoomSuiteNumber varchar(max) NULL,
Employee_SSN varchar(11) NOT NULL,
Employee_ANumber varchar(11) NULL,
Employee_Gender char(5) NOT NULL,
Employee_DOB datetime NOT NULL,
Employee_Age int NULL,
Employee_ID varchar(max) NULL,
Employee_StartDate datetime NOT NULL,
Employee_TermDate datetime NULL,
Employee_Wage money NULL,
Employee_Minor char(3) NOT NULL,
Employee_FullTime char(3) NOT NULL,
Employee_PartTime char(3) NOT NULL,
Employee_Consultant char(3) NOT NULL,
Employee_Type varchar(10) NULL,
NOK_Prefix varchar(50) NOT NULL,
NOK_FirstName varchar(max) NOT NULL,
NOK_MiddleInitial varchar(2) NULL,
NOK_LastName varchar(max) NOT NULL,
NOK_Suffix varchar(50) NULL,
NOK_Addr1 varchar(max) NOT NULL,
NOK_Addr2 varchar(max) NULL,
NOK_Addr3 varchar(max) NULL,
NOK_City varchar(max) NOT NULL,
NOK_State varchar(14) NOT NULL,
NOK_ZipCode varchar(10) NOT NULL,
NOK_HomePhone varchar(13) NOT NULL,
NOK_CellPhone varchar(13) NULL,
NOK_Pager varchar(13) NULL,
NOK_Fax varchar(13) NULL,
NOK_BusinessPhone varchar(13) NULL,
NOK_BusinessPhoneExt varchar(max) NULL,
NOK_BusinessEmail varchar(max) NULL,
NOK_PersonalEmail varchar(max) NULL,
NOK_Relationship varchar(20) NOT NULL,
LastModifiedBy varchar(max) NOT NULL,
LastModifiedDate datetime NOT NULL,
CONSTRAINT PK_Users_Profile PRIMARY KEY CLUSTERED
(
UsersProfilePK ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE Users_Profile WITH CHECK ADD CONSTRAINT FK_Users_Profile_Users_Auth FOREIGN KEY(UsersPKFK)
REFERENCES Users_Auth (UsersPK)
ALTER TABLE Users_Profile CHECK CONSTRAINT FK_Users_Profile_Users_Auth

[BrailleSchool]

thanks to all who offered suggestions and assistance