|
I am running a SQL statement in C#, where I am using the WHERE clause to limit a query to a date.
Here's the SQL:
DbCommand.CommandText = "SELECT SUM-DATE, SUM-STORE, SUM(SUM-RETURNS) FROM SUMMARY WHERE SUM-DATE = '" + dtProcDate + "' GROUP BY SUM-STORE";
The dtProcDate is assigned here:
if (DateTime.Now.DayOfWeek == DayOfWeek.Monday)
{
dtProcDate = DateTime.Now.AddDays(-2);
}
else
{
dtProcDate = DateTime.Now.AddDays(-1);
}
When I query just the date, I get the date in the MM/DD/YYYY HH:MM:SS AM/PM format, which is what dtProcDate shows as it's format also.
Here is the Error:
ERROR [HY000] Data Type Mismatch in WHERE clause, Value '10/24/2009 2:22:39 PM'.
What am I missing here?
Jude
|
|
|
|
|
Let me know what database you are using??
check this :
here[^]
|
|
|
|
|
Thanx for the link.
The database is System Z.
I am no longer getting a type mismatch. I changed dtProcDate to dtProcDate.ToShortDateString(), but I am not getting the result I want.
Jude
|
|
|
|
|
Depends. The most likely pair are:
1) SUM-DATE is not a DateTime.
2) Your datetime format is not what the database is expecting. Remember that
string + DateTime + string is an implicit
string + DateTime.ToString() + string which means it is formated to your current default environment - in this case "dd/mm/yyyy". Try changing to
dtProcDate.ToString("yyyy-MM-dd HH:mm:ss"); and you should be fine.
See here for DateTime.ToString formats[^]
No trees were harmed in the sending of this message; however, a significant number of electrons were slightly inconvenienced.
This message is made of fully recyclable Zeros and Ones
|
|
|
|
|
Thanx, but that gives me a syntax error.
When I do dtProcDate.ToShortDateString(), I do not receive an error, but I do not get the results that I need.
This SQL statement worked in Access....
Jude
|
|
|
|
|
SQL statements based on ToShortDateString() make absolutely no sense to me, as that method relies on the Regional Settings whereas a database should not be localized. You really should use an explicit format specification as in ToString("yyyy-MM-dd")
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
True. I was trying it for troubleshooting.
When I query just the date I get a MM/DD/YYYY HH:MM:SS AM/PM in return.
When I use just the dtProcDate in a compare, I receive an error of Syntax Error: Unprocessed input. Net token '6' (which is the hour of the time).
When I try to use either # or quotes around the date I receive Syntax Error: Required text missing. Next token '#'.
When I try a dtProcDate.ToString("MM:dd:yyyy hh:mm tt") I receive a Syntax Error: Unprocessed input Next Token: '06' (which is the hour of the time here).
I am at a loss. I have never had this much trouble comparing dates!
Jude
|
|
|
|
|
TheJudeDude wrote: I was trying it for troubleshooting
troubleshooting code better be correct too.
TheJudeDude wrote: When I query just the date I get a MM/DD/YYYY HH:MM:SS AM/PM in return
No. the database returns a date, which is a struct containing some numbers. And for human consumption your PC converts that into a string, according to some rules, by default influenced by the Regional Settings. That Control Panel is exactly how the human user tells the system what he likes to use as date and time formats. An explicit formatting string allows you to specify a different format, independent of Regional Settings.
SQL wants date/datetime according to ISO 8601, which means dashes in the date, colons in the time, year-month-day order, a 'T' in between, etc etc. The delimiter (quote, hash, whatever) may depend on the exact database.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Thanx for the input. Are you familiar with System Z?
Jude
|
|
|
|
|
TheJudeDude wrote: Are you familiar with System Z?
No I'm not. Google knows about it. Seems to be an IBM thing. If this isn't all happening on a Windows PC, how does it fit the C# forum at all?
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Yes google knows about it, just thought I may ask you. All I am looking for is to make a date comparison work. The database is on a Red Hat server. I need to make reports from the data through an ODBC connection from a Windows platform.
There is very little documentation from the provider and the support is spotty. The company I work for just changed our POS software and I am trying to put out the same reports that management are used to having.
Well, thanks for your input so far.
Jude
|
|
|
|
|
if all documentation were missing, I would try a few experiments on a DATE field:
SELECT * FROM tablename WHERE dateFieldName='2009-10-27'
SELECT * FROM tablename WHERE dateFieldName=#2009-10-27#
SELECT * FROM tablename WHERE dateFieldName=`2009-10-27`
or on a DATETIME field, same tests however replace = by >=
all this assumes a new record got added with dateFieldName set to NOW() or CURDATE() or whatever yields the current date or datetime.
And of course my entire test would sit in a try-catch with the catch displaying exception.ToString()
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Thank you so much for your help!
But...here is the report:
Single quotes, '10/25/2009' returns an error of Type Mismatch in WHERE clause, value '10/25/2009'
A pound around the date returns an error of Syntax Error: Required text missing. Next token '#'
The ` character around the date returns the same as the previous. (thought this was the one that would work due to using ` in bash scripting and this being a *nix server)
Using the DateTime field dtProcDate by itself returns an error of Unprocessed input. Next token '8' (which is the hour of the day)
Doing the same on >= would yield the same result.
The data is updated daily at midnight local time, so the data is static for the date used.
And all is set in a try/catch.
These pretzels are making me thirsty!
Jude
|
|
|
|
|
For the last time, it is bound to be #year-month-day# where # is some special character
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
|
Hi,
AFAIK SQL needs Date and DateTime literals to be in a specific format, which is independent of how your system shows dates and datetimes to the human user (which gets controlled by the Regional Settings Control Panel).
I used Google "SQL literal date" and found this[^] amongst many others referring to ISO 8601.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
I am new to C# but have developed an application that worked until the need came up to ask for two additional fields of information. Because of the way the app is structured, I decided to ask for these two fields in a second form (Form2) using a dialog box. This seems to work well and finally was able to retrieve the two textboxes into the first form. I have two problems.
1) I am not sure where I should check that both fields have been filled. I have tried in both forms but although I can generate a message telling the user that both are required, I cannot seem to get Form2 to redisplay for the user to fill in the fields. The code continues to the next step. I want it to - if fields empty then display message and redisplay form. Where is the best place to do this check and redisplay the form until the user fills in both fields and hits OK or hits cancel?
2) For some reason Form2 gets displayed twice. I enter both fields and hit OK and the form is redisplayed with my input and I have to hit OK again. Any ideas how this can happen? I use the normal code.
Form2 qForm = new Form2();
qForm.ShowDialog();
Thanx in advance for any help. This site has been a big help in working through this. 
|
|
|
|
|
The validation should be done in the form that contains the fields and the form should not be dismissed until valid data has been entered
Form2 qForm = new Form2();
if( qForm.ShowDialog() == DialogResults.OK )
{
}
only two letters away from being an asset
|
|
|
|
|
Thank you all for your responses. Here is the code that I have in Form1
public void ShowMyDialog()
{
Form2 qForm = new Form2();
qForm.ShowDialog();
if (qForm.ShowDialog ()== DialogResult.OK)
{
CaseNo = qForm.CaseNo;
Reason = qForm.Reason;
MessageBox.Show("Form2 Exists" + qForm.CaseNo + qForm.Reason);
if (textBox1.Text == "" || textBox2.Text == "")
{
MessageBox.Show("Both Case No and Reason are required");
ShowMyDialog();
}
}
}
Here's what is in Form2
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
public string CaseNo
{
get { return textBox1.Text; }
}
public string Reason
{
get { return textBox2.Text; }
}
public void button1_Click(object sender, System.EventArgs e)
{
}
}
CaseNo and Reason flow back to Form1 but I am having a problem knowing how and where to check for the these two fields being present. These code sees if the fields are present but then just passes through. Mark, is this what you were suggesting?
Thanx again!!!
|
|
|
|
|
Close but quite there yet.
Either use RequiredField validators for the CaseNo and Reason textboxes on Form2 or validate them in the click event.
public void button1_Click(object sender, System.EventArgs e)
{
if(string.IsNullOrEmpty(CaseNo) || string.IsNullOrEmpty(Reason) )
{
MessageBox(...);
}
else
{
DialogResult = DialogResult.OK;
Close();
}
}
Now Form2 will not be dismissed until the fields are valid.
Also check to make sure button1 is not set as the default button for form2 or it will close the form automatically.
only two letters away from being an asset
|
|
|
|
|
That helped a lot. It is much closer to what it should do. OK was set as default. Thank you Mark!!
However, what would cause Form2 to be redisplayed after pressing OK or Cancel? Form2 pops up again and I have to press either OK or Cancel for it to continue.
Thanx again for your help. It's these little gotchas that make this so exciting. 
|
|
|
|
|
JTRizos wrote: what would cause Form2 to be redisplayed after pressing OK or Cancel?
Something in the code, it doesn't just happen. Are you calling the Show method again?
only two letters away from being an asset
|
|
|
|
|
No, I don't but it seems I need to look closer at the code. The required field code you suggested in Form2 is processed twice (that's for submittals with an empty field) and then drops back to the next step in the code back in Form1 even if both fields are not filled. Cannot figure out why but it seems to want to process Form2 just twice or pop up Form2 again if the fields are filled.
Thanx for your help. If you can think of something or anyone else who offered an answer earlier, I would be much appreciative.
|
|
|
|
|
JTRizos: "No, I don't"
Err... yes you do!
Form2 qForm = new Form2();
qForm.ShowDialog();
if (qForm.ShowDialog ()== DialogResult.OK)
|
|
|
|
|
Yes, I know. Learned that yesterday. Still learning C# and it's little proclivities. Thanx for responding.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
