Increased Datetime Precision

Any ideas on how to get greater than the 3.33 ms precision currently
available in the DataTime type?
Thanks,
Randy LRandy,
What are your requirements? What do you need a better precision for?|||What are you trying to do? DateTime has 1ms increments. DateTime.Now is
something like +- 50ms accurate.
William Stacey [MVP]
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
| Any ideas on how to get greater than the 3.33 ms precision currently
| available in the DataTime type?
|
| Thanks,
| Randy L
|
||||Store the milliseconds separately, in a SMALLINT column perhaps. To balance
the increased storage you could use SMALLDATETIME and then the number of
seconds and milliseconds separately.
"Randy L." <randy.luchsinger@.community.nospam> wrote in message
news:uTZQiIXPGHA.1088@.tk2msftngp13.phx.gbl...
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L
>|||The precision you mention is the maximum precision of the datetime data
type.
If you need to store dates with times of greater precision in the time
portion, then you will have to program a solution yourself. One approach
is to use a smalldatetime (provided your dates fall within the range
supported by smalldatetime), and store the second and subsecond portion
of the timestamp in an int column as number of (remaining) milliseconds.
For example, to store the datetime 2006-03-01T22:19:03.999, you could
store it as:
CAST('2006-03-01T22:19:03.999' AS smalldatetime) AS
MostSignificantDateTimePart
,CAST(CAST(SUBSTRING('2006-03-01T22:19:03.999',18,5) AS decimal(5,3)) *
1000 AS int) AS LeastSignificantDateTimePart
HTH,
Gert-Jan
"Randy L." wrote:
> Any ideas on how to get greater than the 3.33 ms precision currently
> available in the DataTime type?
> Thanks,
> Randy L|||The transactions for which we are collecting this data occur very rapidly.
When we then store that time in SQL, the times are then truncated further.
As a result, ordering data by datetime does not always give the true order
of the transaction.
For example:
Trans# Actual Time Stored Time
-- -- --
--
1 2006-03-01 16:00:00.590014 2006-03-01 16:00:00.590
2 2006-03-01 16:00:00.590018 2006-03-01 16:00:00.590
3 2006-03-01 16:00:00.590030 2006-03-01 16:00:00.590
The result in SQL would be 3 times that are exactly the same.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141247397.749764.161370@.u72g2000cwu.googlegroups.com...
> Randy,
> What are your requirements? What do you need a better precision for?
>|||Sorry, wrong ng. Thought I was on c# ng.
William Stacey [MVP]|||Randy,
just from the top of my head: I guess an identity column will give you
the true order of inserts...|||> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
But not necessarily the true order of transaction date/time. The exact
moment in time may be coming from different systems, without synchronized
atomic precision (which is arguably more important in this case than in the
general case), and different system components might have lag times in which
the transactions are recorded (either because they are farther away or on
slower connections, or because they queue up their inserts, etc). Also, if
the table is populated via INSERT...SELECT...FROM there is no guarantee that
the IDENTITY values will be assigned in the 'expected' order.
A|||Alexander,
The transactions we track do not get inserted into SQL as they occur.
They occur across a variety of other processes and are then delivered to the
database. So the order by which they are inserted into the DB has no
significance.
Thanks,
Randy L
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141248868.318368.227020@.i39g2000cwa.googlegroups.com...
> Randy,
> just from the top of my head: I guess an identity column will give you
> the true order of inserts...
>

Incorrect syntax near the keyword 'WHERE'.

Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0

Below is the code I am using for an update to a MS Sql Database.

<%@. Language=VBScript %>
<% Option Explicit %>

<html>
<head>
<title>Sample Script 2 - Part 3 </title>
<!-- copyright MDFernandez -->
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->

<center>
<%

Dim oRS
Dim Conn

Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql

Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open

sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id

set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>
<!-- don't include in sample code display -->
<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>

</form>

</center>
</body>
</html>sql=sql & "PhoneNumber='" & PhoneNumber & "',"

It looks like the syntax error is due to the extraneous comma after the last
column.

I strongly suggest you google 'SQL injection'. Your current code will allow
a hacker can execute any arbitrary SQL statement. The best protection
against injection is to use parameterized SQL statements, stored procedures
and validate user input. Never build a SQL Statement string by
concatenating user input values. The example below uses a parameterized
UPDATE statement via OLEDB:

Const adParamInput = 1
Const adInteger = 3
Const adVarChar = 200

Set Conn = CreateObject("ADODB.Connection")
Set Command = CreateObject("ADODB.Command")

Conn.Open _
"Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=FriendsContactInfo;" & _
"App=AspRunner Professional Application"

Command.ActiveConnection = Conn

Command.CommandText = _
" UPDATE dbo.FPFriends" & _
" SET" & _
" Name=?," & _
" StreetAddress=?," & _
" Ciy=?," & _
" State=?," & _
" Zip=?," & _
" PhoneNumber=?" & _
" WHERE Id=?"

Set parameter = Command.CreateParameter( _
"Name", _
adVarChar, _
adParamInput, _
30)
parameter.Value = Name
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"StreetAddress", _
adVarChar, _
adParamInput, _
30)
parameter.Value = StreetAddress
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"City", _
adVarChar, _
adParamInput, _
30)
parameter.Value = City
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"State", _
adVarChar, _
adParamInput, _
2)
parameter.Value = State
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Zip", _
adVarChar, _
adParamInput, _
5)
parameter.Value = Zip
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"PhoneNumber", _
adVarChar, _
adParamInput, _
15)
parameter.Value = PhoneNumber
Command.Parameters.Append parameter

Set parameter = Command.CreateParameter( _
"Id", _
adInteger, _
adParamInput)
parameter.Value = Id
Command.Parameters.Append parameter

Command.Execute

Conn.Close

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DaveF" <jeacdf@.excite.comwrote in message
news:1173540573.073247.128620@.t69g2000cwt.googlegr oups.com...

Quote:

Originally Posted by

Any Ideas as to this error message. I am trying to learn using ms sql
server 7.0
>
Below is the code I am using for an update to a MS Sql Database.
>
<%@. Language=VBScript %>
<% Option Explicit %>
>
<html>
<head>
<title>Sample Script 2 - Part 3 </title>
<!-- copyright MDFernandez -->
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=../part3sol/style.css">
</head>
<body bgcolor="#FFFFFF">
<!--#include virtual="/adovbs.inc"-->
>
<center>
<%
>
Dim oRS
Dim Conn
>
Dim Id
Dim Name
Dim StreetAddress
Dim City
Dim State
Dim Zip
Dim PhoneNumber
dim sql
>
Id = request.form("Id")
Name = request.form("Name")
StreetAddress = request.form("StreetAddress")
City = request.form("City")
State = request.form("State")
Zip = request.form("Zip")
PhoneNumber = request.form("PhoneNumber")
>
>
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunner
Professional
Application;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")
'Conn.Open
>
>
sql="update FPFriends"
sql=sql & " set Name='" & Name & "',"
sql=sql & "StreetAddress='" & StreetAddress & "',"
sql=sql & "Ciy='" & City & "',"
sql=sql & "State='" & State & "',"
sql=sql & "Zip='" & Zip & "',"
sql=sql & "PhoneNumber='" & PhoneNumber & "',"
sql=sql & " WHERE Id=" & Id
>
>
>
set oRS=Conn.Execute (sql)
response.write "<font face='arial' size=4>"
response.write "<br><br>The record has been updated."
response.write "</b></font>"
' close the connection to the database
Conn.Close
%>
<!-- don't include in sample code display -->
<form>
<input type="button" value=" Close This Window "
onClick="window.location='aboutus.htm'"><br>
<button onClick="window.location='menu1_1.asp'">Update another
record</button>
>
</form>
>
</center>
</body>
</html>
>

Incorrect syntax near =

Hi there

I'm getting anIncorrect syntax near '='. error when I click update in my gridview.

Any ideas?

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Taskdb%>"
SelectCommand="SELECT [taskid], [taskname], [taskdescription], [taskstatus] FROM [tblTask]"
UpdateCommand="UPDATE [tbltask) set [taskname] = @.taskname, [taskdescription] = @.taskdescription, [taskstatus] = @.taskstatus where [taskid] = @.taskid">
<UpdateParameters>
<asp:Parameter Type="String" Name="taskname" />
<asp:Parameter Type="String" Name="taskdescription" />
<asp:Parameter Type="Int16" Name="taskstatus" />
<asp:Parameter Type="Int16" Name="taskid" />
</UpdateParameters>
</asp:SqlDataSource>

Thanks in advance.

looks like a typo

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Taskdb%>"
SelectCommand="SELECT [taskid], [taskname], [taskdescription], [taskstatus] FROM [tblTask]"
UpdateCommand="UPDATE[tbltask) set [taskname] = @.taskname, [taskdescription] = @.taskdescription, [taskstatus] = @.taskstatus where [taskid] = @.taskid">
<UpdateParameters>
<asp:Parameter Type="String" Name="taskname" />
<asp:Parameter Type="String" Name="taskdescription" />
<asp:Parameter Type="Int16" Name="taskstatus" />
<asp:Parameter Type="Int16" Name="taskid" />
</UpdateParameters>
</asp:SqlDataSource>

change the highlight section above to[tbltask]

|||

Thanks, how did I miss that! I must have looked at the code 100 times over.