Solved

DataSet changes values is possible?

Posted on 2007-11-21
9
250 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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