Help with first VB application

Help with first VB application - Data Entry form

microsoft.public.dotnet.languages.vb

8 Aug 2006 6:27 PM

Timothy.Rybak

Hello all,

This is my first attempt at an application, so kid gloves are
appreciated.

I need to make a very simple form that only has a few elements. One is
TraceCode - a text field that is populated when a user scans a label.
The other is ScanDate - a date/time field that should equal the
date/time of the scan (e.g. 7/31/2006 5:00:00 AM).

When a button is clicked, or Enter is pressed on the keyboard (or as
the last character of the scan), the data should be transmitted to a
SQL table, and the two text field should be cleared and ready for the
next scan.

Currently, I have a simple form, but you have to manually type the
ScanDate. Also, the fields don't clear after the update is successful.

Ideally, there would only be one textbox available to the user, and
when they scan the TraceCode, the ScanDate is automatically populated,
the data is transacted to the SQL table, and the fields clear, ready
for the next scan.

Here is the only code I have, and it is for the "GO" button":

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
        Dim objConnection As New SqlClient.SqlConnection _
            ("server=.;database=SafetyStock;trusted_connection=true")
        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
String

        Try

            objConnection.Open()
            objTransaction = objConnection.BeginTransaction

            objCommand.Transaction = objTransaction

            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
TraceCode) " & _
                "VALUES('" & txtDatetime.Text & "', '" &
txtTraceCode.Text & "')"
            objCommand.CommandText = strSQL
            objCommand.ExecuteNonQuery()

            objTransaction.Commit()
            objConnection.Close()

        Catch
            Button1.Text = "Failed"
            objTransaction.Rollback()
            objConnection.Close()

        End Try
    End Sub
End Class

Please HELP! :)

8 Aug 2006 6:40 PM

Scott M.

What, exactly, is your question?

<Timothy.Ry***@gmail.com> wrote in message
Show quoteHide quote

news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
> Hello all,
>
> This is my first attempt at an application, so kid gloves are
> appreciated.
>
> I need to make a very simple form that only has a few elements. One is
> TraceCode - a text field that is populated when a user scans a label.
> The other is ScanDate - a date/time field that should equal the
> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>
> When a button is clicked, or Enter is pressed on the keyboard (or as
> the last character of the scan), the data should be transmitted to a
> SQL table, and the two text field should be cleared and ready for the
> next scan.
>
> Currently, I have a simple form, but you have to manually type the
> ScanDate. Also, the fields don't clear after the update is successful.
>
> Ideally, there would only be one textbox available to the user, and
> when they scan the TraceCode, the ScanDate is automatically populated,
> the data is transacted to the SQL table, and the fields clear, ready
> for the next scan.
>
> Here is the only code I have, and it is for the "GO" button":
>
>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>        Dim objConnection As New SqlClient.SqlConnection _
>            ("server=.;database=SafetyStock;trusted_connection=true")
>        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
> String
>
>        Try
>
>            objConnection.Open()
>            objTransaction = objConnection.BeginTransaction
>
>            objCommand.Transaction = objTransaction
>
>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
> TraceCode) " & _
>                "VALUES('" & txtDatetime.Text & "', '" &
> txtTraceCode.Text & "')"
>            objCommand.CommandText = strSQL
>            objCommand.ExecuteNonQuery()
>
>            objTransaction.Commit()
>            objConnection.Close()
>
>        Catch
>            Button1.Text = "Failed"
>            objTransaction.Rollback()
>            objConnection.Close()
>
>        End Try
>    End Sub
> End Class
>
> Please HELP! :)
>

8 Aug 2006 6:55 PM

Timothy.Rybak

Sorry,

I need to accomplish the goals listed up top, but I don't know enough
about VB.net to get there. I am hoping someone can tell me how to
modify the given code to accomplish those goals.

Tim

Scott M. wrote:
Show quoteHide quote

> What, exactly, is your question?
>
>
> <Timothy.Ry***@gmail.com> wrote in message
> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
> > Hello all,
> >
> > This is my first attempt at an application, so kid gloves are
> > appreciated.
> >
> > I need to make a very simple form that only has a few elements. One is
> > TraceCode - a text field that is populated when a user scans a label.
> > The other is ScanDate - a date/time field that should equal the
> > date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
> >
> > When a button is clicked, or Enter is pressed on the keyboard (or as
> > the last character of the scan), the data should be transmitted to a
> > SQL table, and the two text field should be cleared and ready for the
> > next scan.
> >
> > Currently, I have a simple form, but you have to manually type the
> > ScanDate. Also, the fields don't clear after the update is successful.
> >
> > Ideally, there would only be one textbox available to the user, and
> > when they scan the TraceCode, the ScanDate is automatically populated,
> > the data is transacted to the SQL table, and the fields clear, ready
> > for the next scan.
> >
> > Here is the only code I have, and it is for the "GO" button":
> >
> >    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> > System.EventArgs) Handles Button1.Click
> >        Dim objConnection As New SqlClient.SqlConnection _
> >            ("server=.;database=SafetyStock;trusted_connection=true")
> >        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
> >        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
> > String
> >
> >        Try
> >
> >            objConnection.Open()
> >            objTransaction = objConnection.BeginTransaction
> >
> >            objCommand.Transaction = objTransaction
> >
> >            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
> > TraceCode) " & _
> >                "VALUES('" & txtDatetime.Text & "', '" &
> > txtTraceCode.Text & "')"
> >            objCommand.CommandText = strSQL
> >            objCommand.ExecuteNonQuery()
> >
> >            objTransaction.Commit()
> >            objConnection.Close()
> >
> >        Catch
> >            Button1.Text = "Failed"
> >            objTransaction.Rollback()
> >            objConnection.Close()
> >
> >        End Try
> >    End Sub
> > End Class
> >
> > Please HELP! :)
> >

8 Aug 2006 7:23 PM

Scott M.

It sounds like you need some specialized scanning hardware & software, which
is not part of .NET.

<Timothy.Ry***@gmail.com> wrote in message
Show quoteHide quote

news:1155063337.573909.154810@m79g2000cwm.googlegroups.com...
> Sorry,
>
> I need to accomplish the goals listed up top, but I don't know enough
> about VB.net to get there. I am hoping someone can tell me how to
> modify the given code to accomplish those goals.
>
> Tim
>
> Scott M. wrote:
>> What, exactly, is your question?
>>
>>
>> <Timothy.Ry***@gmail.com> wrote in message
>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>> > Hello all,
>> >
>> > This is my first attempt at an application, so kid gloves are
>> > appreciated.
>> >
>> > I need to make a very simple form that only has a few elements. One is
>> > TraceCode - a text field that is populated when a user scans a label.
>> > The other is ScanDate - a date/time field that should equal the
>> > date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>> >
>> > When a button is clicked, or Enter is pressed on the keyboard (or as
>> > the last character of the scan), the data should be transmitted to a
>> > SQL table, and the two text field should be cleared and ready for the
>> > next scan.
>> >
>> > Currently, I have a simple form, but you have to manually type the
>> > ScanDate. Also, the fields don't clear after the update is successful.
>> >
>> > Ideally, there would only be one textbox available to the user, and
>> > when they scan the TraceCode, the ScanDate is automatically populated,
>> > the data is transacted to the SQL table, and the fields clear, ready
>> > for the next scan.
>> >
>> > Here is the only code I have, and it is for the "GO" button":
>> >
>> >    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
>> > System.EventArgs) Handles Button1.Click
>> >        Dim objConnection As New SqlClient.SqlConnection _
>> >            ("server=.;database=SafetyStock;trusted_connection=true")
>> >        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>> >        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>> > String
>> >
>> >        Try
>> >
>> >            objConnection.Open()
>> >            objTransaction = objConnection.BeginTransaction
>> >
>> >            objCommand.Transaction = objTransaction
>> >
>> >            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>> > TraceCode) " & _
>> >                "VALUES('" & txtDatetime.Text & "', '" &
>> > txtTraceCode.Text & "')"
>> >            objCommand.CommandText = strSQL
>> >            objCommand.ExecuteNonQuery()
>> >
>> >            objTransaction.Commit()
>> >            objConnection.Close()
>> >
>> >        Catch
>> >            Button1.Text = "Failed"
>> >            objTransaction.Rollback()
>> >            objConnection.Close()
>> >
>> >        End Try
>> >    End Sub
>> > End Class
>> >
>> > Please HELP! :)
>> >
>

8 Aug 2006 7:35 PM

Timothy.Rybak

I don't think so really... all the scanner is doing is speeding up the
data entry. What if I changed it to say:

TraceCode - a textbox that a user types a 12 char string in to
ScanDate - when user presses enter key, a the date/time is captured

When the user hits the enter key, the two fields are transacted to the
SQL server and the two fields are cleared.

The only thing I don't have working is the stupid date/time. I figured
out that I can put a simple txtTraceCode.Text = "" to clear the field
after the transaction is completed.

The rest works perfectly given the code below (which was taken, almost
exactly out of Karl Moore's vidual Basic .NET: The Tutorials). All of
the SQL stuff works perfectly.

Thanks!
Tim

Scott M. wrote:
Show quoteHide quote

> It sounds like you need some specialized scanning hardware & software, which
> is not part of .NET.
>
>
> <Timothy.Ry***@gmail.com> wrote in message
> news:1155063337.573909.154810@m79g2000cwm.googlegroups.com...
> > Sorry,
> >
> > I need to accomplish the goals listed up top, but I don't know enough
> > about VB.net to get there. I am hoping someone can tell me how to
> > modify the given code to accomplish those goals.
> >
> > Tim
> >
> > Scott M. wrote:
> >> What, exactly, is your question?
> >>
> >>
> >> <Timothy.Ry***@gmail.com> wrote in message
> >> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
> >> > Hello all,
> >> >
> >> > This is my first attempt at an application, so kid gloves are
> >> > appreciated.
> >> >
> >> > I need to make a very simple form that only has a few elements. One is
> >> > TraceCode - a text field that is populated when a user scans a label.
> >> > The other is ScanDate - a date/time field that should equal the
> >> > date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
> >> >
> >> > When a button is clicked, or Enter is pressed on the keyboard (or as
> >> > the last character of the scan), the data should be transmitted to a
> >> > SQL table, and the two text field should be cleared and ready for the
> >> > next scan.
> >> >
> >> > Currently, I have a simple form, but you have to manually type the
> >> > ScanDate. Also, the fields don't clear after the update is successful.
> >> >
> >> > Ideally, there would only be one textbox available to the user, and
> >> > when they scan the TraceCode, the ScanDate is automatically populated,
> >> > the data is transacted to the SQL table, and the fields clear, ready
> >> > for the next scan.
> >> >
> >> > Here is the only code I have, and it is for the "GO" button":
> >> >
> >> >    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> >> > System.EventArgs) Handles Button1.Click
> >> >        Dim objConnection As New SqlClient.SqlConnection _
> >> >            ("server=.;database=SafetyStock;trusted_connection=true")
> >> >        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
> >> >        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
> >> > String
> >> >
> >> >        Try
> >> >
> >> >            objConnection.Open()
> >> >            objTransaction = objConnection.BeginTransaction
> >> >
> >> >            objCommand.Transaction = objTransaction
> >> >
> >> >            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
> >> > TraceCode) " & _
> >> >                "VALUES('" & txtDatetime.Text & "', '" &
> >> > txtTraceCode.Text & "')"
> >> >            objCommand.CommandText = strSQL
> >> >            objCommand.ExecuteNonQuery()
> >> >
> >> >            objTransaction.Commit()
> >> >            objConnection.Close()
> >> >
> >> >        Catch
> >> >            Button1.Text = "Failed"
> >> >            objTransaction.Rollback()
> >> >            objConnection.Close()
> >> >
> >> >        End Try
> >> >    End Sub
> >> > End Class
> >> >
> >> > Please HELP! :)
> >> >
> >

8 Aug 2006 8:20 PM

Terry

Not sure why you want to put the date in the text box when the enter key is
pressed, since you are going to immediatly send it to the database and then
clear it.
Basically you want to do the following:
txtDatetime.Text = Now().ToString
Since you said that the scan code is always 12 characters long, you could
put the following in the in the TextChanged event for the scan code:
If txtTraceCode.Text.Length = 12 then
txtDatetime.Text = Now().ToString
EndIf

--
Terry

Show quoteHide quote

"Timothy.Ry***@gmail.com" wrote:

> I don't think so really... all the scanner is doing is speeding up the
> data entry. What if I changed it to say:
>
> TraceCode - a textbox that a user types a 12 char string in to
> ScanDate - when user presses enter key, a the date/time is captured
>
> When the user hits the enter key, the two fields are transacted to the
> SQL server and the two fields are cleared.
>
> The only thing I don't have working is the stupid date/time. I figured
> out that I can put a simple txtTraceCode.Text = "" to clear the field
> after the transaction is completed.
>
> The rest works perfectly given the code below (which was taken, almost
> exactly out of Karl Moore's vidual Basic .NET: The Tutorials). All of
> the SQL stuff works perfectly.
>
> Thanks!
> Tim
>
>
> Scott M. wrote:
> > It sounds like you need some specialized scanning hardware & software, which
> > is not part of .NET.
> >
> >
> > <Timothy.Ry***@gmail.com> wrote in message
> > news:1155063337.573909.154810@m79g2000cwm.googlegroups.com...
> > > Sorry,
> > >
> > > I need to accomplish the goals listed up top, but I don't know enough
> > > about VB.net to get there. I am hoping someone can tell me how to
> > > modify the given code to accomplish those goals.
> > >
> > > Tim
> > >
> > > Scott M. wrote:
> > >> What, exactly, is your question?
> > >>
> > >>
> > >> <Timothy.Ry***@gmail.com> wrote in message
> > >> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
> > >> > Hello all,
> > >> >
> > >> > This is my first attempt at an application, so kid gloves are
> > >> > appreciated.
> > >> >
> > >> > I need to make a very simple form that only has a few elements. One is
> > >> > TraceCode - a text field that is populated when a user scans a label.
> > >> > The other is ScanDate - a date/time field that should equal the
> > >> > date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
> > >> >
> > >> > When a button is clicked, or Enter is pressed on the keyboard (or as
> > >> > the last character of the scan), the data should be transmitted to a
> > >> > SQL table, and the two text field should be cleared and ready for the
> > >> > next scan.
> > >> >
> > >> > Currently, I have a simple form, but you have to manually type the
> > >> > ScanDate. Also, the fields don't clear after the update is successful.
> > >> >
> > >> > Ideally, there would only be one textbox available to the user, and
> > >> > when they scan the TraceCode, the ScanDate is automatically populated,
> > >> > the data is transacted to the SQL table, and the fields clear, ready
> > >> > for the next scan.
> > >> >
> > >> > Here is the only code I have, and it is for the "GO" button":
> > >> >
> > >> >    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> > >> > System.EventArgs) Handles Button1.Click
> > >> >        Dim objConnection As New SqlClient.SqlConnection _
> > >> >            ("server=.;database=SafetyStock;trusted_connection=true")
> > >> >        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
> > >> >        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
> > >> > String
> > >> >
> > >> >        Try
> > >> >
> > >> >            objConnection.Open()
> > >> >            objTransaction = objConnection.BeginTransaction
> > >> >
> > >> >            objCommand.Transaction = objTransaction
> > >> >
> > >> >            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
> > >> > TraceCode) " & _
> > >> >                "VALUES('" & txtDatetime.Text & "', '" &
> > >> > txtTraceCode.Text & "')"
> > >> >            objCommand.CommandText = strSQL
> > >> >            objCommand.ExecuteNonQuery()
> > >> >
> > >> >            objTransaction.Commit()
> > >> >            objConnection.Close()
> > >> >
> > >> >        Catch
> > >> >            Button1.Text = "Failed"
> > >> >            objTransaction.Rollback()
> > >> >            objConnection.Close()
> > >> >
> > >> >        End Try
> > >> >    End Sub
> > >> > End Class
> > >> >
> > >> > Please HELP! :)
> > >> >
> > >
>
>

8 Aug 2006 8:27 PM

Scott M.

You simply want the date to populate a textbox when a button is pressed?

Sub Button1_Click() Handles Button1.Click
txtDate.Text = Now.ToShortDateString
End Sub

If you want to clear the textboxes, just set their EnableViewState
properties to False and they will clear automatically after posting to the
server.

<Timothy.Ry***@gmail.com> wrote in message
Show quoteHide quote

news:1155065729.775674.23990@p79g2000cwp.googlegroups.com...
>I don't think so really... all the scanner is doing is speeding up the
> data entry. What if I changed it to say:
>
> TraceCode - a textbox that a user types a 12 char string in to
> ScanDate - when user presses enter key, a the date/time is captured
>
> When the user hits the enter key, the two fields are transacted to the
> SQL server and the two fields are cleared.
>
> The only thing I don't have working is the stupid date/time. I figured
> out that I can put a simple txtTraceCode.Text = "" to clear the field
> after the transaction is completed.
>
> The rest works perfectly given the code below (which was taken, almost
> exactly out of Karl Moore's vidual Basic .NET: The Tutorials). All of
> the SQL stuff works perfectly.
>
> Thanks!
> Tim
>
>
> Scott M. wrote:
>> It sounds like you need some specialized scanning hardware & software,
>> which
>> is not part of .NET.
>>
>>
>> <Timothy.Ry***@gmail.com> wrote in message
>> news:1155063337.573909.154810@m79g2000cwm.googlegroups.com...
>> > Sorry,
>> >
>> > I need to accomplish the goals listed up top, but I don't know enough
>> > about VB.net to get there. I am hoping someone can tell me how to
>> > modify the given code to accomplish those goals.
>> >
>> > Tim
>> >
>> > Scott M. wrote:
>> >> What, exactly, is your question?
>> >>
>> >>
>> >> <Timothy.Ry***@gmail.com> wrote in message
>> >> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>> >> > Hello all,
>> >> >
>> >> > This is my first attempt at an application, so kid gloves are
>> >> > appreciated.
>> >> >
>> >> > I need to make a very simple form that only has a few elements. One
>> >> > is
>> >> > TraceCode - a text field that is populated when a user scans a
>> >> > label.
>> >> > The other is ScanDate - a date/time field that should equal the
>> >> > date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>> >> >
>> >> > When a button is clicked, or Enter is pressed on the keyboard (or as
>> >> > the last character of the scan), the data should be transmitted to a
>> >> > SQL table, and the two text field should be cleared and ready for
>> >> > the
>> >> > next scan.
>> >> >
>> >> > Currently, I have a simple form, but you have to manually type the
>> >> > ScanDate. Also, the fields don't clear after the update is
>> >> > successful.
>> >> >
>> >> > Ideally, there would only be one textbox available to the user, and
>> >> > when they scan the TraceCode, the ScanDate is automatically
>> >> > populated,
>> >> > the data is transacted to the SQL table, and the fields clear, ready
>> >> > for the next scan.
>> >> >
>> >> > Here is the only code I have, and it is for the "GO" button":
>> >> >
>> >> >    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
>> >> > As
>> >> > System.EventArgs) Handles Button1.Click
>> >> >        Dim objConnection As New SqlClient.SqlConnection _
>> >> >            ("server=.;database=SafetyStock;trusted_connection=true")
>> >> >        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>> >> >        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>> >> > String
>> >> >
>> >> >        Try
>> >> >
>> >> >            objConnection.Open()
>> >> >            objTransaction = objConnection.BeginTransaction
>> >> >
>> >> >            objCommand.Transaction = objTransaction
>> >> >
>> >> >            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>> >> > TraceCode) " & _
>> >> >                "VALUES('" & txtDatetime.Text & "', '" &
>> >> > txtTraceCode.Text & "')"
>> >> >            objCommand.CommandText = strSQL
>> >> >            objCommand.ExecuteNonQuery()
>> >> >
>> >> >            objTransaction.Commit()
>> >> >            objConnection.Close()
>> >> >
>> >> >        Catch
>> >> >            Button1.Text = "Failed"
>> >> >            objTransaction.Rollback()
>> >> >            objConnection.Close()
>> >> >
>> >> >        End Try
>> >> >    End Sub
>> >> > End Class
>> >> >
>> >> > Please HELP! :)
>> >> >
>> >
>

9 Aug 2006 4:56 AM

Cor Ligthert [MVP]

Timothy,

I assume that your date on the database is in DateTime format.

To set a datetime string as you get it to a DateTime that are four commands
(they are internally almost all the same), in sequence of easy to use are
that

dim mydate as datetime = CDate(scannedDate)
http://msdn2.microsoft.com/en-us/library/2dt118h2.aspx

,, = Convert.ToDateTime(scannedDate)
http://msdn2.microsoft.com/en-us/library/2dt118h2.aspx

,, = DateTime.Parse(overloaded)
http://msdn2.microsoft.com/en-us/library/ms289484.aspx

,, = DateTime.ParseExact(overloaded)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclassparseexacttopic.asp

Have a look at those, although I assume that the first one will directly
work fine for you.

And don't forget to use parameters
http://www.vb-tips.com/dbpages.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071

I hope this helps,

Cor

<Timothy.Ry***@gmail.com> schreef in bericht
Show quoteHide quote

9 Aug 2006 12:03 PM

jeff

Comment...

Getting the Date / Time from the 'client' machine can lead to 'false' date /
time stamping...ie the user has messed with the system clock and so on ...
If this time will be used for control / report purposes, I would recommend
either ...

a. Have the database fill the datetime stamp with a default obtained from
the server ...

b. Create a function to get the current date / time from the server or a
common source.

Question - What do you considered the 'Time Stamp' for the scan ... when
item is scanned or when the record is saved ... if scanned, you will need to
get the date from a common source ... if saved, set the default value in the
data table to get the current date time on the server and only issue an
insert with the text field... and let the server fill the date/time stamp.

Jeff.

PS:

b: - Get a data from a common place...

    Assuming MSSQL Server Database...
    SELECT GetDate();
    using a ExecuteScaler on an OLEDBConnection ...

    Using MSAccess...
    SELECT Now();
    using a ExecuteScaler on an OLEDBConnection.

<Timothy.Ry***@gmail.com> wrote in message
Show quoteHide quote

9 Aug 2006 4:06 PM

Scott M.

Hold on Jeff. What if the client, web server and database are in diffeent
time zones? I don't think anyone suggested using the client for the date,
but I think the server should generate that, not the database.

Show quoteHide quote

"jeff" <jhersey at allnorth dottt com> wrote in message
news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>
> Comment...
>
> Getting the Date / Time from the 'client' machine can lead to 'false' date
> / time stamping...ie the user has messed with the system clock and so on
> ... If this time will be used for control / report purposes, I would
> recommend either ...
>
> a. Have the database fill the datetime stamp with a default obtained from
> the server ...
>
> b. Create a function to get the current date / time from the server or a
> common source.
>
> Question - What do you considered the 'Time Stamp' for the scan ... when
> item is scanned or when the record is saved ... if scanned, you will need
> to get the date from a common source ... if saved, set the default value
> in the data table to get the current date time on the server and only
> issue an insert with the text field... and let the server fill the
> date/time stamp.
>
> Jeff.
>
> PS:
>
> b: - Get a data from a common place...
>
>    Assuming MSSQL Server Database...
>    SELECT GetDate();
>    using a ExecuteScaler on an OLEDBConnection ...
>
>    Using MSAccess...
>    SELECT Now();
>    using a ExecuteScaler on an OLEDBConnection.
>
>
>
>
> <Timothy.Ry***@gmail.com> wrote in message
> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>> Hello all,
>>
>> This is my first attempt at an application, so kid gloves are
>> appreciated.
>>
>> I need to make a very simple form that only has a few elements. One is
>> TraceCode - a text field that is populated when a user scans a label.
>> The other is ScanDate - a date/time field that should equal the
>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>
>> When a button is clicked, or Enter is pressed on the keyboard (or as
>> the last character of the scan), the data should be transmitted to a
>> SQL table, and the two text field should be cleared and ready for the
>> next scan.
>>
>> Currently, I have a simple form, but you have to manually type the
>> ScanDate. Also, the fields don't clear after the update is successful.
>>
>> Ideally, there would only be one textbox available to the user, and
>> when they scan the TraceCode, the ScanDate is automatically populated,
>> the data is transacted to the SQL table, and the fields clear, ready
>> for the next scan.
>>
>> Here is the only code I have, and it is for the "GO" button":
>>
>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
>> System.EventArgs) Handles Button1.Click
>>        Dim objConnection As New SqlClient.SqlConnection _
>>            ("server=.;database=SafetyStock;trusted_connection=true")
>>        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>> String
>>
>>        Try
>>
>>            objConnection.Open()
>>            objTransaction = objConnection.BeginTransaction
>>
>>            objCommand.Transaction = objTransaction
>>
>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>> TraceCode) " & _
>>                "VALUES('" & txtDatetime.Text & "', '" &
>> txtTraceCode.Text & "')"
>>            objCommand.CommandText = strSQL
>>            objCommand.ExecuteNonQuery()
>>
>>            objTransaction.Commit()
>>            objConnection.Close()
>>
>>        Catch
>>            Button1.Text = "Failed"
>>            objTransaction.Rollback()
>>            objConnection.Close()
>>
>>        End Try
>>    End Sub
>> End Class
>>
>> Please HELP! :)
>>
>
>

9 Aug 2006 6:03 PM

jeff

....exactly... my point.

what if you have multiple clients in different time zones ... multiple web
servers in different time zones ... and multiple databases servers in
different time zones... with a user from one time zone, connecting to a web
server in another and a database in a third ... and the next time the user
log-ins ... same CLIENT machine ... he hits a different web server and
different database for his next scan transaction ... you need some type of
'control' in the date stamping ... and to me the logical one is the Database
Server.

you need to set a base line for time stamping ... and to me, relying on the
CLIENT for this, is not good practice...as per you suggestion...

Sub Button1_Click() Handles Button1.Click
txtDate.Text = Now.ToShortDateString
End Sub

I simply made a suggestion to use the database as the control date / time
'stamper' (for lack of a better term). If you have different databases in
different time zones, when you synchronize your data ... if you are not
identifying which server the record was initially created on, you offset you
timestamps accordingly during the sync process ... or when you build a
consolidated report to include data from other database servers in different
timezones, you offset your timestamps based on the database server's
'timezone' ...

relying on the CLIENT is poor practice and has a hard time standing up to an
audit ... unless you have a mechanism / policy in place that controls the
time on a CLIENT machines.

Just my 2 cents ... just offering a suggestion thats all.

Jeff

Show quoteHide quote

"Scott M." <s-mar@nospam.nospam> wrote in message
news:eHymO18uGHA.1224@TK2MSFTNGP03.phx.gbl...
> Hold on Jeff. What if the client, web server and database are in diffeent
> time zones? I don't think anyone suggested using the client for the date,
> but I think the server should generate that, not the database.
>
>
>
>
> "jeff" <jhersey at allnorth dottt com> wrote in message
> news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>>
>> Comment...
>>
>> Getting the Date / Time from the 'client' machine can lead to 'false'
>> date / time stamping...ie the user has messed with the system clock and
>> so on ... If this time will be used for control / report purposes, I
>> would recommend either ...
>>
>> a. Have the database fill the datetime stamp with a default obtained from
>> the server ...
>>
>> b. Create a function to get the current date / time from the server or a
>> common source.
>>
>> Question - What do you considered the 'Time Stamp' for the scan ... when
>> item is scanned or when the record is saved ... if scanned, you will need
>> to get the date from a common source ... if saved, set the default value
>> in the data table to get the current date time on the server and only
>> issue an insert with the text field... and let the server fill the
>> date/time stamp.
>>
>> Jeff.
>>
>> PS:
>>
>> b: - Get a data from a common place...
>>
>>    Assuming MSSQL Server Database...
>>    SELECT GetDate();
>>    using a ExecuteScaler on an OLEDBConnection ...
>>
>>    Using MSAccess...
>>    SELECT Now();
>>    using a ExecuteScaler on an OLEDBConnection.
>>
>>
>>
>>
>> <Timothy.Ry***@gmail.com> wrote in message
>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>>> Hello all,
>>>
>>> This is my first attempt at an application, so kid gloves are
>>> appreciated.
>>>
>>> I need to make a very simple form that only has a few elements. One is
>>> TraceCode - a text field that is populated when a user scans a label.
>>> The other is ScanDate - a date/time field that should equal the
>>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>>
>>> When a button is clicked, or Enter is pressed on the keyboard (or as
>>> the last character of the scan), the data should be transmitted to a
>>> SQL table, and the two text field should be cleared and ready for the
>>> next scan.
>>>
>>> Currently, I have a simple form, but you have to manually type the
>>> ScanDate. Also, the fields don't clear after the update is successful.
>>>
>>> Ideally, there would only be one textbox available to the user, and
>>> when they scan the TraceCode, the ScanDate is automatically populated,
>>> the data is transacted to the SQL table, and the fields clear, ready
>>> for the next scan.
>>>
>>> Here is the only code I have, and it is for the "GO" button":
>>>
>>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
>>> System.EventArgs) Handles Button1.Click
>>>        Dim objConnection As New SqlClient.SqlConnection _
>>>            ("server=.;database=SafetyStock;trusted_connection=true")
>>>        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>>> String
>>>
>>>        Try
>>>
>>>            objConnection.Open()
>>>            objTransaction = objConnection.BeginTransaction
>>>
>>>            objCommand.Transaction = objTransaction
>>>
>>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>>> TraceCode) " & _
>>>                "VALUES('" & txtDatetime.Text & "', '" &
>>> txtTraceCode.Text & "')"
>>>            objCommand.CommandText = strSQL
>>>            objCommand.ExecuteNonQuery()
>>>
>>>            objTransaction.Commit()
>>>            objConnection.Close()
>>>
>>>        Catch
>>>            Button1.Text = "Failed"
>>>            objTransaction.Rollback()
>>>            objConnection.Close()
>>>
>>>        End Try
>>>    End Sub
>>> End Class
>>>
>>> Please HELP! :)
>>>
>>
>>
>
>

9 Aug 2006 9:02 PM

Scott M.

Yes, but again, I don't think anyone was suggesting the client in the first
place.

In my opinion, the database is not the place to put a time stamp of this
kind. While I agree that good primary keys are partially time based, this
is not what the OP has stated the time would be used for. The time is going
to become data that is used by the business layer of the applicaiton.
Databases should not be in charge of creating business data in this manner.

Show quoteHide quote

"jeff" <jhersey at allnorth dottt com> wrote in message
news:OvnwS49uGHA.324@TK2MSFTNGP06.phx.gbl...
>
> ...exactly... my point.
>
> what if you have multiple clients in different time zones ... multiple web
> servers in different time zones ... and multiple databases servers in
> different time zones... with a user from one time zone, connecting to a
> web server in another and a database in a third ... and the next time the
> user log-ins ... same CLIENT machine ... he hits a different web server
> and different database for his next scan transaction ... you need some
> type of 'control' in the date stamping ... and to me the logical one is
> the Database Server.
>
> you need to set a base line for time stamping ... and to me, relying on
> the CLIENT for this, is not good practice...as per you suggestion...
>
> Sub Button1_Click() Handles Button1.Click
>    txtDate.Text = Now.ToShortDateString
> End Sub
>
> I simply made a suggestion to use the database as the control date / time
> 'stamper' (for lack of a better term). If you have different databases in
> different time zones, when you synchronize your data ... if you are not
> identifying which server the record was initially created on, you offset
> you timestamps accordingly during the sync process ... or when you build a
> consolidated report to include data from other database servers in
> different timezones, you offset your timestamps based on the database
> server's 'timezone' ...
>
> relying on the CLIENT is poor practice and has a hard time standing up to
> an audit ... unless you have a mechanism / policy in place that controls
> the time on a CLIENT machines.
>
> Just my 2 cents ... just offering a suggestion thats all.
>
> Jeff
>
> "Scott M." <s-mar@nospam.nospam> wrote in message
> news:eHymO18uGHA.1224@TK2MSFTNGP03.phx.gbl...
>> Hold on Jeff. What if the client, web server and database are in
>> diffeent time zones? I don't think anyone suggested using the client for
>> the date, but I think the server should generate that, not the database.
>>
>>
>>
>>
>> "jeff" <jhersey at allnorth dottt com> wrote in message
>> news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>>>
>>> Comment...
>>>
>>> Getting the Date / Time from the 'client' machine can lead to 'false'
>>> date / time stamping...ie the user has messed with the system clock and
>>> so on ... If this time will be used for control / report purposes, I
>>> would recommend either ...
>>>
>>> a. Have the database fill the datetime stamp with a default obtained
>>> from the server ...
>>>
>>> b. Create a function to get the current date / time from the server or a
>>> common source.
>>>
>>> Question - What do you considered the 'Time Stamp' for the scan ... when
>>> item is scanned or when the record is saved ... if scanned, you will
>>> need to get the date from a common source ... if saved, set the default
>>> value in the data table to get the current date time on the server and
>>> only issue an insert with the text field... and let the server fill the
>>> date/time stamp.
>>>
>>> Jeff.
>>>
>>> PS:
>>>
>>> b: - Get a data from a common place...
>>>
>>>    Assuming MSSQL Server Database...
>>>    SELECT GetDate();
>>>    using a ExecuteScaler on an OLEDBConnection ...
>>>
>>>    Using MSAccess...
>>>    SELECT Now();
>>>    using a ExecuteScaler on an OLEDBConnection.
>>>
>>>
>>>
>>>
>>> <Timothy.Ry***@gmail.com> wrote in message
>>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>>>> Hello all,
>>>>
>>>> This is my first attempt at an application, so kid gloves are
>>>> appreciated.
>>>>
>>>> I need to make a very simple form that only has a few elements. One is
>>>> TraceCode - a text field that is populated when a user scans a label.
>>>> The other is ScanDate - a date/time field that should equal the
>>>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>>>
>>>> When a button is clicked, or Enter is pressed on the keyboard (or as
>>>> the last character of the scan), the data should be transmitted to a
>>>> SQL table, and the two text field should be cleared and ready for the
>>>> next scan.
>>>>
>>>> Currently, I have a simple form, but you have to manually type the
>>>> ScanDate. Also, the fields don't clear after the update is successful.
>>>>
>>>> Ideally, there would only be one textbox available to the user, and
>>>> when they scan the TraceCode, the ScanDate is automatically populated,
>>>> the data is transacted to the SQL table, and the fields clear, ready
>>>> for the next scan.
>>>>
>>>> Here is the only code I have, and it is for the "GO" button":
>>>>
>>>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
>>>> System.EventArgs) Handles Button1.Click
>>>>        Dim objConnection As New SqlClient.SqlConnection _
>>>>            ("server=.;database=SafetyStock;trusted_connection=true")
>>>>        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>>>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>>>> String
>>>>
>>>>        Try
>>>>
>>>>            objConnection.Open()
>>>>            objTransaction = objConnection.BeginTransaction
>>>>
>>>>            objCommand.Transaction = objTransaction
>>>>
>>>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>>>> TraceCode) " & _
>>>>                "VALUES('" & txtDatetime.Text & "', '" &
>>>> txtTraceCode.Text & "')"
>>>>            objCommand.CommandText = strSQL
>>>>            objCommand.ExecuteNonQuery()
>>>>
>>>>            objTransaction.Commit()
>>>>            objConnection.Close()
>>>>
>>>>        Catch
>>>>            Button1.Text = "Failed"
>>>>            objTransaction.Rollback()
>>>>            objConnection.Close()
>>>>
>>>>        End Try
>>>>    End Sub
>>>> End Class
>>>>
>>>> Please HELP! :)
>>>>
>>>
>>>
>>
>>
>
>

9 Aug 2006 9:46 PM

jeff

Okay... will stop here. We can continue agreeing to disagree.

Thanks
Jeff.

PS... never mentioned time based PK for you to agree with ... I do not like
these and avoid them.

PPS. you suggested the client get the time stamp ... txtDate.Text =
Now.ToShortDateString

Show quoteHide quote

"Scott M." <s-mar@nospam.nospam> wrote in message
news:%23fifBb$uGHA.1296@TK2MSFTNGP02.phx.gbl...
> Yes, but again, I don't think anyone was suggesting the client in the
> first place.
>
> In my opinion, the database is not the place to put a time stamp of this
> kind. While I agree that good primary keys are partially time based, this
> is not what the OP has stated the time would be used for. The time is
> going to become data that is used by the business layer of the
> applicaiton. Databases should not be in charge of creating business data
> in this manner.
>
> "jeff" <jhersey at allnorth dottt com> wrote in message
> news:OvnwS49uGHA.324@TK2MSFTNGP06.phx.gbl...
>>
>> ...exactly... my point.
>>
>> what if you have multiple clients in different time zones ... multiple
>> web servers in different time zones ... and multiple databases servers in
>> different time zones... with a user from one time zone, connecting to a
>> web server in another and a database in a third ... and the next time the
>> user log-ins ... same CLIENT machine ... he hits a different web server
>> and different database for his next scan transaction ... you need some
>> type of 'control' in the date stamping ... and to me the logical one is
>> the Database Server.
>>
>> you need to set a base line for time stamping ... and to me, relying on
>> the CLIENT for this, is not good practice...as per you suggestion...
>>
>> Sub Button1_Click() Handles Button1.Click
>>    txtDate.Text = Now.ToShortDateString
>> End Sub
>>
>> I simply made a suggestion to use the database as the control date / time
>> 'stamper' (for lack of a better term). If you have different databases
>> in different time zones, when you synchronize your data ... if you are
>> not identifying which server the record was initially created on, you
>> offset you timestamps accordingly during the sync process ... or when you
>> build a consolidated report to include data from other database servers
>> in different timezones, you offset your timestamps based on the database
>> server's 'timezone' ...
>>
>> relying on the CLIENT is poor practice and has a hard time standing up to
>> an audit ... unless you have a mechanism / policy in place that controls
>> the time on a CLIENT machines.
>>
>> Just my 2 cents ... just offering a suggestion thats all.
>>
>> Jeff
>>
>> "Scott M." <s-mar@nospam.nospam> wrote in message
>> news:eHymO18uGHA.1224@TK2MSFTNGP03.phx.gbl...
>>> Hold on Jeff. What if the client, web server and database are in
>>> diffeent time zones? I don't think anyone suggested using the client
>>> for the date, but I think the server should generate that, not the
>>> database.
>>>
>>>
>>>
>>>
>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>> news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>>>>
>>>> Comment...
>>>>
>>>> Getting the Date / Time from the 'client' machine can lead to 'false'
>>>> date / time stamping...ie the user has messed with the system clock and
>>>> so on ... If this time will be used for control / report purposes, I
>>>> would recommend either ...
>>>>
>>>> a. Have the database fill the datetime stamp with a default obtained
>>>> from the server ...
>>>>
>>>> b. Create a function to get the current date / time from the server or
>>>> a common source.
>>>>
>>>> Question - What do you considered the 'Time Stamp' for the scan ...
>>>> when item is scanned or when the record is saved ... if scanned, you
>>>> will need to get the date from a common source ... if saved, set the
>>>> default value in the data table to get the current date time on the
>>>> server and only issue an insert with the text field... and let the
>>>> server fill the date/time stamp.
>>>>
>>>> Jeff.
>>>>
>>>> PS:
>>>>
>>>> b: - Get a data from a common place...
>>>>
>>>>    Assuming MSSQL Server Database...
>>>>    SELECT GetDate();
>>>>    using a ExecuteScaler on an OLEDBConnection ...
>>>>
>>>>    Using MSAccess...
>>>>    SELECT Now();
>>>>    using a ExecuteScaler on an OLEDBConnection.
>>>>
>>>>
>>>>
>>>>
>>>> <Timothy.Ry***@gmail.com> wrote in message
>>>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>>>>> Hello all,
>>>>>
>>>>> This is my first attempt at an application, so kid gloves are
>>>>> appreciated.
>>>>>
>>>>> I need to make a very simple form that only has a few elements. One
>>>>> is
>>>>> TraceCode - a text field that is populated when a user scans a label.
>>>>> The other is ScanDate - a date/time field that should equal the
>>>>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>>>>
>>>>> When a button is clicked, or Enter is pressed on the keyboard (or as
>>>>> the last character of the scan), the data should be transmitted to a
>>>>> SQL table, and the two text field should be cleared and ready for the
>>>>> next scan.
>>>>>
>>>>> Currently, I have a simple form, but you have to manually type the
>>>>> ScanDate. Also, the fields don't clear after the update is
>>>>> successful.
>>>>>
>>>>> Ideally, there would only be one textbox available to the user, and
>>>>> when they scan the TraceCode, the ScanDate is automatically populated,
>>>>> the data is transacted to the SQL table, and the fields clear, ready
>>>>> for the next scan.
>>>>>
>>>>> Here is the only code I have, and it is for the "GO" button":
>>>>>
>>>>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
>>>>> System.EventArgs) Handles Button1.Click
>>>>>        Dim objConnection As New SqlClient.SqlConnection _
>>>>>            ("server=.;database=SafetyStock;trusted_connection=true")
>>>>>        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>>>>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>>>>> String
>>>>>
>>>>>        Try
>>>>>
>>>>>            objConnection.Open()
>>>>>            objTransaction = objConnection.BeginTransaction
>>>>>
>>>>>            objCommand.Transaction = objTransaction
>>>>>
>>>>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>>>>> TraceCode) " & _
>>>>>                "VALUES('" & txtDatetime.Text & "', '" &
>>>>> txtTraceCode.Text & "')"
>>>>>            objCommand.CommandText = strSQL
>>>>>            objCommand.ExecuteNonQuery()
>>>>>
>>>>>            objTransaction.Commit()
>>>>>            objConnection.Close()
>>>>>
>>>>>        Catch
>>>>>            Button1.Text = "Failed"
>>>>>            objTransaction.Rollback()
>>>>>            objConnection.Close()
>>>>>
>>>>>        End Try
>>>>>    End Sub
>>>>> End Class
>>>>>
>>>>> Please HELP! :)
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

9 Aug 2006 11:04 PM

Scott M.

> PS... never mentioned time based PK for you to agree with ... I do not
> like these and avoid them.

Well, you are in the minority on that one. Having a PK that is, in part,
time stamped is the best way to avoid data concurrency issues when doing
updates, inserts and deletes.

> PPS. you suggested the client get the time stamp ... txtDate.Text =
> Now.ToShortDateString

I did suggest this code, but this is server-side code (not client-side
JavaScript, for example) and thus, will get the time from the web server,
not the client. VB.NET code (or any .NET code for that matter) does not
function on the client.

Show quoteHide quote

>
>
> "Scott M." <s-mar@nospam.nospam> wrote in message
> news:%23fifBb$uGHA.1296@TK2MSFTNGP02.phx.gbl...
>> Yes, but again, I don't think anyone was suggesting the client in the
>> first place.
>>
>> In my opinion, the database is not the place to put a time stamp of this
>> kind. While I agree that good primary keys are partially time based,
>> this is not what the OP has stated the time would be used for. The time
>> is going to become data that is used by the business layer of the
>> applicaiton. Databases should not be in charge of creating business data
>> in this manner.
>>
>> "jeff" <jhersey at allnorth dottt com> wrote in message
>> news:OvnwS49uGHA.324@TK2MSFTNGP06.phx.gbl...
>>>
>>> ...exactly... my point.
>>>
>>> what if you have multiple clients in different time zones ... multiple
>>> web servers in different time zones ... and multiple databases servers
>>> in different time zones... with a user from one time zone, connecting to
>>> a web server in another and a database in a third ... and the next time
>>> the user log-ins ... same CLIENT machine ... he hits a different web
>>> server and different database for his next scan transaction ... you need
>>> some type of 'control' in the date stamping ... and to me the logical
>>> one is the Database Server.
>>>
>>> you need to set a base line for time stamping ... and to me, relying on
>>> the CLIENT for this, is not good practice...as per you suggestion...
>>>
>>> Sub Button1_Click() Handles Button1.Click
>>>    txtDate.Text = Now.ToShortDateString
>>> End Sub
>>>
>>> I simply made a suggestion to use the database as the control date /
>>> time 'stamper' (for lack of a better term). If you have different
>>> databases in different time zones, when you synchronize your data ... if
>>> you are not identifying which server the record was initially created
>>> on, you offset you timestamps accordingly during the sync process ... or
>>> when you build a consolidated report to include data from other database
>>> servers in different timezones, you offset your timestamps based on the
>>> database server's 'timezone' ...
>>>
>>> relying on the CLIENT is poor practice and has a hard time standing up
>>> to an audit ... unless you have a mechanism / policy in place that
>>> controls the time on a CLIENT machines.
>>>
>>> Just my 2 cents ... just offering a suggestion thats all.
>>>
>>> Jeff
>>>
>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>> news:eHymO18uGHA.1224@TK2MSFTNGP03.phx.gbl...
>>>> Hold on Jeff. What if the client, web server and database are in
>>>> diffeent time zones? I don't think anyone suggested using the client
>>>> for the date, but I think the server should generate that, not the
>>>> database.
>>>>
>>>>
>>>>
>>>>
>>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>>> news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>>>>>
>>>>> Comment...
>>>>>
>>>>> Getting the Date / Time from the 'client' machine can lead to 'false'
>>>>> date / time stamping...ie the user has messed with the system clock
>>>>> and so on ... If this time will be used for control / report purposes,
>>>>> I would recommend either ...
>>>>>
>>>>> a. Have the database fill the datetime stamp with a default obtained
>>>>> from the server ...
>>>>>
>>>>> b. Create a function to get the current date / time from the server or
>>>>> a common source.
>>>>>
>>>>> Question - What do you considered the 'Time Stamp' for the scan ...
>>>>> when item is scanned or when the record is saved ... if scanned, you
>>>>> will need to get the date from a common source ... if saved, set the
>>>>> default value in the data table to get the current date time on the
>>>>> server and only issue an insert with the text field... and let the
>>>>> server fill the date/time stamp.
>>>>>
>>>>> Jeff.
>>>>>
>>>>> PS:
>>>>>
>>>>> b: - Get a data from a common place...
>>>>>
>>>>>    Assuming MSSQL Server Database...
>>>>>    SELECT GetDate();
>>>>>    using a ExecuteScaler on an OLEDBConnection ...
>>>>>
>>>>>    Using MSAccess...
>>>>>    SELECT Now();
>>>>>    using a ExecuteScaler on an OLEDBConnection.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> <Timothy.Ry***@gmail.com> wrote in message
>>>>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>>>>>> Hello all,
>>>>>>
>>>>>> This is my first attempt at an application, so kid gloves are
>>>>>> appreciated.
>>>>>>
>>>>>> I need to make a very simple form that only has a few elements. One
>>>>>> is
>>>>>> TraceCode - a text field that is populated when a user scans a label.
>>>>>> The other is ScanDate - a date/time field that should equal the
>>>>>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>>>>>
>>>>>> When a button is clicked, or Enter is pressed on the keyboard (or as
>>>>>> the last character of the scan), the data should be transmitted to a
>>>>>> SQL table, and the two text field should be cleared and ready for the
>>>>>> next scan.
>>>>>>
>>>>>> Currently, I have a simple form, but you have to manually type the
>>>>>> ScanDate. Also, the fields don't clear after the update is
>>>>>> successful.
>>>>>>
>>>>>> Ideally, there would only be one textbox available to the user, and
>>>>>> when they scan the TraceCode, the ScanDate is automatically
>>>>>> populated,
>>>>>> the data is transacted to the SQL table, and the fields clear, ready
>>>>>> for the next scan.
>>>>>>
>>>>>> Here is the only code I have, and it is for the "GO" button":
>>>>>>
>>>>>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
>>>>>> As
>>>>>> System.EventArgs) Handles Button1.Click
>>>>>>        Dim objConnection As New SqlClient.SqlConnection _
>>>>>>            ("server=.;database=SafetyStock;trusted_connection=true")
>>>>>>        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>>>>>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>>>>>> String
>>>>>>
>>>>>>        Try
>>>>>>
>>>>>>            objConnection.Open()
>>>>>>            objTransaction = objConnection.BeginTransaction
>>>>>>
>>>>>>            objCommand.Transaction = objTransaction
>>>>>>
>>>>>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>>>>>> TraceCode) " & _
>>>>>>                "VALUES('" & txtDatetime.Text & "', '" &
>>>>>> txtTraceCode.Text & "')"
>>>>>>            objCommand.CommandText = strSQL
>>>>>>            objCommand.ExecuteNonQuery()
>>>>>>
>>>>>>            objTransaction.Commit()
>>>>>>            objConnection.Close()
>>>>>>
>>>>>>        Catch
>>>>>>            Button1.Text = "Failed"
>>>>>>            objTransaction.Rollback()
>>>>>>            objConnection.Close()
>>>>>>
>>>>>>        End Try
>>>>>>    End Sub
>>>>>> End Class
>>>>>>
>>>>>> Please HELP! :)
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

9 Aug 2006 11:09 PM

Scott M.

I did just realize though, that you and I have been assuming 2 different
fundmental things...

I have been assuming this is an ASP.NET web application, hence my last
comment:

> I did suggest this code, but this is server-side code (not client-side
> JavaScript, for example) and thus, will get the time from the web server,
> not the client. VB.NET code (or any .NET code for that matter) does not
> function on the client.

I think you have been commenting from the point of a WinForms application.
If that is the case (the OP didn't specify), then you are correct, the code
would execute on the client.

Show quoteHide quote

"Scott M." <s-mar@nospam.nospam> wrote in message
news:ebiX1eAvGHA.724@TK2MSFTNGP05.phx.gbl...
>> PS... never mentioned time based PK for you to agree with ... I do not
>> like these and avoid them.
>
> Well, you are in the minority on that one. Having a PK that is, in part,
> time stamped is the best way to avoid data concurrency issues when doing
> updates, inserts and deletes.
>
>> PPS. you suggested the client get the time stamp ... txtDate.Text =
>> Now.ToShortDateString
>
> I did suggest this code, but this is server-side code (not client-side
> JavaScript, for example) and thus, will get the time from the web server,
> not the client. VB.NET code (or any .NET code for that matter) does not
> function on the client.
>
>
>>
>>
>> "Scott M." <s-mar@nospam.nospam> wrote in message
>> news:%23fifBb$uGHA.1296@TK2MSFTNGP02.phx.gbl...
>>> Yes, but again, I don't think anyone was suggesting the client in the
>>> first place.
>>>
>>> In my opinion, the database is not the place to put a time stamp of this
>>> kind. While I agree that good primary keys are partially time based,
>>> this is not what the OP has stated the time would be used for. The time
>>> is going to become data that is used by the business layer of the
>>> applicaiton. Databases should not be in charge of creating business data
>>> in this manner.
>>>
>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>> news:OvnwS49uGHA.324@TK2MSFTNGP06.phx.gbl...
>>>>
>>>> ...exactly... my point.
>>>>
>>>> what if you have multiple clients in different time zones ... multiple
>>>> web servers in different time zones ... and multiple databases servers
>>>> in different time zones... with a user from one time zone, connecting
>>>> to a web server in another and a database in a third ... and the next
>>>> time the user log-ins ... same CLIENT machine ... he hits a different
>>>> web server and different database for his next scan transaction ... you
>>>> need some type of 'control' in the date stamping ... and to me the
>>>> logical one is the Database Server.
>>>>
>>>> you need to set a base line for time stamping ... and to me, relying on
>>>> the CLIENT for this, is not good practice...as per you suggestion...
>>>>
>>>> Sub Button1_Click() Handles Button1.Click
>>>>    txtDate.Text = Now.ToShortDateString
>>>> End Sub
>>>>
>>>> I simply made a suggestion to use the database as the control date /
>>>> time 'stamper' (for lack of a better term). If you have different
>>>> databases in different time zones, when you synchronize your data ...
>>>> if you are not identifying which server the record was initially
>>>> created on, you offset you timestamps accordingly during the sync
>>>> process ... or when you build a consolidated report to include data
>>>> from other database servers in different timezones, you offset your
>>>> timestamps based on the database server's 'timezone' ...
>>>>
>>>> relying on the CLIENT is poor practice and has a hard time standing up
>>>> to an audit ... unless you have a mechanism / policy in place that
>>>> controls the time on a CLIENT machines.
>>>>
>>>> Just my 2 cents ... just offering a suggestion thats all.
>>>>
>>>> Jeff
>>>>
>>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>>> news:eHymO18uGHA.1224@TK2MSFTNGP03.phx.gbl...
>>>>> Hold on Jeff. What if the client, web server and database are in
>>>>> diffeent time zones? I don't think anyone suggested using the client
>>>>> for the date, but I think the server should generate that, not the
>>>>> database.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>>>> news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>>>>>>
>>>>>> Comment...
>>>>>>
>>>>>> Getting the Date / Time from the 'client' machine can lead to 'false'
>>>>>> date / time stamping...ie the user has messed with the system clock
>>>>>> and so on ... If this time will be used for control / report
>>>>>> purposes, I would recommend either ...
>>>>>>
>>>>>> a. Have the database fill the datetime stamp with a default obtained
>>>>>> from the server ...
>>>>>>
>>>>>> b. Create a function to get the current date / time from the server
>>>>>> or a common source.
>>>>>>
>>>>>> Question - What do you considered the 'Time Stamp' for the scan ...
>>>>>> when item is scanned or when the record is saved ... if scanned, you
>>>>>> will need to get the date from a common source ... if saved, set the
>>>>>> default value in the data table to get the current date time on the
>>>>>> server and only issue an insert with the text field... and let the
>>>>>> server fill the date/time stamp.
>>>>>>
>>>>>> Jeff.
>>>>>>
>>>>>> PS:
>>>>>>
>>>>>> b: - Get a data from a common place...
>>>>>>
>>>>>>    Assuming MSSQL Server Database...
>>>>>>    SELECT GetDate();
>>>>>>    using a ExecuteScaler on an OLEDBConnection ...
>>>>>>
>>>>>>    Using MSAccess...
>>>>>>    SELECT Now();
>>>>>>    using a ExecuteScaler on an OLEDBConnection.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> <Timothy.Ry***@gmail.com> wrote in message
>>>>>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>>>>>>> Hello all,
>>>>>>>
>>>>>>> This is my first attempt at an application, so kid gloves are
>>>>>>> appreciated.
>>>>>>>
>>>>>>> I need to make a very simple form that only has a few elements. One
>>>>>>> is
>>>>>>> TraceCode - a text field that is populated when a user scans a
>>>>>>> label.
>>>>>>> The other is ScanDate - a date/time field that should equal the
>>>>>>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>>>>>>
>>>>>>> When a button is clicked, or Enter is pressed on the keyboard (or as
>>>>>>> the last character of the scan), the data should be transmitted to a
>>>>>>> SQL table, and the two text field should be cleared and ready for
>>>>>>> the
>>>>>>> next scan.
>>>>>>>
>>>>>>> Currently, I have a simple form, but you have to manually type the
>>>>>>> ScanDate. Also, the fields don't clear after the update is
>>>>>>> successful.
>>>>>>>
>>>>>>> Ideally, there would only be one textbox available to the user, and
>>>>>>> when they scan the TraceCode, the ScanDate is automatically
>>>>>>> populated,
>>>>>>> the data is transacted to the SQL table, and the fields clear, ready
>>>>>>> for the next scan.
>>>>>>>
>>>>>>> Here is the only code I have, and it is for the "GO" button":
>>>>>>>
>>>>>>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
>>>>>>> As
>>>>>>> System.EventArgs) Handles Button1.Click
>>>>>>>        Dim objConnection As New SqlClient.SqlConnection _
>>>>>>>            ("server=.;database=SafetyStock;trusted_connection=true")
>>>>>>>        Dim objCommand As New SqlClient.SqlCommand("", objconnection)
>>>>>>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>>>>>>> String
>>>>>>>
>>>>>>>        Try
>>>>>>>
>>>>>>>            objConnection.Open()
>>>>>>>            objTransaction = objConnection.BeginTransaction
>>>>>>>
>>>>>>>            objCommand.Transaction = objTransaction
>>>>>>>
>>>>>>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>>>>>>> TraceCode) " & _
>>>>>>>                "VALUES('" & txtDatetime.Text & "', '" &
>>>>>>> txtTraceCode.Text & "')"
>>>>>>>            objCommand.CommandText = strSQL
>>>>>>>            objCommand.ExecuteNonQuery()
>>>>>>>
>>>>>>>            objTransaction.Commit()
>>>>>>>            objConnection.Close()
>>>>>>>
>>>>>>>        Catch
>>>>>>>            Button1.Text = "Failed"
>>>>>>>            objTransaction.Rollback()
>>>>>>>            objConnection.Close()
>>>>>>>
>>>>>>>        End Try
>>>>>>>    End Sub
>>>>>>> End Class
>>>>>>>
>>>>>>> Please HELP! :)
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

9 Aug 2006 11:47 PM

jeff

Scott.

agree...I had assumed a winform application...since the OP stated using
Scanning hardware .... I assumed a desktop / winform client application ...
and that is why I had given a SELECT script for obtaining a 'date time'
stamp from the database machine - control machine ... and for this example,
the database machine was the easiest ... unless there is an easier way to
obtain a date time value from another machine - that the client machine
'knows' about ?

agree...if a webform / asp.net application ... script executes on server ...
problem solved - web server is control system. would not want to pull
unnecessary data to the client ...

Jeff.

PS: I do not lilke using the time-stamp approach with PK's for the simple
reason that all database venders handle dates and times slightly different -
and when building a database independent UI / Client - Server application, I
find it much easier to code a framework without them ... if this puts me in
the minority, I will need to do a bit more research to convince me
otherwise.

Show quoteHide quote

"Scott M." <s-mar@nospam.nospam> wrote in message
news:%23b2S7hAvGHA.3428@TK2MSFTNGP02.phx.gbl...
>I did just realize though, that you and I have been assuming 2 different
>fundmental things...
>
> I have been assuming this is an ASP.NET web application, hence my last
> comment:
>
>> I did suggest this code, but this is server-side code (not client-side
>> JavaScript, for example) and thus, will get the time from the web server,
>> not the client. VB.NET code (or any .NET code for that matter) does not
>> function on the client.
>
> I think you have been commenting from the point of a WinForms application.
> If that is the case (the OP didn't specify), then you are correct, the
> code would execute on the client.
>
>
> "Scott M." <s-mar@nospam.nospam> wrote in message
> news:ebiX1eAvGHA.724@TK2MSFTNGP05.phx.gbl...
>>> PS... never mentioned time based PK for you to agree with ... I do not
>>> like these and avoid them.
>>
>> Well, you are in the minority on that one. Having a PK that is, in part,
>> time stamped is the best way to avoid data concurrency issues when doing
>> updates, inserts and deletes.
>>
>>> PPS. you suggested the client get the time stamp ... txtDate.Text =
>>> Now.ToShortDateString
>>
>> I did suggest this code, but this is server-side code (not client-side
>> JavaScript, for example) and thus, will get the time from the web server,
>> not the client. VB.NET code (or any .NET code for that matter) does not
>> function on the client.
>>
>>
>>>
>>>
>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>> news:%23fifBb$uGHA.1296@TK2MSFTNGP02.phx.gbl...
>>>> Yes, but again, I don't think anyone was suggesting the client in the
>>>> first place.
>>>>
>>>> In my opinion, the database is not the place to put a time stamp of
>>>> this kind. While I agree that good primary keys are partially time
>>>> based, this is not what the OP has stated the time would be used for.
>>>> The time is going to become data that is used by the business layer of
>>>> the applicaiton. Databases should not be in charge of creating business
>>>> data in this manner.
>>>>
>>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>>> news:OvnwS49uGHA.324@TK2MSFTNGP06.phx.gbl...
>>>>>
>>>>> ...exactly... my point.
>>>>>
>>>>> what if you have multiple clients in different time zones ... multiple
>>>>> web servers in different time zones ... and multiple databases servers
>>>>> in different time zones... with a user from one time zone, connecting
>>>>> to a web server in another and a database in a third ... and the next
>>>>> time the user log-ins ... same CLIENT machine ... he hits a different
>>>>> web server and different database for his next scan transaction ...
>>>>> you need some type of 'control' in the date stamping ... and to me the
>>>>> logical one is the Database Server.
>>>>>
>>>>> you need to set a base line for time stamping ... and to me, relying
>>>>> on the CLIENT for this, is not good practice...as per you
>>>>> suggestion...
>>>>>
>>>>> Sub Button1_Click() Handles Button1.Click
>>>>>    txtDate.Text = Now.ToShortDateString
>>>>> End Sub
>>>>>
>>>>> I simply made a suggestion to use the database as the control date /
>>>>> time 'stamper' (for lack of a better term). If you have different
>>>>> databases in different time zones, when you synchronize your data ...
>>>>> if you are not identifying which server the record was initially
>>>>> created on, you offset you timestamps accordingly during the sync
>>>>> process ... or when you build a consolidated report to include data
>>>>> from other database servers in different timezones, you offset your
>>>>> timestamps based on the database server's 'timezone' ...
>>>>>
>>>>> relying on the CLIENT is poor practice and has a hard time standing up
>>>>> to an audit ... unless you have a mechanism / policy in place that
>>>>> controls the time on a CLIENT machines.
>>>>>
>>>>> Just my 2 cents ... just offering a suggestion thats all.
>>>>>
>>>>> Jeff
>>>>>
>>>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>>>> news:eHymO18uGHA.1224@TK2MSFTNGP03.phx.gbl...
>>>>>> Hold on Jeff. What if the client, web server and database are in
>>>>>> diffeent time zones? I don't think anyone suggested using the client
>>>>>> for the date, but I think the server should generate that, not the
>>>>>> database.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>>>>> news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>>>>>>>
>>>>>>> Comment...
>>>>>>>
>>>>>>> Getting the Date / Time from the 'client' machine can lead to
>>>>>>> 'false' date / time stamping...ie the user has messed with the
>>>>>>> system clock and so on ... If this time will be used for control /
>>>>>>> report purposes, I would recommend either ...
>>>>>>>
>>>>>>> a. Have the database fill the datetime stamp with a default obtained
>>>>>>> from the server ...
>>>>>>>
>>>>>>> b. Create a function to get the current date / time from the server
>>>>>>> or a common source.
>>>>>>>
>>>>>>> Question - What do you considered the 'Time Stamp' for the scan ...
>>>>>>> when item is scanned or when the record is saved ... if scanned, you
>>>>>>> will need to get the date from a common source ... if saved, set the
>>>>>>> default value in the data table to get the current date time on the
>>>>>>> server and only issue an insert with the text field... and let the
>>>>>>> server fill the date/time stamp.
>>>>>>>
>>>>>>> Jeff.
>>>>>>>
>>>>>>> PS:
>>>>>>>
>>>>>>> b: - Get a data from a common place...
>>>>>>>
>>>>>>>    Assuming MSSQL Server Database...
>>>>>>>    SELECT GetDate();
>>>>>>>    using a ExecuteScaler on an OLEDBConnection ...
>>>>>>>
>>>>>>>    Using MSAccess...
>>>>>>>    SELECT Now();
>>>>>>>    using a ExecuteScaler on an OLEDBConnection.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> <Timothy.Ry***@gmail.com> wrote in message
>>>>>>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>>>>>>>> Hello all,
>>>>>>>>
>>>>>>>> This is my first attempt at an application, so kid gloves are
>>>>>>>> appreciated.
>>>>>>>>
>>>>>>>> I need to make a very simple form that only has a few elements.
>>>>>>>> One is
>>>>>>>> TraceCode - a text field that is populated when a user scans a
>>>>>>>> label.
>>>>>>>> The other is ScanDate - a date/time field that should equal the
>>>>>>>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>>>>>>>
>>>>>>>> When a button is clicked, or Enter is pressed on the keyboard (or
>>>>>>>> as
>>>>>>>> the last character of the scan), the data should be transmitted to
>>>>>>>> a
>>>>>>>> SQL table, and the two text field should be cleared and ready for
>>>>>>>> the
>>>>>>>> next scan.
>>>>>>>>
>>>>>>>> Currently, I have a simple form, but you have to manually type the
>>>>>>>> ScanDate. Also, the fields don't clear after the update is
>>>>>>>> successful.
>>>>>>>>
>>>>>>>> Ideally, there would only be one textbox available to the user, and
>>>>>>>> when they scan the TraceCode, the ScanDate is automatically
>>>>>>>> populated,
>>>>>>>> the data is transacted to the SQL table, and the fields clear,
>>>>>>>> ready
>>>>>>>> for the next scan.
>>>>>>>>
>>>>>>>> Here is the only code I have, and it is for the "GO" button":
>>>>>>>>
>>>>>>>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
>>>>>>>> As
>>>>>>>> System.EventArgs) Handles Button1.Click
>>>>>>>>        Dim objConnection As New SqlClient.SqlConnection _
>>>>>>>>
>>>>>>>> ("server=.;database=SafetyStock;trusted_connection=true")
>>>>>>>>        Dim objCommand As New SqlClient.SqlCommand("",
>>>>>>>> objconnection)
>>>>>>>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>>>>>>>> String
>>>>>>>>
>>>>>>>>        Try
>>>>>>>>
>>>>>>>>            objConnection.Open()
>>>>>>>>            objTransaction = objConnection.BeginTransaction
>>>>>>>>
>>>>>>>>            objCommand.Transaction = objTransaction
>>>>>>>>
>>>>>>>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>>>>>>>> TraceCode) " & _
>>>>>>>>                "VALUES('" & txtDatetime.Text & "', '" &
>>>>>>>> txtTraceCode.Text & "')"
>>>>>>>>            objCommand.CommandText = strSQL
>>>>>>>>            objCommand.ExecuteNonQuery()
>>>>>>>>
>>>>>>>>            objTransaction.Commit()
>>>>>>>>            objConnection.Close()
>>>>>>>>
>>>>>>>>        Catch
>>>>>>>>            Button1.Text = "Failed"
>>>>>>>>            objTransaction.Rollback()
>>>>>>>>            objConnection.Close()
>>>>>>>>
>>>>>>>>        End Try
>>>>>>>>    End Sub
>>>>>>>> End Class
>>>>>>>>
>>>>>>>> Please HELP! :)
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

11 Aug 2006 12:12 PM

jeff

Scottt...

I have done a small poll ... and people I have asked and responded, have
never used a timestamp / datetime field as part of their PK set ... and the
one fella that did (or at least was brought in to a project after design),
had to go back and re-write them, cause he was getting too many
transactions, and the app would flequently encounter collisions due to the
timestamp / composite key approach.

So, I will remain in the 'minority' (quite a large on from where I sit) and
keep 'not' using timestamps / datatime fields in my PK's .... surrogate key
approach works just fine for me.

Thanks.
Jeff.

Show quoteHide quote

"jeff" <jhersey at allnorth dottt com> wrote in message
news:eijOk4AvGHA.4752@TK2MSFTNGP02.phx.gbl...
>
> Scott.
>
> agree...I had assumed a winform application...since the OP stated using
> Scanning hardware .... I assumed a desktop / winform client application
> ... and that is why I had given a SELECT script for obtaining a 'date
> time' stamp from the database machine - control machine ... and for this
> example, the database machine was the easiest ... unless there is an
> easier way to obtain a date time value from another machine - that the
> client machine 'knows' about ?
>
> agree...if a webform / asp.net application ... script executes on server
> ... problem solved - web server is control system. would not want to pull
> unnecessary data to the client ...
>
> Jeff.
>
> PS: I do not lilke using the time-stamp approach with PK's for the simple
> reason that all database venders handle dates and times slightly
> different - and when building a database independent UI / Client - Server
> application, I find it much easier to code a framework without them ... if
> this puts me in the minority, I will need to do a bit more research to
> convince me otherwise.
>
>
>
> "Scott M." <s-mar@nospam.nospam> wrote in message
> news:%23b2S7hAvGHA.3428@TK2MSFTNGP02.phx.gbl...
>>I did just realize though, that you and I have been assuming 2 different
>>fundmental things...
>>
>> I have been assuming this is an ASP.NET web application, hence my last
>> comment:
>>
>>> I did suggest this code, but this is server-side code (not client-side
>>> JavaScript, for example) and thus, will get the time from the web
>>> server, not the client. VB.NET code (or any .NET code for that matter)
>>> does not function on the client.
>>
>> I think you have been commenting from the point of a WinForms
>> application. If that is the case (the OP didn't specify), then you are
>> correct, the code would execute on the client.
>>
>>
>> "Scott M." <s-mar@nospam.nospam> wrote in message
>> news:ebiX1eAvGHA.724@TK2MSFTNGP05.phx.gbl...
>>>> PS... never mentioned time based PK for you to agree with ... I do not
>>>> like these and avoid them.
>>>
>>> Well, you are in the minority on that one. Having a PK that is, in part,
>>> time stamped is the best way to avoid data concurrency issues when
>>> doing updates, inserts and deletes.
>>>
>>>> PPS. you suggested the client get the time stamp ... txtDate.Text =
>>>> Now.ToShortDateString
>>>
>>> I did suggest this code, but this is server-side code (not client-side
>>> JavaScript, for example) and thus, will get the time from the web
>>> server, not the client. VB.NET code (or any .NET code for that matter)
>>> does not function on the client.
>>>
>>>
>>>>
>>>>
>>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>>> news:%23fifBb$uGHA.1296@TK2MSFTNGP02.phx.gbl...
>>>>> Yes, but again, I don't think anyone was suggesting the client in the
>>>>> first place.
>>>>>
>>>>> In my opinion, the database is not the place to put a time stamp of
>>>>> this kind. While I agree that good primary keys are partially time
>>>>> based, this is not what the OP has stated the time would be used for.
>>>>> The time is going to become data that is used by the business layer of
>>>>> the applicaiton. Databases should not be in charge of creating
>>>>> business data in this manner.
>>>>>
>>>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>>>> news:OvnwS49uGHA.324@TK2MSFTNGP06.phx.gbl...
>>>>>>
>>>>>> ...exactly... my point.
>>>>>>
>>>>>> what if you have multiple clients in different time zones ...
>>>>>> multiple web servers in different time zones ... and multiple
>>>>>> databases servers in different time zones... with a user from one
>>>>>> time zone, connecting to a web server in another and a database in a
>>>>>> third ... and the next time the user log-ins ... same CLIENT machine
>>>>>> ... he hits a different web server and different database for his
>>>>>> next scan transaction ... you need some type of 'control' in the date
>>>>>> stamping ... and to me the logical one is the Database Server.
>>>>>>
>>>>>> you need to set a base line for time stamping ... and to me, relying
>>>>>> on the CLIENT for this, is not good practice...as per you
>>>>>> suggestion...
>>>>>>
>>>>>> Sub Button1_Click() Handles Button1.Click
>>>>>>    txtDate.Text = Now.ToShortDateString
>>>>>> End Sub
>>>>>>
>>>>>> I simply made a suggestion to use the database as the control date /
>>>>>> time 'stamper' (for lack of a better term). If you have different
>>>>>> databases in different time zones, when you synchronize your data ...
>>>>>> if you are not identifying which server the record was initially
>>>>>> created on, you offset you timestamps accordingly during the sync
>>>>>> process ... or when you build a consolidated report to include data
>>>>>> from other database servers in different timezones, you offset your
>>>>>> timestamps based on the database server's 'timezone' ...
>>>>>>
>>>>>> relying on the CLIENT is poor practice and has a hard time standing
>>>>>> up to an audit ... unless you have a mechanism / policy in place that
>>>>>> controls the time on a CLIENT machines.
>>>>>>
>>>>>> Just my 2 cents ... just offering a suggestion thats all.
>>>>>>
>>>>>> Jeff
>>>>>>
>>>>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>>>>> news:eHymO18uGHA.1224@TK2MSFTNGP03.phx.gbl...
>>>>>>> Hold on Jeff. What if the client, web server and database are in
>>>>>>> diffeent time zones? I don't think anyone suggested using the
>>>>>>> client for the date, but I think the server should generate that,
>>>>>>> not the database.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>>>>>> news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>>>>>>>>
>>>>>>>> Comment...
>>>>>>>>
>>>>>>>> Getting the Date / Time from the 'client' machine can lead to
>>>>>>>> 'false' date / time stamping...ie the user has messed with the
>>>>>>>> system clock and so on ... If this time will be used for control /
>>>>>>>> report purposes, I would recommend either ...
>>>>>>>>
>>>>>>>> a. Have the database fill the datetime stamp with a default
>>>>>>>> obtained from the server ...
>>>>>>>>
>>>>>>>> b. Create a function to get the current date / time from the server
>>>>>>>> or a common source.
>>>>>>>>
>>>>>>>> Question - What do you considered the 'Time Stamp' for the scan ...
>>>>>>>> when item is scanned or when the record is saved ... if scanned,
>>>>>>>> you will need to get the date from a common source ... if saved,
>>>>>>>> set the default value in the data table to get the current date
>>>>>>>> time on the server and only issue an insert with the text field...
>>>>>>>> and let the server fill the date/time stamp.
>>>>>>>>
>>>>>>>> Jeff.
>>>>>>>>
>>>>>>>> PS:
>>>>>>>>
>>>>>>>> b: - Get a data from a common place...
>>>>>>>>
>>>>>>>>    Assuming MSSQL Server Database...
>>>>>>>>    SELECT GetDate();
>>>>>>>>    using a ExecuteScaler on an OLEDBConnection ...
>>>>>>>>
>>>>>>>>    Using MSAccess...
>>>>>>>>    SELECT Now();
>>>>>>>>    using a ExecuteScaler on an OLEDBConnection.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> <Timothy.Ry***@gmail.com> wrote in message
>>>>>>>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>>>>>>>>> Hello all,
>>>>>>>>>
>>>>>>>>> This is my first attempt at an application, so kid gloves are
>>>>>>>>> appreciated.
>>>>>>>>>
>>>>>>>>> I need to make a very simple form that only has a few elements.
>>>>>>>>> One is
>>>>>>>>> TraceCode - a text field that is populated when a user scans a
>>>>>>>>> label.
>>>>>>>>> The other is ScanDate - a date/time field that should equal the
>>>>>>>>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>>>>>>>>
>>>>>>>>> When a button is clicked, or Enter is pressed on the keyboard (or
>>>>>>>>> as
>>>>>>>>> the last character of the scan), the data should be transmitted to
>>>>>>>>> a
>>>>>>>>> SQL table, and the two text field should be cleared and ready for
>>>>>>>>> the
>>>>>>>>> next scan.
>>>>>>>>>
>>>>>>>>> Currently, I have a simple form, but you have to manually type the
>>>>>>>>> ScanDate. Also, the fields don't clear after the update is
>>>>>>>>> successful.
>>>>>>>>>
>>>>>>>>> Ideally, there would only be one textbox available to the user,
>>>>>>>>> and
>>>>>>>>> when they scan the TraceCode, the ScanDate is automatically
>>>>>>>>> populated,
>>>>>>>>> the data is transacted to the SQL table, and the fields clear,
>>>>>>>>> ready
>>>>>>>>> for the next scan.
>>>>>>>>>
>>>>>>>>> Here is the only code I have, and it is for the "GO" button":
>>>>>>>>>
>>>>>>>>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal
>>>>>>>>> e As
>>>>>>>>> System.EventArgs) Handles Button1.Click
>>>>>>>>>        Dim objConnection As New SqlClient.SqlConnection _
>>>>>>>>>
>>>>>>>>> ("server=.;database=SafetyStock;trusted_connection=true")
>>>>>>>>>        Dim objCommand As New SqlClient.SqlCommand("",
>>>>>>>>> objconnection)
>>>>>>>>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>>>>>>>>> String
>>>>>>>>>
>>>>>>>>>        Try
>>>>>>>>>
>>>>>>>>>            objConnection.Open()
>>>>>>>>>            objTransaction = objConnection.BeginTransaction
>>>>>>>>>
>>>>>>>>>            objCommand.Transaction = objTransaction
>>>>>>>>>
>>>>>>>>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>>>>>>>>> TraceCode) " & _
>>>>>>>>>                "VALUES('" & txtDatetime.Text & "', '" &
>>>>>>>>> txtTraceCode.Text & "')"
>>>>>>>>>            objCommand.CommandText = strSQL
>>>>>>>>>            objCommand.ExecuteNonQuery()
>>>>>>>>>
>>>>>>>>>            objTransaction.Commit()
>>>>>>>>>            objConnection.Close()
>>>>>>>>>
>>>>>>>>>        Catch
>>>>>>>>>            Button1.Text = "Failed"
>>>>>>>>>            objTransaction.Rollback()
>>>>>>>>>            objConnection.Close()
>>>>>>>>>
>>>>>>>>>        End Try
>>>>>>>>>    End Sub
>>>>>>>>> End Class
>>>>>>>>>
>>>>>>>>> Please HELP! :)
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

11 Aug 2006 1:01 PM

Scott M.

Every app is different and has different requirements, you know that.
Having said that, using a PK that is, in part (not completely), time based
is the best way to avoid concurrency issues. Just about every book and
enterprise application I've read and worked with follow this approach. Does
that make it "law", no. In development there are a number of different ways
to solve any problem. If you have one that works for you, great.

Show quoteHide quote

"jeff" <jhersey at allnorth dottt com> wrote in message
news:eZ05n9TvGHA.1808@TK2MSFTNGP06.phx.gbl...
> Scottt...
>
> I have done a small poll ... and people I have asked and responded, have
> never used a timestamp / datetime field as part of their PK set ... and
> the one fella that did (or at least was brought in to a project after
> design), had to go back and re-write them, cause he was getting too many
> transactions, and the app would flequently encounter collisions due to the
> timestamp / composite key approach.
>
> So, I will remain in the 'minority' (quite a large on from where I sit)
> and keep 'not' using timestamps / datatime fields in my PK's ....
> surrogate key approach works just fine for me.
>
> Thanks.
> Jeff.
>
>
> "jeff" <jhersey at allnorth dottt com> wrote in message
> news:eijOk4AvGHA.4752@TK2MSFTNGP02.phx.gbl...
>>
>> Scott.
>>
>> agree...I had assumed a winform application...since the OP stated using
>> Scanning hardware .... I assumed a desktop / winform client application
>> ... and that is why I had given a SELECT script for obtaining a 'date
>> time' stamp from the database machine - control machine ... and for this
>> example, the database machine was the easiest ... unless there is an
>> easier way to obtain a date time value from another machine - that the
>> client machine 'knows' about ?
>>
>> agree...if a webform / asp.net application ... script executes on server
>> ... problem solved - web server is control system. would not want to
>> pull unnecessary data to the client ...
>>
>> Jeff.
>>
>> PS: I do not lilke using the time-stamp approach with PK's for the simple
>> reason that all database venders handle dates and times slightly
>> different - and when building a database independent UI / Client - Server
>> application, I find it much easier to code a framework without them ...
>> if this puts me in the minority, I will need to do a bit more research to
>> convince me otherwise.
>>
>>
>>
>> "Scott M." <s-mar@nospam.nospam> wrote in message
>> news:%23b2S7hAvGHA.3428@TK2MSFTNGP02.phx.gbl...
>>>I did just realize though, that you and I have been assuming 2 different
>>>fundmental things...
>>>
>>> I have been assuming this is an ASP.NET web application, hence my last
>>> comment:
>>>
>>>> I did suggest this code, but this is server-side code (not client-side
>>>> JavaScript, for example) and thus, will get the time from the web
>>>> server, not the client. VB.NET code (or any .NET code for that matter)
>>>> does not function on the client.
>>>
>>> I think you have been commenting from the point of a WinForms
>>> application. If that is the case (the OP didn't specify), then you are
>>> correct, the code would execute on the client.
>>>
>>>
>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>> news:ebiX1eAvGHA.724@TK2MSFTNGP05.phx.gbl...
>>>>> PS... never mentioned time based PK for you to agree with ... I do not
>>>>> like these and avoid them.
>>>>
>>>> Well, you are in the minority on that one. Having a PK that is, in
>>>> part, time stamped is the best way to avoid data concurrency issues
>>>> when doing updates, inserts and deletes.
>>>>
>>>>> PPS. you suggested the client get the time stamp ... txtDate.Text =
>>>>> Now.ToShortDateString
>>>>
>>>> I did suggest this code, but this is server-side code (not client-side
>>>> JavaScript, for example) and thus, will get the time from the web
>>>> server, not the client. VB.NET code (or any .NET code for that matter)
>>>> does not function on the client.
>>>>
>>>>
>>>>>
>>>>>
>>>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>>>> news:%23fifBb$uGHA.1296@TK2MSFTNGP02.phx.gbl...
>>>>>> Yes, but again, I don't think anyone was suggesting the client in the
>>>>>> first place.
>>>>>>
>>>>>> In my opinion, the database is not the place to put a time stamp of
>>>>>> this kind. While I agree that good primary keys are partially time
>>>>>> based, this is not what the OP has stated the time would be used for.
>>>>>> The time is going to become data that is used by the business layer
>>>>>> of the applicaiton. Databases should not be in charge of creating
>>>>>> business data in this manner.
>>>>>>
>>>>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>>>>> news:OvnwS49uGHA.324@TK2MSFTNGP06.phx.gbl...
>>>>>>>
>>>>>>> ...exactly... my point.
>>>>>>>
>>>>>>> what if you have multiple clients in different time zones ...
>>>>>>> multiple web servers in different time zones ... and multiple
>>>>>>> databases servers in different time zones... with a user from one
>>>>>>> time zone, connecting to a web server in another and a database in a
>>>>>>> third ... and the next time the user log-ins ... same CLIENT machine
>>>>>>> ... he hits a different web server and different database for his
>>>>>>> next scan transaction ... you need some type of 'control' in the
>>>>>>> date stamping ... and to me the logical one is the Database Server.
>>>>>>>
>>>>>>> you need to set a base line for time stamping ... and to me, relying
>>>>>>> on the CLIENT for this, is not good practice...as per you
>>>>>>> suggestion...
>>>>>>>
>>>>>>> Sub Button1_Click() Handles Button1.Click
>>>>>>>    txtDate.Text = Now.ToShortDateString
>>>>>>> End Sub
>>>>>>>
>>>>>>> I simply made a suggestion to use the database as the control date /
>>>>>>> time 'stamper' (for lack of a better term). If you have different
>>>>>>> databases in different time zones, when you synchronize your data
>>>>>>> ... if you are not identifying which server the record was initially
>>>>>>> created on, you offset you timestamps accordingly during the sync
>>>>>>> process ... or when you build a consolidated report to include data
>>>>>>> from other database servers in different timezones, you offset your
>>>>>>> timestamps based on the database server's 'timezone' ...
>>>>>>>
>>>>>>> relying on the CLIENT is poor practice and has a hard time standing
>>>>>>> up to an audit ... unless you have a mechanism / policy in place
>>>>>>> that controls the time on a CLIENT machines.
>>>>>>>
>>>>>>> Just my 2 cents ... just offering a suggestion thats all.
>>>>>>>
>>>>>>> Jeff
>>>>>>>
>>>>>>> "Scott M." <s-mar@nospam.nospam> wrote in message
>>>>>>> news:eHymO18uGHA.1224@TK2MSFTNGP03.phx.gbl...
>>>>>>>> Hold on Jeff. What if the client, web server and database are in
>>>>>>>> diffeent time zones? I don't think anyone suggested using the
>>>>>>>> client for the date, but I think the server should generate that,
>>>>>>>> not the database.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> "jeff" <jhersey at allnorth dottt com> wrote in message
>>>>>>>> news:ezBzQv6uGHA.4972@TK2MSFTNGP05.phx.gbl...
>>>>>>>>>
>>>>>>>>> Comment...
>>>>>>>>>
>>>>>>>>> Getting the Date / Time from the 'client' machine can lead to
>>>>>>>>> 'false' date / time stamping...ie the user has messed with the
>>>>>>>>> system clock and so on ... If this time will be used for control /
>>>>>>>>> report purposes, I would recommend either ...
>>>>>>>>>
>>>>>>>>> a. Have the database fill the datetime stamp with a default
>>>>>>>>> obtained from the server ...
>>>>>>>>>
>>>>>>>>> b. Create a function to get the current date / time from the
>>>>>>>>> server or a common source.
>>>>>>>>>
>>>>>>>>> Question - What do you considered the 'Time Stamp' for the scan
>>>>>>>>> ... when item is scanned or when the record is saved ... if
>>>>>>>>> scanned, you will need to get the date from a common source ... if
>>>>>>>>> saved, set the default value in the data table to get the current
>>>>>>>>> date time on the server and only issue an insert with the text
>>>>>>>>> field... and let the server fill the date/time stamp.
>>>>>>>>>
>>>>>>>>> Jeff.
>>>>>>>>>
>>>>>>>>> PS:
>>>>>>>>>
>>>>>>>>> b: - Get a data from a common place...
>>>>>>>>>
>>>>>>>>>    Assuming MSSQL Server Database...
>>>>>>>>>    SELECT GetDate();
>>>>>>>>>    using a ExecuteScaler on an OLEDBConnection ...
>>>>>>>>>
>>>>>>>>>    Using MSAccess...
>>>>>>>>>    SELECT Now();
>>>>>>>>>    using a ExecuteScaler on an OLEDBConnection.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> <Timothy.Ry***@gmail.com> wrote in message
>>>>>>>>> news:1155061620.089944.269190@p79g2000cwp.googlegroups.com...
>>>>>>>>>> Hello all,
>>>>>>>>>>
>>>>>>>>>> This is my first attempt at an application, so kid gloves are
>>>>>>>>>> appreciated.
>>>>>>>>>>
>>>>>>>>>> I need to make a very simple form that only has a few elements.
>>>>>>>>>> One is
>>>>>>>>>> TraceCode - a text field that is populated when a user scans a
>>>>>>>>>> label.
>>>>>>>>>> The other is ScanDate - a date/time field that should equal the
>>>>>>>>>> date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
>>>>>>>>>>
>>>>>>>>>> When a button is clicked, or Enter is pressed on the keyboard (or
>>>>>>>>>> as
>>>>>>>>>> the last character of the scan), the data should be transmitted
>>>>>>>>>> to a
>>>>>>>>>> SQL table, and the two text field should be cleared and ready for
>>>>>>>>>> the
>>>>>>>>>> next scan.
>>>>>>>>>>
>>>>>>>>>> Currently, I have a simple form, but you have to manually type
>>>>>>>>>> the
>>>>>>>>>> ScanDate. Also, the fields don't clear after the update is
>>>>>>>>>> successful.
>>>>>>>>>>
>>>>>>>>>> Ideally, there would only be one textbox available to the user,
>>>>>>>>>> and
>>>>>>>>>> when they scan the TraceCode, the ScanDate is automatically
>>>>>>>>>> populated,
>>>>>>>>>> the data is transacted to the SQL table, and the fields clear,
>>>>>>>>>> ready
>>>>>>>>>> for the next scan.
>>>>>>>>>>
>>>>>>>>>> Here is the only code I have, and it is for the "GO" button":
>>>>>>>>>>
>>>>>>>>>>    Private Sub Button1_Click(ByVal sender As System.Object, ByVal
>>>>>>>>>> e As
>>>>>>>>>> System.EventArgs) Handles Button1.Click
>>>>>>>>>>        Dim objConnection As New SqlClient.SqlConnection _
>>>>>>>>>>
>>>>>>>>>> ("server=.;database=SafetyStock;trusted_connection=true")
>>>>>>>>>>        Dim objCommand As New SqlClient.SqlCommand("",
>>>>>>>>>> objconnection)
>>>>>>>>>>        Dim objTransaction As SqlClient.SqlTransaction, strSQL As
>>>>>>>>>> String
>>>>>>>>>>
>>>>>>>>>>        Try
>>>>>>>>>>
>>>>>>>>>>            objConnection.Open()
>>>>>>>>>>            objTransaction = objConnection.BeginTransaction
>>>>>>>>>>
>>>>>>>>>>            objCommand.Transaction = objTransaction
>>>>>>>>>>
>>>>>>>>>>            strSQL = "INSERT INTO ActiveSafetyStock(ScanDate,
>>>>>>>>>> TraceCode) " & _
>>>>>>>>>>                "VALUES('" & txtDatetime.Text & "', '" &
>>>>>>>>>> txtTraceCode.Text & "')"
>>>>>>>>>>            objCommand.CommandText = strSQL
>>>>>>>>>>            objCommand.ExecuteNonQuery()
>>>>>>>>>>
>>>>>>>>>>            objTransaction.Commit()
>>>>>>>>>>            objConnection.Close()
>>>>>>>>>>
>>>>>>>>>>        Catch
>>>>>>>>>>            Button1.Text = "Failed"
>>>>>>>>>>            objTransaction.Rollback()
>>>>>>>>>>            objConnection.Close()
>>>>>>>>>>
>>>>>>>>>>        End Try
>>>>>>>>>>    End Sub
>>>>>>>>>> End Class
>>>>>>>>>>
>>>>>>>>>> Please HELP! :)
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>