Solved

Create SQL Table Default Value Error

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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