I'm developing an application in VB 2005 Express using SQL 2005 Express. I need to put a timestamp into my table each time I create a row...
The following is a snippet...
Dim DDate As [SqlDateTime] = Now()
Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' " & DDate & " ')"
Which won't work as I am attempting to concatinate a SqlDateTime into a string.
My best guess is that I need to somehow to use a DEFAULT value in the table that persists so each time a row is created the datetime it was created is saved with the row, rather than being re-calculated each time the table is opened. There are probably several other ways of doing it and this may not be the easiest.
I'm not a programmer, just an Engineer, so I can only read Help for 5 minutes at a time.
Hi Ian,
What is the data type of the field named Date_Time? What is the error you are getting?
I would suggest removing the quotes from your query and just letting SQL handle the data type change from SqlDateTime into what ever Date_Time is defined as. If it is a string, the conversion will likely happen automatically. (At least it does in T-SQL.)
If that doesn't work, consider using a type conversion function rather than just wraping the value in quotes. You should be able to find information about data type conversion functions in the VB help documentations.
One final consideration, if you only need to determine if a row has been updated and the actual time is not really important, you might consider the timestamp data type. This data type automatically updates when a row is added or changed, but it stores a relative time, no the time associated with a clock.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
Mike,
In order: "datetime"
The error I get is a VB pre-complier error warning that I am attempting to mix "String" with "SqlDateTime".
Removing the quotes does not help as the connection to the database is made with the following command:
Dim TheCommand As SqlCommand = New SqlCommand(TheQuery, TheConnection)
Where both "TheQuery" and "TheConnection" are strings.
Unfortunately I need to know the time and date, this will be used for locating information later.
My most recent attempts have used :
Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' GETDATE() ')"
Which throws "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." on execution (of the VB program).
I have attempted to set the default on the Date_Time column in "Groups" to be calculated as Getdate() and persistent, but then I get the following error: "Computed column 'DateTime' in table 'Tmp_Groups' cannot be persisted because the column is non-deterministic." I note that a calculated column in the table has no "format", which makes sense if it is not to be saved.
I am assuming that this is the correct forum, I think that the problem is related to SQL rather than .NET or VB.
I will be off the air now for 54 hours due to the weekend.
Thanks for your time Mike.
Ian
|||
It's interesting that you're getting an error specifying a timestamp field. Timestamp is different than datetime, you should make sure that you really have a datetime field.
That said, you're still trying to create string values by putting quotes around them. That isn't how you do it, you have to conver non-string values into strings using a conversion function, in this case CStr(). I managed to write an insert from VB by converting the output of Now() to a string and passing that in my query. I've povided the table definition and VB code below for you to examine.
Table Def-
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Shippers](
[ShipperID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Phone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tsTime] [datetime] NULL,
[sTime] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [aaaaaShippers_PK] PRIMARY KEY NONCLUSTERED
(
[ShipperID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-- End Table -
- Code--
Sub Main()
Dim cnn As New SqlConnection
cnn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=NorthwindSQL_Upsize;Integrated Security=True"
cnn.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO Shippers (CompanyName, Phone, tsTime) VALUES('VB Shippers','555-1212','" & CStr(Now()) & "')"
cmd.Connection = cnn
cmd.ExecuteNonQuery()
End Sub
- End Code --
|||Mike,
A very astute observation, I do have an issue with the column format.
A search of my harddisk shows a total of 8 copies of this database, two I have as backups, one is a copy I was importing data into using Management Studio, the rest have uncertain pedigree, (some I no doubt created in error).
I long for the days of SQL Server 2000 when I only kept two copies of the database, (one live & one copy). Having a cut-back latest version makes it very tempting to develop & use the latest, but I wonder if SQL Server Express has been emasculated to the extent that it is no longer worthy of the title "Database". Yes, the mistake was mine, I think it was an easier mistake to make due to the "over enthusiastic feature removal".
Thank you for you help Mike.
Ian
sql