Solved

Create SQL Table Default Value Error

Posted on 2013-05-10
5
312 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

806 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