Insert date 1900-01-01

Discussion in 'ASP .Net' started by Arek, Oct 6, 2004.

  1. Arek

    Arek Guest

    Hey,
    I am inserting values in the table:

    Dim sqlcomm1 As SqlCommand = New SqlCommand("INSERT INTO tblTasks
    (idTask, outdate) VALUES ('" & IDTask.text & "','" & txtOutdate.Text &
    "')", conn)
    sqlcomm1.ExecuteNonQuery()

    Query is working fine if there is value in the txtOutDate, but if user
    leaves the field txtOutdate empty, system insert date 1900-01-01. I want
    system to insert null.

    How can I achieve that?

    Regards
    Arek




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Arek, Oct 6, 2004
    #1
    1. Advertising

  2. in c#
    txtOutdate.Text == string.empty ? null : txtOutdate.Text

    i'm sorry i am not expert in visualbasic.

    but i suppose you can make an if
    if txtOutdate.Text == string.empty then
    str = null
    else
    str = txtOutdate.Text

    I think you should also transform your string in datetime format recognized
    by sql.


    "Arek" wrote:

    > Hey,
    > I am inserting values in the table:
    >
    > Dim sqlcomm1 As SqlCommand = New SqlCommand("INSERT INTO tblTasks
    > (idTask, outdate) VALUES ('" & IDTask.text & "','" & txtOutdate.Text &
    > "')", conn)
    > sqlcomm1.ExecuteNonQuery()
    >
    > Query is working fine if there is value in the txtOutDate, but if user
    > leaves the field txtOutdate empty, system insert date 1900-01-01. I want
    > system to insert null.
    >
    > How can I achieve that?
    >
    > Regards
    > Arek
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    >
     
    =?Utf-8?B?UHN5Y2hv?=, Oct 6, 2004
    #2
    1. Advertising

  3. Hi Arek,

    It isn't clear whether your data is supposed to be a datetime or a quoted
    string. If it is a string and you want to insert a Null if the text is empty,
    you can try testing with the function code below. For a datetime, you'll need
    a different value. You can try hardcoding the values at first until you see
    what type is acceptable.

    Let us know how you make out?

    Ken

    <%@ Page Language="VB" %>
    <script runat="server">

    Sub Button1_Click(sender As Object, e As EventArgs)
    label1.text="INSERT INTO tblTasks (idTask, outdate) VALUES ('" &
    IDTask.text & "'," & CheckNull(txtOutdate.Text) & ")"
    End Sub

    Function CheckNull(strInVal as object) as string
    if isNothing(strInVal) then
    return "Null"
    end if

    if strInval="" then
    return "Null"
    end if

    ' Everything is fine
    return " '" & strInval & "' "
    end function

    </script>
    <html>
    <head>
    </head>
    <body>
    <form runat="server">
    <p>
    </p>
    <p>
    <asp:TextBox id="IDTask" runat="server">MyValue</asp:TextBox>
    </p>
    <p>
    <asp:TextBox id="txtOutdate" runat="server">The value goes
    here</asp:TextBox>
    </p>
    <p>
    <asp:Button id="Button1" onclick="Button1_Click" runat="server"
    Text="Button"></asp:Button>
    </p>
    <p>
    <asp:Label id="Label1" runat="server">Label</asp:Label>
    </p>
    <!-- Insert content here -->
    </form>
    </body>
    </html>


    "Arek" wrote:

    > Hey,
    > I am inserting values in the table:
    >
    > Dim sqlcomm1 As SqlCommand = New SqlCommand("INSERT INTO tblTasks
    > (idTask, outdate) VALUES ('" & IDTask.text & "','" & txtOutdate.Text &
    > "')", conn)
    > sqlcomm1.ExecuteNonQuery()
    >
    > Query is working fine if there is value in the txtOutDate, but if user
    > leaves the field txtOutdate empty, system insert date 1900-01-01. I want
    > system to insert null.
    >
    > How can I achieve that?
    >
    > Regards
    > Arek
    >
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    >
     
    =?Utf-8?B?S2VuIENveCBbTWljcm9zb2Z0IE1WUF0=?=, Oct 6, 2004
    #3
  4. Arek

    Greg Burns Guest

    I am not a not an expert in C#, but shouldn't that be DBNull.Value?

    Regardless, it is a lot cleaner using parameters...

    Dim sqlcomm1 As New SqlCommand("INSERT INTO tblTasks (idTask, outdate)
    VALUES (@ID, @OutDate)", conn)

    sqlcomm1.Parameters.Add("@ID", SqlDbType.Int).Value = CInt(IDTask.text)

    If Not txtOutdate.Text = String.Empty Then
    sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
    CDate(txtOutdate)
    Else
    sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
    DBNull.Value
    End If

    sqlcomm1.ExecuteNonQuery()

    HTH,
    Greg



    "Psycho" <> wrote in message
    news:...
    > in c#
    > txtOutdate.Text == string.empty ? null : txtOutdate.Text
    >
    > i'm sorry i am not expert in visualbasic.
    >
    > but i suppose you can make an if
    > if txtOutdate.Text == string.empty then
    > str = null
    > else
    > str = txtOutdate.Text
    >
    > I think you should also transform your string in datetime format
    > recognized
    > by sql.
    >
    >
    > "Arek" wrote:
    >
    >> Hey,
    >> I am inserting values in the table:
    >>
    >> Dim sqlcomm1 As SqlCommand = New SqlCommand("INSERT INTO tblTasks
    >> (idTask, outdate) VALUES ('" & IDTask.text & "','" & txtOutdate.Text &
    >> "')", conn)
    >> sqlcomm1.ExecuteNonQuery()
    >>
    >> Query is working fine if there is value in the txtOutDate, but if user
    >> leaves the field txtOutdate empty, system insert date 1900-01-01. I want
    >> system to insert null.
    >>
    >> How can I achieve that?
    >>
    >> Regards
    >> Arek
    >>
    >>
    >>
    >>
    >> *** Sent via Developersdex http://www.developersdex.com ***
    >> Don't just participate in USENET...get rewarded for it!
    >>
     
    Greg Burns, Oct 6, 2004
    #4
  5. Arek

    Greg Burns Guest

    should have been...
    > sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
    > CDate(txtOutdate.Text)



    "Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
    news:...
    >I am not a not an expert in C#, but shouldn't that be DBNull.Value?
    >
    > Regardless, it is a lot cleaner using parameters...
    >
    > Dim sqlcomm1 As New SqlCommand("INSERT INTO tblTasks (idTask, outdate)
    > VALUES (@ID, @OutDate)", conn)
    >
    > sqlcomm1.Parameters.Add("@ID", SqlDbType.Int).Value = CInt(IDTask.text)
    >
    > If Not txtOutdate.Text = String.Empty Then
    > sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
    > CDate(txtOutdate)
    > Else
    > sqlcomm1.Parameters.Add("@OutDate", SqlDbType.DateTime).Value =
    > DBNull.Value
    > End If
    >
    > sqlcomm1.ExecuteNonQuery()
    >
    > HTH,
    > Greg
    >
    >
    >
    > "Psycho" <> wrote in message
    > news:...
    >> in c#
    >> txtOutdate.Text == string.empty ? null : txtOutdate.Text
    >>
    >> i'm sorry i am not expert in visualbasic.
    >>
    >> but i suppose you can make an if
    >> if txtOutdate.Text == string.empty then
    >> str = null
    >> else
    >> str = txtOutdate.Text
    >>
    >> I think you should also transform your string in datetime format
    >> recognized
    >> by sql.
    >>
    >>
    >> "Arek" wrote:
    >>
    >>> Hey,
    >>> I am inserting values in the table:
    >>>
    >>> Dim sqlcomm1 As SqlCommand = New SqlCommand("INSERT INTO tblTasks
    >>> (idTask, outdate) VALUES ('" & IDTask.text & "','" & txtOutdate.Text &
    >>> "')", conn)
    >>> sqlcomm1.ExecuteNonQuery()
    >>>
    >>> Query is working fine if there is value in the txtOutDate, but if user
    >>> leaves the field txtOutdate empty, system insert date 1900-01-01. I want
    >>> system to insert null.
    >>>
    >>> How can I achieve that?
    >>>
    >>> Regards
    >>> Arek
    >>>
    >>>
    >>>
    >>>
    >>> *** Sent via Developersdex http://www.developersdex.com ***
    >>> Don't just participate in USENET...get rewarded for it!
    >>>

    >
    >
     
    Greg Burns, Oct 6, 2004
    #5
  6. Arek

    Arek Guest

    I was thinking about using parameters and converting my query to stored
    procedure. The thing is that this is just part of the query and query
    itself is big. So I was trying to avoid this.
    Anyway, my outdate is in datetime format.
    I will try to use proposed here solutions and I will post whatever
    worked for me...:)

    Regards


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Arek, Oct 7, 2004
    #6
  7. Arek

    Arek Guest

    Ok,

    I finally use query with parameters and then check
    If Not TxtOutDate.Text = "" Then
    sqlcomm1.Parameters.Add("@TxtOutDate",
    SqlDbType.SmallDateTime).Value = TxtOutDate.Text
    Else
    sqlcomm1.Parameters.Add("@TxtOutDate",
    SqlDbType.SmallDateTime).Value = DBNull.Value
    End If

    This is working for me.

    When I tried to use CDate or Cint, or if not txtOutdate.text =
    String.Empty, system was giving me errors.

    Thank you for your help
    Arek



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Arek, Oct 7, 2004
    #7
  8. Arek

    Arek Guest

    Ok,

    I finally use query with parameters and then check
    If Not TxtOutDate.Text = "" Then
    sqlcomm1.Parameters.Add("@TxtOutDate",
    SqlDbType.SmallDateTime).Value = TxtOutDate.Text
    Else
    sqlcomm1.Parameters.Add("@TxtOutDate",
    SqlDbType.SmallDateTime).Value = DBNull.Value
    End If

    This is working for me.

    When I tried to use CDate or Cint, or if not txtOutdate.text =
    String.Empty, system was giving me errors.

    Thank you for your help
    Arek



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Arek, Oct 7, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TN Bella
    Replies:
    1
    Views:
    1,556
    TN Bella
    Jul 1, 2004
  2. David Lozzi

    Null Dates are not 1/1/1900

    David Lozzi, May 18, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    16,201
    Kevin Spencer
    May 19, 2005
  3. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,367
    Michael Borgwardt
    May 30, 2004
  4. Aaron [SQL Server MVP]

    Date in SQL: 1/1/1900

    Aaron [SQL Server MVP], Jun 9, 2004, in forum: ASP General
    Replies:
    5
    Views:
    163
    Ice Man
    Jun 11, 2004
  5. Replies:
    1
    Views:
    236
    Jano Svitok
    Jul 17, 2007
Loading...

Share This Page