locked
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. RRS feed

  • Question

  • User-1626039111 posted

    Hi,

    While running sql query in code behind file(using sqlcommand or oledbcommand) 

    sample query
    strQuery = "if NOT EXISTS (Select * from EMP where LName='reddy')"
    
            strQuery += " BEGIN INSERT into EMP(LName) "
    
            strQuery += " VALUES ('naveen') End GO "

     i am getting the error as

    Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT',or 'UPDATE'

    Please don't suggest ( use the procedures)

    is it possible to run  the above sql statement in code bind file, ( the business requirement is not using the procedures)

    Thanks in advance

     

    Tuesday, February 15, 2011 7:21 AM

Answers

  • User-1199946673 posted

    IF NOT EXISTS is not supported in Access. However, there is a trick. You need to write an SQL statement with a where clause

     

    strQuery = "INSERT INTO EMP (LName) SELECT TOP 1 'naveen' FROM EMP"

    This will insert 1 record into the table EMP

    To insert only when the record doesn't exist, add

     

    strQuery += " WHERE (SELECT Count(*) FROM EMP LName='reddy') = 0"
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 16, 2011 4:11 AM

All replies

  • User1872574491 posted

    Hi,
    please try removing begin & end go.
    and try again.

    Hi,

    please try removing begin & end go.

    and try again.

    Tuesday, February 15, 2011 7:47 AM
  • User-1626039111 posted

    Thanks for giving quick response.

    i tried with all the way's with and with out begin, end ,go , no use Frown

    Tuesday, February 15, 2011 8:04 AM
  • User1872574491 posted

    Try to remove only go statemant, and try again.

    Its working in sql query window.

    Tuesday, February 15, 2011 11:41 PM
  • User-1626039111 posted

    Hi Hussain/Tech Professionals,

    The Problem is , The above mentioned query has been working in query analiser. while using IF NOT EXISTS in CodeBehind file i am getting the problem, if you begin with  DML command's its working fine in Codebehind file. any body can help, on this issue.

    Wednesday, February 16, 2011 1:52 AM
  • User1872574491 posted

    Hi naveen,

    it is working from code-behind as well.

    You just need to remove the go statement.

    i have cross-checked it.

    Wednesday, February 16, 2011 2:08 AM
  • User-1199946673 posted

    IF NOT EXISTS is not supported in Access. However, there is a trick. You need to write an SQL statement with a where clause

     

    strQuery = "INSERT INTO EMP (LName) SELECT TOP 1 'naveen' FROM EMP"

    This will insert 1 record into the table EMP

    To insert only when the record doesn't exist, add

     

    strQuery += " WHERE (SELECT Count(*) FROM EMP LName='reddy') = 0"
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 16, 2011 4:11 AM
  • User-1626039111 posted

    thanks lot, query is working fine

    Wednesday, February 16, 2011 7:03 AM