[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

DataSet changes values is possible?

Posted on 2007-11-21
9
Medium Priority
?
255 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
[X]
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
  • 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 225 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 150 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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