Solved

DataSet changes values is possible?

Posted on 2007-11-21
9
248 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 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

26 Experts available now in Live!

Get 1:1 Help Now