Link to home
Create AccountLog in
Avatar of G Scott
G ScottFlag for United States of America

asked on

Create SQL Table Default Value Error

I have a database that I scripted in SQL Server to get the create db SQL statement.  It works just fine in a SQL Query window, of course, but I am trying to do it from within VB.NET.  It creates the tables just fine, but the statements to set the [timestamp] columns to a default value of getdate() fails saying:
User generated image
Here is my entire code that creates all the tables.  At the point of the error all the tables have been created correctly.


  Dim connectString As String = "Data Source=MyServer\sqlexpress;Initial Catalog=MyDatabase;User Id=User;Password=Pass1;"

        Dim objConn As SqlConnection = New SqlConnection(connectString)
        objConn.Open()

        Dim command As SqlCommand = New SqlCommand("CREATE TABLE [dbo].[forkliftStatus2]([forkliftNumber] [nvarchar](max) NULL,[status] [nchar](1) NULL,      [TempID] [int] IDENTITY(1,1) NOT NULL,      [comments] [nvarchar](max) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]", objConn)
        Dim reader As SqlDataReader = command.ExecuteReader()
        reader.Close()

        Dim command2 As SqlCommand = New SqlCommand(" CREATE TABLE [dbo].[mainthistory2](" & _
 "[forkliftnumber] [nvarchar](50) NULL," & _
 "[timestamp] [nvarchar](50) NULL," & _
 "[employee] [nvarchar](50) NULL," & _
 "[status] [nchar](1) NULL," & _
 "[comments] [nvarchar](max) NULL," & _
 "[returnedstatus] [nvarchar](50) NULL" & _
") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]", objConn)

        Dim reader2 As SqlDataReader = command2.ExecuteReader()
        reader2.Close()


        Dim command3 As SqlCommand = New SqlCommand("CREATE TABLE [dbo].[tblCurrent2](" & _
 "[forklift] [nvarchar](50) NULL," & _
 "[tires] [nchar](1) NULL," & _
  "[hydcont] [nchar](1) NULL," & _
 "[hydleak] [nchar](1) NULL," & _
 "[steering] [nchar](1) NULL," & _
 "[brakes] [nchar](1) NULL," & _
 "[horn] [nchar](1) NULL," & _
 "[alarm] [nchar](1) NULL," & _
 "[battery] [nchar](1) NULL," & _
 "[headlights] [nchar](1) NULL," & _
 "[warning] [nchar](1) NULL," & _
 "[gauges] [nchar](1) NULL," & _
 "[ground] [nchar](1) NULL," & _
 "[seatbelt] [nchar](1) NULL," & _
 "[signs] [nchar](1) NULL," & _
 "[operator] [nvarchar](50) NULL," & _
 "[timestamp] [nvarchar](50) NULL," & _
 "[ID] [int] IDENTITY(1,1) NOT NULL," & _
 "[status] [nchar](1) NULL," & _
 "[comments] [nvarchar](max) NULL," & _
 "[thetime] [nvarchar](50) NULL" & _
") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]", objConn)
        Dim reader3 As SqlDataReader = command3.ExecuteReader()
        reader3.Close()


        Dim command4 As SqlCommand = New SqlCommand("CREATE TABLE [dbo].[tblHistory2](" & _
 "[forklift] [nvarchar](50) NULL," & _
 "[tires] [nchar](10) NULL," & _
 "[hydcont] [nchar](10) NULL," & _
 "[hydleak] [nchar](10) NULL," & _
 "[steering] [nchar](10) NULL," & _
 "[brakes] [nchar](10) NULL," & _
 "[horn] [nchar](10) NULL," & _
 "[alarm] [nchar](10) NULL," & _
 "[battery] [nchar](10) NULL," & _
 "[headlights] [nchar](10) NULL," & _
 "[warning] [nchar](10) NULL," & _
 "[gauges] [nchar](10) NULL," & _
 "[ground] [nchar](10) NULL," & _
 "[seatbelt] [nchar](10) NULL," & _
 "[signs] [nchar](10) NULL," & _
 "[operator] [nvarchar](50) NULL," & _
 "[timestamp] [nvarchar](50) NULL," & _
 "[ID] [int] IDENTITY(1,1) NOT NULL," & _
 "[Shift] [nchar](10) NULL," & _
 "[status] [nchar](1) NULL," & _
 "[comments] [nvarchar](max) NULL" & _
") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]", objConn)
        Dim reader4 As SqlDataReader = command4.ExecuteReader()
        reader4.Close()

        Dim command5 As SqlCommand = New SqlCommand("CREATE TABLE [dbo].[notification2]([contacts] [nvarchar](50) NULL,[active] [nchar](1) NULL) ON [PRIMARY]", objConn)
        Dim reader5 As SqlDataReader = command5.ExecuteReader()
        reader5.Close()
'********Error Lines and assuming commands 7 and 8 would fail too************
        Dim command6 As SqlCommand = New SqlCommand("ALTER TABLE [dbo].[mainthistory] ADD  CONSTRAINT [DF_mainthistory_timestamp]  DEFAULT (getdate()) FOR [timestamp]", objConn)
        Dim reader6 As SqlDataReader = command6.ExecuteReader()
        reader6.Close()

        Dim command7 As SqlCommand = New SqlCommand("ALTER TABLE [dbo].[tblCurrent] ADD  CONSTRAINT [DF_tblCurrent_timestamp]  DEFAULT (getdate()) FOR [timestamp]", objConn)
        Dim reader7 As SqlDataReader = command7.ExecuteReader()
        reader7.Close()

        Dim command8 As SqlCommand = New SqlCommand("ALTER TABLE [dbo].[tblHistory] ADD  CONSTRAINT [DF_tblHistory_timestamp]  DEFAULT (getdate()) FOR [timestamp]", objConn)
        Dim reader8 As SqlDataReader = command8.ExecuteReader()
        reader8.Close()

        objConn.Close()
       
    End Sub


Thanks for any input on this.




By the way, on a side note.  I have opened this question about code tags.  I am not sure what the accepted thing to do is. Some like it, more people apparently don't.

https://www.experts-exchange.com/questions/28124115/To-Code-Tag-or-Not-To-Code-Tag.html
ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
PS: I like code tags!
PS2: you can use .ExecuteNonQuery() if there's nothing being returned. Creating a reader and just closing it is not necessary.
Avatar of G Scott

ASKER

So, how do I add a column [timestamp] and give it a default value of getdate()?  Do I do that in the initial table creation or do I add the default with the full table creation?

Thanks for the tip on the reader.  I found a solution on the interwebs and just went with it.
Avatar of G Scott

ASKER

"[timestamp] [nvarchar](50) default getdate(),"

Thanks Robert, you put me on track to figure out how to do the above.  I didn't know I could do it this way. Thanks!!!!