Error retrieving date from SQL Database

[DotNetNewbie]

Hi,

I am sure this is something really really simple, but for the life of me I cannot see the mistake:

I have an SQL database which I am retrieving information from, however when I retrieve any date information I get the following error:

System.IndexOutOfRangeException was unhandled

The field within the database is a date field, which contains a number of records, all the dates are as follows:

12/01/09

The line on which the error appears is:

TxtDatePurchased.Text = DataRS.Item("PurchasedDate").ToString

Any ideas, I know it is going to be something really simple.

Cheers in advance.

D

 

[gmmastros]

Can you show the query?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DotNetNewbie]

Hi,

The query that is ran is:

SQLOpen = "SELECT * FROM Asset WHERE AssetID = " & LstAssets.SelectedItems(0).SubItems(0).Text & ""

SQLCmd = New SqlCommand(SQLOpen, Conn)
Try
Dim DataRS As SqlDataReader = SQLCmd.ExecuteReader
If DataRS.HasRows = True Then
DataRS.Read()
TxtAsset.Text = DataRS.Item(0).ToString()
TxtPurchaseOrderNumber.Text = DataRS.Item("PurchaseNo").ToString
TxtDatePurchased.Text = Convert.ToDateTime(DataRS.Item("PurchasedDate").ToString)


TxtAsset.Enabled = False
End If
Catch ex As SqlException
MsgBox(ex.Message.ToString() + "Unable to load information from the SQL Table, please check that the login details have been saved correctly.", MsgBoxStyle.Critical, "Unable to access Database table")
End Try
Conn.Close()

 

[gmmastros]

If PurchasedDate is a DateTime column in the database, then it must contain a valid date (because the database wouldn't allow it to be invalid). Therefore...

1. You could have a null value in the PurchasedDate column.
2. Your PurchasedDate is a varchar column and contains invalid data.

As far as I can tell, those are the only reasons why you could be having this problem.

You can check for this in a query window by running the following queries:

Select * From Asset Where PurchasedDate Is NULL

To see the data type:

Select Data_Type 
From   Information_Schema.Columns
Where  Table_Name = 'Asset'
       And Column_Name = 'PurchasedDate'

If the above query does NOT return DateTime, then you can find invalid dates in the table with this:

Select *
From   Asset
Where  IsDate(PurchasedDate) = 0

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DotNetNewbie]

George,

Much apprecited, I will delete the records from the database and try again, just in case its some form of corruption.

Then execute your suggestions and see what happens.

Thank again.

D

 

[RiverGuy]

System.IndexOutOfRangeException

To me, this seems like you're referencing an element of your collection which does not exist. My guess is that you've typed the name of your PurchasedDate column incorrectly in this line of code:

DataRS.Item("PurchasedDate").ToString

However, that may not be it. One other thing I noticed is that this line of code doesn't make much sense:

TxtDatePurchased.Text = Convert.ToDateTime(DataRS.Item("PurchasedDate").ToString)

If your column is defined as DateTime in SQL Server, what you are doing is return a DateTime, casting it to a String, and then casting it back to DateTime. I would get rid of the Convert.ToDateTime part of your code. The .Text property of your TextBox takes a string, after all.

 

[DotNetNewbie]

RiverGuy,

The convert statement was added as someone else recommented it from another forum. It isnt in there anymore.

cheers for the advice.

D

 

[DotNetNewbie]

Hi,

I have resolved it. I recreated the table and it worked, not sure of the cause!

Thanks everyone for your time and assistance.

D.