?
Solved

Create SQL Table Default Value Error

Posted on 2013-05-10
5
Medium Priority
?
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month12 days, 14 hours left to enroll

777 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