Re: Universal Date Time Formatting with C# - C# Discussion Boards

11-Dec-07 9:08

I have an application that needs to record data in a database and the particular date when that data was written to the database. More, I need customers to be able to retrieve data from the database by date or date range. To that end, the MS Access database I'm using has a field called TestDate of type Date/Time, and I set up two date/time pickers to allow customers to select a date or date range from which to get data.

All this works well and good until I change my regional/language options so the date format changes, say from mm/dd/yyyy to dd/mm/yyyy. Then, although the MS Access database updates the TestDate data automatically to reflect the change in date/time format and my date/time pickers in the C# appilcation reflect the altered format, the result of executing SQL statements based on the date/time pickers does not return the correct data from the database.

Rather, selecting all data from December 10, 2007 retrieves data from October 12, 2007. By contrast, if I select, say November 29, 2007, the correct data is returned. Something, then, is getting confused about ambiguous dates.

Any suggestions would be appreciated.

Pete O'Hanlon11-Dec-07 9:38

Pete O'Hanlon

11-Dec-07 9:38

You are storing using DateTime fields from the textboxes aren't you, rather than using a string? More importantly, you are using a parameter to put the field into the database aren't you?

If you want to force a UTC date, then you use the U format qualifier with the DateTime. For example

DateTime dt = DateTime.Now;
Console.WriteLine(dt.ToString("U"));

Deja View - the feeling that you've seen this post before.

My blog | My articles

polishprogrammer11-Dec-07 11:42

11-Dec-07 11:42

Yes, I am using a parameterized query to insert new records into the database and storing the data using the DateTime data type rather than a string.

Still, somewhere between picking the datetime for the SELECT statement and retrieving the data from the database, the date time format gets lost in translation. If I select a non-ambiguous date such as 13/09/2007 (September 13, 2007), execution of the SQL command returns the correct data, but selecting an ambiguous date, such as 3/12/2007 (intending December 3, 2007) returns erroneous data. The report shows 12/3/2007 (March 12, 2007), but the value of the date prior to the execution of the SQL statement is 3/12/2007 (December 3, 2007).

RobScripta12-Dec-07 1:14

RobScripta

12-Dec-07 1:14

Hi, I'm learning C# by lurking the forums, but have much experience in Access. This solution works in Access, and I think it will work in C# but haven't tested yet.

In an Access query you have to enclose the date in # to force american date format.

This is how I do this (In Access)

Function ScriptaUDF(Datum As Date)

ScriptaUDF = "#" & Trim(Str(Month(Datum))) & "/" & Trim(Str(Day(Datum))) & "/" & Trim(Str(Year(Datum))) & "#"

End Function

So whenever I need a date in a query I use:

where (((Table.datefield) = " & ScriptaUDF(Me!Datefield) & "))

I hope this helps

polishprogrammer12-Dec-07 3:00

12-Dec-07 3:00

That's interesting. The problem I've been having occurs, seemingly, during the execution of the SQL statement. I enclose my SQL SELECT statements with a # sign currently, so

"SELECT * FROM Table WHERE Date BETWEEN #" startdate #" AND #" endate"

If the # character forces an American date format, that would explain much, I think.

polishprogrammer12-Dec-07 3:27

12-Dec-07 3:27

How would I specify a date in a SQL expression without forcing the american date format? Is there something I can use to insert a date in the SQL query without using the # sign? Or, if I use the # sign, can I simply force the format that is needed?

polishprogrammer12-Dec-07 6:25

How to import the data inside a text file into a database?

12-Dec-07 6:25

I found a solution. Basically, the Jet Engine seems to have problems with translation. To get the correct data, I have to do this:

SELECT * FROM TABLE WHERE Date BETWEEN Format(#" + startDate + "#,'mm/dd/yyyy')AND Format(#" + endDate + "#,'mm/dd/yyyy')

By setting the format in the SQL statement to United States format, the data seems to come back to me correctly.

josephx2k711-Dec-07 8:49

josephx2k7

11-Dec-07 8:49

If I have a text file containing the first line like this:
John 1976 200,000 United States

How do I import it to the database specifying that the first column should be John, second is 1976, third is 200,000, and last column is United States?

What are the commands/syntax being used to achieve this? Thanks.

Re: How to import the data inside a text file into a database?

Christian Graus11-Dec-07 9:27

Christian Graus

11-Dec-07 9:27

Your text file format is a disaster. So long as country is the only field that can have a space in it, you may be OK. You use the string.Split method to turn this into a string array. If there's a fifth or sixth element, you append those to the fourth ( that's the country ). Then you have values to stuff into the database.

Re: How to import the data inside a text file into a database?

josephx2k711-Dec-07 10:13

josephx2k7

11-Dec-07 10:13

Thanks. How do we use bcp or bulk insert to import the data in text file into the database? I haven't done anything with databases or ADO.Net so I might need some step by step procedure to do this.

Re: How to import the data inside a text file into a database?

Christian Graus11-Dec-07 13:46

Christian Graus

11-Dec-07 13:46

In that case, I'd recommend going through an article on the topic, on this site or elsewhere. It's just going to cover things in far more depth and clarity than I can hope to do in a forum reply.

Is it possible to create instance aliases in C#?

Skippums11-Dec-07 8:05

Re: Is it possible to create instance aliases in C#? [modified]

11-Dec-07 8:05

I answered a question a few posts ago, where a user thought that assigning reference types was creating aliases for the names of those types. After thinking about it, I began to wonder if there IS a way to do this, so that you have two instances referencing the same pointer to the object. For example, is there a way to do the following in C# (I doubt it, because it was often the cause of many problems in C++, but just thought I would ask)?

object A = new object();
// The goal of the next line is to ensure that object.ReferenceEquals(A, B) is ALWAYS true
// I know this doesn't compile, but is there some way to do this?
object B = ref A;
// Now, after the prior statement (whatever it is), setting EITHER A or B changes BOTH A and B
B = new object();
// At this point object.ReferenceEquals(A, B) should still be true

Hope this question makes sense. Thanks,

Sounds like somebody's got a case of the Mondays

-Jeff

Luc Pattyn11-Dec-07 8:42

Luc Pattyn

11-Dec-07 8:42

Hi Jeff,

I have never seen such thing, so AFAIK it is impossible.
Also, IMO the C# language designers would consider it unnecessary and unsafe.
And if you look at pointers, they can only point to value types.

So I think you will have to refactor the code and substitute every B by A.

[added]
the feature you're asking about is similar to the union in C/C++;
it is not supported in C#, although there is a way to mimic it inside structs,
by giving explicit FieldOffset values

I use unions (in C that is) only to byte-convert from one type to another, not to get two names for
the same thing; sometimes I miss it in C#. Not sure how the C# compiler would cope with the latter.
[/added]

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

Sorry for any delays in replying, I currently don't always get e-mail notifications.

modified on Tuesday, December 11, 2007 2:49:59 PM

Re: Is it possible to create instance aliases in C#?

Skippums11-Dec-07 9:09

Re: Is it possible to create instance aliases in C#?

11-Dec-07 9:09

Yeah, I remembered doing it in C++ before, but couldn't remember what the keyword was. I don't really have a need (nor see a need) to do this, since, as you said, I could just substitute the variable A for all occurances of B and still have identical code. This is a feature that I think should NOT have been implemented in C#, so I am glad to see that it isn't. Thanks Luc,

Sounds like somebody's got a case of the Mondays

-Jeff

Skippums11-Dec-07 9:26

Re: Is it possible to create instance aliases in C#?

11-Dec-07 9:26

After googling for a while, I determined that the code I was refering to in C++ would have looked something like this (I think)...

int i = 0;
int j = &i;
j = 5; // Doesn't this also change i?

I think that the previous code forces i and j to always be equivalent, but again, it has been a while since I programmed in C++, so I can't be certain. Thanks,

Sounds like somebody's got a case of the Mondays

-Jeff

Luc Pattyn11-Dec-07 9:48

Luc Pattyn

11-Dec-07 9:48

Skippums wrote:
int j = &i;

that would not compile, it should be int* j = &i; so there is only a single
int, and a pointer that now points to the same int. So changing it either with i=...
or with *j=... results in changing the single int, whatever way you access it,
it is still a single variable.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

Sorry for any delays in replying, I currently don't always get e-mail notifications.

Format code for a decimal number.

dbrenth11-Dec-07 7:37

dbrenth

11-Dec-07 7:37

I was wondering if there is a simple number format code for if you only want to see the decimal portion of the number if there are any significant digits to the right of the decimal - otherwise show the number as an integer.

ex. 3.5 -> 3.5 and 3.0 -> 3 (instead of 3.0)

Thanks

Brent

Re: Format code for a decimal number.

pmarfleet11-Dec-07 7:54

pmarfleet

11-Dec-07 7:54

Use something like:

num.ToString("0.#")

This will display the string representation of the number including the first significant digit to the right of the decimal point if required.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Receiving Connection Error w/ SQL DB File on Remote/Hosting Server

dboy22111-Dec-07 7:17

dboy221

11-Dec-07 7:17

I have an app that connects to a db in the Data Directory for Website Project. I am able to connect to the database fine locally. However, after I uploaded my project to my hosting provider's server and try to process a form that does a sql insert, I receive the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure my be caused by the fact that under the default settings SQL server does not allow remote connections. (provider: Sql Network Interfaces, error 26 - Error Locating Server/Instance Specified.

My connection string to the datasource is as such in my webconfig file:

connectionString="DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|DefinaBackEnd.mdf;IntegratedSecurity=True;ConnectTimeout=30;User Instance=True"

and the call to the connecting string is called as such:
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefinaBackEndConnectionString"].ConnectionString);
conn.Open();

Can someone please assist with how to resolve this as this site just looks for a sql db file as opposed to an instance of sql server itself. Thanks to all in advance.

Classes and references

User 665811-Dec-07 6:42

User 6658

11-Dec-07 6:42

Hi, let's take this piece of code:

Engine newEngine = new Engine(200, "PS");
Engine oldEngine = null;

oldEngine = Car.Engine; // store old engine of this car
Car.Engine = newEngine; // give this car a new engine
... // do something with this engine
Car.Engine = oldEngine; // put back the old engine

If Engine is a class (reference type), shouldn't oldEngine share the same value like newEngine? Right now they have different values.

regards

modified 12-Sep-18 21:01pm.

Re: Classes and references

Skippums11-Dec-07 6:59