Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2007
    Posts
    13

    Unanswered: What is the problem with this SQL command ?

    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:
    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.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Jul 2007
    Posts
    13
    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.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Jul 2007
    Posts
    13
    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.

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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();
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Jul 2007
    Posts
    13
    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.


  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    The code I posted works for me against a PSQL database. It should work. If it's not, post the exact error.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  9. #9
    Join Date
    Jul 2007
    Posts
    13
    Here is the error message:

    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.

  10. #10
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  11. #11
    Join Date
    Jul 2007
    Posts
    13
    Hi

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

    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.

  12. #12
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  13. #13
    Join Date
    Jul 2007
    Posts
    13
    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:

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

  14. #14
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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:
    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".
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  15. #15
    Join Date
    Jul 2007
    Posts
    13
    Hi

    How do i run those command in PCC ?
    Where do i write them ?

    The versions:
    W3ODBCEI.DLL = 8.10.117.17
    W3ODBCCI.DLL = 8.10.117.17

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •