Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DataSet changes values is possible?

Posted on 2007-11-21
9
251 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

789 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