Solved

DataSet changes values is possible?

Posted on 2007-11-21
9
245 Views
Last Modified: 2010-04-15
I populate a DataSet using a sql query, one of the columns is a date in oracle format, I want to convert this to a proper date, I am therefore trying to loop through the dataset date column, and convert each entry to a date.  Doesnt seem to be working though, here is the code:


for(int a=0;a<=ds.Tables[0].Rows.Count-1;a++)
{
DateTime basetime2=DateTime.Parse("31 Dec 1969 18:00:00");
basetime2 = basetime2.AddSeconds(double.Parse(ds.Tables[0].Rows[a].ItemArray[2].ToString()));
ds.Tables[0].Rows[a].ItemArray[2]=basetime2.ToString();
}

DataGrid1.DataSource = ds;
DataGrid1.DataBind();

why is this not working, and is there an easier way?
0
Comment
Question by:sterankin
  • 5
  • 3
9 Comments
 
LVL 40

Expert Comment

by:evilrix
ID: 20327923
>> why is this not working?
Please clarify what you mean by not working.
0
 

Author Comment

by:sterankin
ID: 20327967
Sorry for not being clear, when I bind the dataset to the datagrid, the DATE column is still in the oracle format, like this:

REF_NUM             STATUS           OPEN_DATE             C_LAST_NAME          C_FIRST_NAME
5992436                    RIP              1195494904                        Doe                           John


As you can see above, the OPEN_DATE has not been coverted to a proper date.
0
 
LVL 40

Accepted Solution

by:
evilrix earned 75 total points
ID: 20328117
Are there no Oracle functions to manipulate data as part of your query?

Something like (this is MySQL)...

select DATE_FORMAT(mydate, "dd/mm/yyyy") from mytable;

I found this: -
http://www.oradev.com/oracle_date_format.jsp
select to_char(mydate,'DD/MM/YYYY HH24:MI:SS') from mytable;

Does that help? If not try looking at date formatting functions in Oracle (sorry, I don't know Orable).
0
 

Author Comment

by:sterankin
ID: 20328192
Thanks but that did not work - the date is stored in oracle as the number of seconds after 31 Dec 1969 18:00:00.

So to convert it to the correct date I need to add the number of seconds to 31 Dec 1969 18:00:00 to get the correct date.

e.g. if the date stored in oracle is like this:  1195494904    
I need to add 1195494904   as seconds to 31 Dec 1969 18:00:00 in order to get the correct date.

I was trying to loop through the dataset to do this, but I'm not sure if you can edit the values in a dataset before it is bound to a datagrid, that is what I am trying to find out.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:sterankin
ID: 20328305

I should not that it IS converting the date correctly, just not changing them in the dataset.

i,e, if I add this code:
TextBox1.Text=basetime2.ToString();

The textbox displays the correct date in the correct format.  So is it not therefore possible to edit the entries in a dataset?

How else could I do this so I can display the correctly formatted dates in the datagrid?
0
 

Author Comment

by:sterankin
ID: 20328531

This is really confusing me now so I have increased the points, I think it should be ok to edit the dataset through the loop, but it does not seem to be stroring the value?

code:

OdbcConnection conn = new OdbcConnection(connectionString);

				conn.Open();

				OdbcDataAdapter da = new OdbcDataAdapter (openSql, conn);

				DataSet ds = new DataSet();

				da.Fill(ds);

				

				conn.Close();

				//da.Dispose();

				

				for(int a=0;a<=ds.Tables[0].Rows.Count-1;a++)

				{

					DateTime basetime2=DateTime.Parse("31 Dec 1969 18:00:00");

					 basetime2 = basetime2.AddSeconds(double.Parse(ds.Tables[0].Rows[a].ItemArray[2].ToString()));

					

ds.Tables[0].Rows[a].ItemArray[2] = basetime2.ToString();

TextBox1.Text=basetime2.ToString(); // THIS WORKS!!

TextBox1.Text=ds.Tables[0].Rows[a].ItemArray[2].ToString(); // THIS DOESNT! WHY???

				}
 

		DataGrid1.DataSource = ds;

		DataGrid1.DataBind();

Open in new window

0
 
LVL 22

Assisted Solution

by:p_davis
p_davis earned 50 total points
ID: 20328993
you will have to call ds.AcceptChanges before binding your grid
 
also i would suggest using a foreach statement instead of the for loop

foreach(Datarow row in ds.tables[0].rows)
{
    row["yourdatetimefieldname"] = <whatever value you want> //(omit the <>
}

ds.acceptchanges();

//gridbinding here
0
 

Author Comment

by:sterankin
ID: 20333669

This is how I managed to get it to work by manipulating the SQL, so thanks to evilrix for pointing me in that direction.
p_davis I have not tried your solution but it look like what I was originally looking for.

Anyway here is my sql solution:

SELECT to_date('31/12/1969 23:59:59','DD/MM/YYYY HH24:MI:SS')+(HGR6.CALL_REQ.OPEN_DATE/(24*60*60)) OPENDATE FROM...
0
 
LVL 40

Expert Comment

by:evilrix
ID: 20333691
Glad to have helped :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now