CODEWITHSUNDEEP
sqlasp.netoracle-databaseoracle10g
HEEN
HEEN

Reputation: 4727

Error with the query in Oracle

I am using Oracle and using in my toad to check what the result is. But I am getting error as

ORA-01740: missing double quote in identifier

Here is my query

SELECT T1.Project_Id, 
       PROPERTY_NAME Project_name,
       T1.Vehicle_No,
       T1.Creation_date,
       T1.Time_In,
       T1.Time_Out
FROM 
       XXCUS.XX_SUPINV T1
   INNER JOIN XXACL_PN_PROJBUILD_V T2 
       ON T1.Project_Id = T2.Project_id
WHERE  t1.Project_Id = '" + ddlProjectName.SelectedValue + "' 
   AND Creation_date BETWEEN to_date fnd_conc_date.string_to_date('"TxtIndate.Text"') AND 
         to_date fnd_conc_date.string_to_date('"txtOutDate.Text"')"

Please suggest where I am mistaking

Upvotes: 3

Views: 12699

Answers (3)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

Seems that you have additional " in the end of query:

to_date fnd_conc_date.string_to_date('"txtOutDate.Text"')" -- here

should be like:

to_date fnd_conc_date.string_to_date('"txtOutDate.Text"')

An initial double quote (") was found without a closing quote. If an identifier contains a blank or special characters other than $, #, or _, it must be enclosed in double quotes.

Documentation

Upvotes: 1

Abecee
Abecee

Reputation: 2393

Turning a comment into an answer...

You might want to verify first, the data is actually flowing from the database to your front end. For this, verify a simplified query to return data in, e.g., TOAD. Then just copy the query string to your application code, and send it from there to the database. Try something along the lines of:

SELECT
  T1.Project_Id,
  PROPERTY_NAME Project_name,
  T1.Vehicle_No,
  T1.Creation_date,
  T1.Time_In,
  T1.Time_Out
FROM XXCUS.XX_SUPINV T1
INNER JOIN XXACL_PN_PROJBUILD_V T2
  ON T1.Project_Id = T2.Project_id
WHERE t1.Project_Id = 409
  AND Creation_date BETWEEN TO_DATE('01-jan-2015', 'DD-MON-YYYY')
                    AND TO_DATE('01-jan-2012', 'DD-MON-YYYY')

Please comment, if and as adjustment / further detail is required.

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

This is the actual Query (with data) you're trying to execute.

SELECT T1.Project_Id, 
       PROPERTY_NAME Project_name,
       T1.Vehicle_No,
       T1.Creation_date,
       T1.Time_In,
       T1.Time_Out
FROM 
       XXCUS.XX_SUPINV T1
   INNER JOIN XXACL_PN_PROJBUILD_V T2 
       ON T1.Project_Id = T2.Project_id
WHERE  t1.Project_Id = '409' 
   AND Creation_date BETWEEN to_date('01-jan-2015','DD-mon-yyyy') AND 
         to_date('01-jan-2012','DD-mon-yyyy')

You're programmatic version could be ( Just derived from your base version)

 sl =  "SELECT T1.Project_Id, 
           PROPERTY_NAME Project_name,
           T1.Vehicle_No,
           T1.Creation_date,
           T1.Time_In,
           T1.Time_Out
    FROM 
           XXCUS.XX_SUPINV T1
       INNER JOIN XXACL_PN_PROJBUILD_V T2 
           ON T1.Project_Id = T2.Project_id
    WHERE  t1.Project_Id = '" + ddlProjectName.SelectedValue + "' 
       AND Creation_date BETWEEN fnd_conc_date.string_to_date('" + TxtIndate.Text+ "') AND 
             fnd_conc_date.string_to_date('"+ txtOutDate.Text +"')"

To improve readability and avoid SQL*Injection,you should try using bind variables( I am not so thorough with the .NET syntax)

Upvotes: 1

Related Questions