What is the problem with this SQL command ?

1qaz2wsx7 · #1 (**permalink**) 08-06-07, 03:13

Hi

What is wrong with this command ?

Code:

string strSql2 = "SELECT toInWords FROM services WHERE serviceTypeCode=" + Convert.ToInt32(str2) + " AND clientCode=" + Convert.ToInt32(str) + " AND fromDate1=#" + date1.Date + "#";

This is the error that accourd:

Quote:

One or more errors occurred during processing of command.
[LNA][Pervasive][ODBC Engine Interface]Syntax Error: SELECT toInWords FROM services WHERE serviceTypeCode=0 AND clientCode=0 AND fromDate1=<< ??? >>#01/01/0001 00:00:00#

What is the problem ?

Thanks.

mirtheil · #2 (**permalink**) 08-06-07, 10:23

The date format is wrong. THe PSQL format is:
'yyyy-mm-dd'

That assumes the "fromDate1" is a PSQL Date. If it's not, you need to tell us what data type it is.

1qaz2wsx7 · #3 (**permalink**) 08-07-07, 04:10

Hi

Yes, fromDate1 is a PSQL date.

And the Date1 variable is a DateTime type.

I also tryed this:

Code:

string strSql2 = "SELECT toInWords FROM services WHERE serviceTypeCode=" + Convert.ToInt32(str2) + " AND clientCode=" + Convert.ToInt32(str) + " AND fromDate1=#08/08/1989#";

And its sayd the same error. :/

What is the problem ?

Thanks.

mirtheil · #4 (**permalink**) 08-07-07, 07:42

Quote:

The date format is wrong. THe PSQL format is:
'yyyy-mm-dd'

That means your SQL statement should be like:
SELECT * FROM table WHERE dateField = '2007-08-07'

Note the single quotes around the date.

1qaz2wsx7 · #5 (**permalink**) 08-07-07, 08:46

Hi

Thanks, but there is another problem now :/

In c# there is a variable type for date and time together
and its not a good format for the PSQL Date field,
so its doing problems, how can i fix it ?

Even when i take a Date field from the dataBase and put it
in a dataGrid, the format is changing automaticly
and a time 00:00:00 is add into it :/

(i changed the field into Char so now its working
but im looking for a way to work with Date)

Thanks.

mirtheil · #6 (**permalink**) 08-07-07, 09:28

The code I've used for Dates in PSQL through C# is:

Code:

System.DateTime dCurDate = System.DateTime.Today;
string sCurDate = dCurDate.Year.ToString() + "-" + dCurDate.Month.ToString() + "-" + dCurDate.Day.ToString();

1qaz2wsx7 · #7 (**permalink**) 08-08-07, 04:45

Hi

I tryed to use what you sayd, and there is another problm :/

The month/day/year is turning the value into Integer so if the value
is 03, its becoming 3, and then when you turn it into string (.tostring())
its stays as 3 and not 03, and that makes a problem because PSQL wants
yyyy-mm-dd and not yyyy-m-d.

mirtheil · #8 (**permalink**) 08-08-07, 07:00

The code I posted works for me against a PSQL database. It should work. If it's not, post the exact error.

1qaz2wsx7 · #9 (**permalink**) 08-08-07, 07:41

Here is the error message:

Quote:

One or more errors occurred during processing of command.
[LNA][Pervasive][ODBC Engine Interface]Invalid date, time or timestamp value.
[LNA][Pervasive][ODBC Engine Interface]Error in predicate: fromDate1 = '2004-3-4'

Thanks.

mirtheil · #10 (**permalink**) 08-08-07, 08:23

The following works for me:

Code:

create table dateTest (f1 date, f2 char(10))#
insert into dateTest (f1, f2) values ('2004-3-4','2004-3-4')#
select * from dateTest where f1 = '2004-3-4'

Does it work for you? I ran the CREATE and INSERT through PCC and the SELECT through .NET (and PCC)
If not, what's the exact version of PSQL you are using?

1qaz2wsx7 · #11 (**permalink**) 08-09-07, 06:13

Hi

Its doing an error message, here is the error message its doing on
the CREATE command:

Quote:

One or more errors occurred during processing of command.
[LNA][Pervasive][ODBC Engine Interface]Syntax Error: create table dateTest (f1 date, f2 char(10))<< ??? >>#

Im using PSQL 8.

Thanks.

mirtheil · #12 (**permalink**) 08-09-07, 08:20

What tool are you using to issue the test statements? PCC should work. THe "#" is a statement separator. You might change it to ";" to see if that works.

WHat's the exact version of PSQL you are using? Specifically, what's the exact version of the W3ODBCEI.DLL on the server and the W3ODBCCI.DLL on the client?

1qaz2wsx7 · #13 (**permalink**) 08-12-07, 04:19

Hi

What do you mean what tool im using ? and what is it pcc ? :\

I tryed to change the # to ; and seems to work
but its doing another problem:

Quote:

One or more errors occurred during processing of command.
[LNA][Pervasive][ODBC Engine Interface]Table or view already exists.

there is no table named dateTest.
Here is the code

Quote:

OleDbDataAdapter adapter2 = new OleDbDataAdapter();
adapter2.SelectCommand = new OleDbCommand("create table dateTest (f1 date, f2 char(10));", con);
DataSet dataset2 = new DataSet();
adapter2.Fill(dataset2, "dateTest");

I dont know what is the exact version of the psql and the other things,
where can i see that ?

Thanks.

mirtheil · #14 (**permalink**) 08-12-07, 12:39

The PCC is the Pervasive Control Center. It is a tool included with PSQL that allows you to issue SQL statements without having to write or modify your application. It's a good diagnostic tool.
You should run the commands I sent in PCC, not your code.

For the version:

Quote:

Specifically, what's the exact version of the W3ODBCEI.DLL on the server and the W3ODBCCI.DLL on the client?

You can see the version of those DLLs in Windows Explorer by right clicking on the file and selecting "Properties".