Solved

Create SQL Table Default Value Error

Posted on 2013-05-10
5
309 Views
Last Modified: 2013-05-10
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:
SQL Error
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.

http://www.experts-exchange.com/Community_Support/General/Q_28124115.html
0
Comment
Question by:G Scott
  • 3
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 39154916
You're creating table mainthistory2, but adding default to table mainthistory which probably does exist already?
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39154917
PS: I like code tags!
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39154922
PS2: you can use .ExecuteNonQuery() if there's nothing being returned. Creating a reader and just closing it is not necessary.
0
 
LVL 1

Author Comment

by:G Scott
ID: 39155014
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.
0
 
LVL 1

Author Closing Comment

by:G Scott
ID: 39155027
"[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!!!!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now