Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Unanswered: ORA-00933: SQL command not properly ended

    I keep receiving this error on this select statement on my asp.net page,

    strSql2 = "SELECT ch.PAYEENAME AS name, ch.PAYEEADDR1 AS addr1, ch.PAYEEADDR2 AS addr2, ch.PAYEECITY AS city, ch.PAYEESTATE AS state, ch.COUNTRYCODE AS country, ch.CASENUM, ch.CLAPAYTRANNUM, 'Completed' AS status FROM CLAIMTRANH AS ch WHERE ch.patientref = '" & Me.txtAccountNo.Text & "' UNION SELECT ch.PAYEENAME AS name, ch.PAYEEADDR1 AS addr1, ch.PAYEEADDR2 AS addr2, ch.PAYEECITY AS city, ch.PAYEESTATE AS state, ch.COUNTRYCODE AS country, ch.CASENUM, ch.CLAPAYTRANNUM, 'In Process' AS Status FROM CLAPAYTRANH AS ch WHERE ch.patientref = '15706CLAIM'"

    I have tried putting in a semicolon at the end of the statement removing the Union and just having one select statement and still receive this error. Can anyone help me with this problem.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What error? and why use a union, use

    strSql2 = "SELECT ch.PAYEENAME AS name, ch.PAYEEADDR1 AS addr1, ch.PAYEEADDR2 AS addr2, ch.PAYEECITY AS city, ch.PAYEESTATE AS state, ch.COUNTRYCODE AS country, ch.CASENUM, ch.CLAPAYTRANNUM, decode(ch.parientref,'15706CLAIM','In Process','Completed') AS status FROM CLAIMTRANH AS ch WHERE ch.patientref in ('" & Me.txtAccountNo.Text & "','15706CLAIM')"
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2008
    Posts
    6
    I still receive the same error. The error is ORA-00933: SQL command not properly ended.

    I paste the wrong select statement it should be this:
    strSql2 = "SELECT ch.PAYEENAME AS name, ch.PAYEEADDR1 AS addr1, ch.PAYEEADDR2 AS addr2, ch.PAYEECITY AS city, ch.PAYEESTATE AS state, ch.COUNTRYCODE AS country, ch.CASENUM, ch.CLAPAYTRANNUM, 'Completed' AS status FROM CLAIMTRANH AS ch WHERE ch.patientref = '" & Me.txtAccountNo.Text & "' UNION all SELECT ch.PAYEENAME AS name, ch.PAYEEADDR1 AS addr1, ch.PAYEEADDR2 AS addr2, ch.PAYEECITY AS city, ch.PAYEESTATE AS state, ch.COUNTRYCODE AS country, ch.CASENUM, ch.CLAPAYTRANNUM, 'In Process' AS Status FROM CLAPAYTRANH AS ch WHERE ch.patientref = '" & Me.txtAccountNo.Text & "'"

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why don't you print the final SQL statement just before executing it? I'm sure it will be better for figuring out what may be wrong with it.

    WHERE ch.patientref = '" & Me.txtAccountNo.Text & "'"
    Have you heard about SQL injection?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2008
    Posts
    6
    Quote Originally Posted by n_i
    Why don't you print the final SQL statement just before executing it? I'm sure it will be better for figuring out what may be wrong with it.



    Have you heard about SQL injection?
    No I haven't.

    I have another Oracle select statement where I get the same 'SQL command not properly ended'

    SELECT DISTINCT CLAIMTRAN.CASENUM, CLAIMTRAN.TOSERVICEDATE, CLAIMTRAN.SUBMITTEDAMOUNT, CLAIMTRAN.CHECKDATE, CLAIMTRAN.CHECKNUM FROM CLAIMTRAN INNER JOIN CLAIMTRANH ON CLAIMTRAN.CASENUM = CLAIMTRANH.CASENUM WHERE CLAIMTRAN.TOSERVICEDATE = '09/03/2008' AND CLAIMTRANH.PATIENTREF = '15706CLAIM'

    What am I doing wrong here?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The problem could be single quotes in your variable Me.txtAccountNo.Text.

Posting Permissions

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