Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 31st, 2009, 11:17 AM
Friend of Wrox
 
Join Date: Feb 2009
Posts: 194
Thanks: 5
Thanked 3 Times in 3 Posts
Default SqlDateTime overflow

I think this is an ASP.NET error so I am putting it here.

Basically I am developing a site which has a Recent News section which contains the date it was inserted, heading and then a link to the actual page where they can read more.

I have designed a table which has the following structure

Id-Int, No Nulls, Primary Key, Is Identity,
Date- DateTime, Nulls has a default value or binding of getdate()
News- nvarchar(MAX), No Nulls
Url- nvarchar(50), No Nulls

I have then configured a ListView control which will allow me to insert, Edit, Update & Delete new content.

So it compiles and builds fine and I enter the news and the url where more can be read. But when I click insert I get the error:

"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."


At first I thought that maybe it was because the table originally had a null value for the dateTime initially and therefore the value wasn't a date however after changing it to accept null values I still get the same error.

I think I need to add some code to the codebehind page in the inserting event handler to format the date however I am unsure if this is correct or how to proceed with it. Currently the codebehind has no code in it for the particular page that I am getting the error. Just to clarify my connection to the LinqDataSource works and everything aswell.

Any help would be great if you need anymore information, I wasn't sure what was needed so I have tried to be as thorough as possible.

Thanks in advance
  #2 (permalink)  
Old March 31st, 2009, 01:00 PM
Friend of Wrox
 
Join Date: Nov 2007
Location: Central Florida, USA.
Posts: 207
Thanks: 2
Thanked 15 Times in 15 Posts
Default What's your code?

What's your insert code? how are you inserting the data? you may need something like IsNull(@Date,GetDate())
__________________
Jason Hall

Follow me on Twitter @jhall2013
  #3 (permalink)  
Old April 1st, 2009, 04:43 AM
Friend of Wrox
 
Join Date: Feb 2009
Posts: 194
Thanks: 5
Thanked 3 Times in 3 Posts
Default

I don't have any...?

I dragged a linqdatasource on to the page configured it to link to a table in my app_code folder in the .dbml file.

then i added a listview control pointed it to the linqdatasource. Removed the parts out of it I didn't need and then built and ran the page which works fine In that I can edit, Update and delete I just get an error on the insert.

So I am thinking that I need some sort of code on the inserted/inserting event handler or maybe my table is wrong.

The idea is that the table will automatically generate the value for the particular date & time when the item is inserted. If that makes sense?
  #4 (permalink)  
Old April 1st, 2009, 08:42 AM
Friend of Wrox
 
Join Date: Nov 2007
Location: Central Florida, USA.
Posts: 207
Thanks: 2
Thanked 15 Times in 15 Posts
Default OK...

can you post the code of your linqdatasource?

look at the insert template if you want it to insert you should have some sort of code there?
__________________
Jason Hall

Follow me on Twitter @jhall2013
  #5 (permalink)  
Old April 1st, 2009, 11:27 AM
Authorized User
 
Join Date: Dec 2008
Posts: 29
Thanks: 1
Thanked 5 Times in 5 Posts
Default

During your insert, make sure you update the Date as

Date = DateTime.Now or other time you deemed appropriate.

before the submit.
  #6 (permalink)  
Old April 1st, 2009, 12:30 PM
Friend of Wrox
 
Join Date: Feb 2009
Posts: 194
Thanks: 5
Thanked 3 Times in 3 Posts
Default

The code I have for the ListView and the LinqDataSource is below:

Code:
      <asp:ListView ID="ListView1" runat="server" DataKeyNames="Id" 
        DataSourceID="LinqDataSourceRecentNews" InsertItemPosition="LastItem">
        <ItemTemplate>
          <br />
          News:
          <asp:Label ID="NewsLabel" runat="server" Text='<%# Eval("News") %>' />
          <br />
          Url:
          <asp:Label ID="UrlLabel" runat="server" Text='<%# Eval("Url") %>' />
          <br />
          <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
          <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
            Text="Delete" />
          <br />
          <br />
          </span>
        </ItemTemplate>
        <AlternatingItemTemplate>
          <span style="">Id:
          <asp:Label ID="IdLabel" runat="server" Text='<%# Eval("Id") %>' />
          <br />
          date:
          <asp:Label ID="dateLabel" runat="server" Text='<%# Eval("date") %>' />
          <br />
          News:
          <asp:Label ID="NewsLabel" runat="server" Text='<%# Eval("News") %>' />
          <br />
          Url:
          <asp:Label ID="UrlLabel" runat="server" Text='<%# Eval("Url") %>' />
          <br />
          <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
          <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
            Text="Delete" />
          <br />
          <br />
          </span>
        </AlternatingItemTemplate>
        <EmptyDataTemplate>
          <span>No data was returned.</span>
        </EmptyDataTemplate>
        <InsertItemTemplate>
           News:
          <asp:TextBox ID="NewsTextBox" runat="server" Text='<%# Bind("News") %>' />
          <br />
          Url:
          <asp:TextBox ID="UrlTextBox" runat="server" Text='<%# Bind("Url") %>' />
          <br />
          <asp:Button ID="InsertButton" runat="server" CommandName="Insert" 
            Text="Insert" />
          <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" 
            Text="Clear" />
          <br />
          <br />
          </span>
        </InsertItemTemplate>
        <LayoutTemplate>
          <div ID="itemPlaceholderContainer" runat="server" style="">
            <span ID="itemPlaceholder" runat="server" />
          </div>
          <div style="">
            <asp:DataPager ID="DataPager1" runat="server">
              <Fields>
                <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" 
                  ShowLastPageButton="True" />
              </Fields>
            </asp:DataPager>
          </div>
        </LayoutTemplate>
        <EditItemTemplate>
          <span style="">Id:
          <asp:Label ID="IdLabel1" runat="server" Text='<%# Eval("Id") %>' />
          <br />
          date:
          <asp:TextBox ID="dateTextBox" runat="server" Text='<%# Bind("date") %>' />
          <br />
          News:
          <asp:TextBox ID="NewsTextBox" runat="server" Text='<%# Bind("News") %>' />
          <br />
          Url:
          <asp:TextBox ID="UrlTextBox" runat="server" Text='<%# Bind("Url") %>' />
          <br />
          <asp:Button ID="UpdateButton" runat="server" CommandName="Update" 
            Text="Update" />
          <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" 
            Text="Cancel" />
          <br />
          <br />
          </span>
        </EditItemTemplate>
        <SelectedItemTemplate>
          <span style="">Id:
          <asp:Label ID="IdLabel" runat="server" Text='<%# Eval("Id") %>' />
          <br />
          date:
          <asp:Label ID="dateLabel" runat="server" Text='<%# Eval("date") %>' />
          <br />
          News:
          <asp:Label ID="NewsLabel" runat="server" Text='<%# Eval("News") %>' />
          <br />
          Url:
          <asp:Label ID="UrlLabel" runat="server" Text='<%# Eval("Url") %>' />
          <br />
          <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
          <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
            Text="Delete" />
          <br />
          <br />
          </span>
        </SelectedItemTemplate>
      </asp:ListView>
      <asp:LinqDataSource ID="LinqDataSourceRecentNews" runat="server"
          ContextTypeName="TakeInitiativeDataContext" EnableDelete="True"
          EnableInsert="True" EnableUpdate="True" TableName="RecentNews">
    </asp:LinqDataSource>
Sorry if this is going slowly I am still learning and all the datasources and Linq sources confuse me still, but thanks alot for the help so far.
  #7 (permalink)  
Old April 1st, 2009, 05:58 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi Will,

The problem lies in the default for the DateTime datatype. It defaults to 1-1-0001 which is too small for SQL.

What you need to do is change this date before you send the object to SQL Server. A few ways:

1. In *Inserting of the ODS or form control you can set the date and time manually.

or

2. Create a partial class (partial to your entiity in the Linq to SQL diagram) and set the default when an object is constructed. Add a new class to the project with code similar to this:

public partial class YourClassName
{
public void YourClassName()
{
this.Date = DateTime.Now;
}
}

Now whenever you create a new object, its Date property defaults to today's date.

I prefer the second solution as it fixes the problem in the model, not the UI.

Hope this helps,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #8 (permalink)  
Old April 2nd, 2009, 03:07 AM
Friend of Wrox
 
Join Date: Feb 2009
Posts: 194
Thanks: 5
Thanked 3 Times in 3 Posts
Default

Ok just a few more questions then.

You said create a partial class which is partial to the entity in the Linq SQL diagram.

In order to do this I would add a class file to the app_code folder with the correct code to do this and then call upon this class in the codebehind file of the page with the listview control on?

I know that the partial class is at the of the code behind file typically with the name of the page file location. Do I change this or do I add another partial class to the document? If you could reference me to a page in your book that would be useful.

If I am now programatically setting the date (which is my undertsanding of what I am doing) do I still need the getdate() function in my SQL table? For that matter should I change the column type as I am now not using SQL to generate the date?

Lastly would you be able to give me the code in VB?

Thanks alot for your help so far.
  #9 (permalink)  
Old April 2nd, 2009, 03:20 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Quote:
You said create a partial class which is partial to the entity in the Linq SQL diagram. In order to do this I would add a class file to the app_code folder with the correct code to do this and then call upon this class in the codebehind file of the page with the listview control on?
Partially true (no pun intended although it comes in nicely). The part about the class in App_Code os correct. However, since you're using a Partial class you are actually extending the classes generated by the LINQ to SQL designer. So you simply add a method to this class which is then used when instances of your class are created, including those created by LINQ to SQL. No need to do anything in a Code Behind; every time a class is created your code is called automatically. Earlier I mistakenly said you should use a constructor as I thought there wasn't a default one in the L2S diagram, but I was wrong. Instead, you need to override OnCreated (see later)

Quote:
I know that the partial class is at the of the code behind file typically with the name of the page file location. Do I change this or do I add another partial class to the document? If you could reference me to a page in your book that would be useful.
Code Behind uses partial classes indeed, but it's just one application of them. A partial class is simply a class split over two source files. Often they are used (as is the case with LINQ to SQL) to separate automatically generated code from user code. In your case, no ASPX files (and thus no Code Behind files) need to be involved.

Quote:
If I am now programatically setting the date (which is my undertsanding of what I am doing) do I still need the getdate() function in my SQL table? For that matter should I change the column type as I am now not using SQL to generate the date?
It depends. The problem is that you probably made the DateTime required in SQL Server. It's a good practice, especially with a default of GetDate(). The problem is that GetDate() in SQL Server never gets called as .NET sends a valid DateTime to the database. Unfortunately, this DateTime is to small to fit in a SQL Server datetime type and thus you get an error.
Quote:
Lastly would you be able to give me the code in VB?
Of course. Here you go. I extended the Review class from the PlanetWrox project to have CreateDateTime and UpdateDateTime default to today's date. The file is called Review.vb and is located in App_Code:

Code:
 
Imports Microsoft.VisualBasic
 
Partial Public Class Review
    Implements System.ComponentModel.INotifyPropertyChanging, _
    System.ComponentModel.INotifyPropertyChanged
 
  Private Sub OnCreated()
    Me.CreateDateTime = DateTime.Now
    Me.UpdateDateTime = DateTime.Now
  End Sub
End Class
Note that this code is called shortly after the object is constructed. If you're loading an item from the database, the CreateDateTime and UpdateDateTime will be overwritten by the values from the database, as you'd expect.

Hope this helps,

Quote:
Thanks alot for your help so far.
You're welcome.

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #10 (permalink)  
Old April 6th, 2009, 03:28 PM
Friend of Wrox
 
Join Date: Feb 2009
Posts: 194
Thanks: 5
Thanked 3 Times in 3 Posts
Default

Thanks again for the help bit busy at the moment but just wanted to say not being rude.

cheers.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ListBox - overflow possible? snufse ASP.NET 2.0 Basics 0 July 25th, 2008 09:50 AM
Overflow problems! Apocolypse2005 Beginning VB 6 2 September 4th, 2007 10:51 AM
Overflow grstad Classic ASP Databases 18 December 14th, 2006 10:06 AM
regarding Overflow of variable hiruni Access VBA 4 February 27th, 2006 02:17 PM
Overflow Error mateenmohd Classic ASP Basics 4 July 5th, 2005 02:12 AM



All times are GMT -4. The time now is 10:28 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.