3 Replies - 242 Views - Last Post: 30 October 2017 - 01:51 AM Rate Topic: -----

#1 Shivani2890  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 29-October 17

Error when inserting rows: Missing VALUES keyword

Posted 29 October 2017 - 11:54 PM

Hi,

I am trying to insert rows through a code in a table using run time input for two columns, and selecting all the other values from the same table.
However, VB is giving me "ORA-00926: missing VALUES keyword", when I am actually using the Select statement within my insert statement.

Below is the code and query:

Set rs3 = New ADODB.Recordset
sSQL2 = "INSERT INTO test_territorymster SELECT" & Text2.Value & ", USEREXTERNALID, TEAMROLE, REPCODE, REPNAME, DATACOUNTRY, RECORDYEAR, DTIMPORT, TERRITORYCODEPSR, TERRITORYCODESM, MODULE, DTADD, ADDBY FROM test_territorymster WHERE DATACOUNTRY ='Malaysia' AND RECORDYEAR=to_char(sysdate,'YYYY') AND GACCODE = '" & Text3.Value & "'"
rs3.Open sSQL2, cn2, adOpenStatic, adLockOptimistic
rs3.Close

It throws "Missing VALUES keyword" error when I run this query through VB Code.

The same query works fine when run directly in database:

INSERT INTO test_territorymster
SELECT 'G721', (run time input in code from text2.value)
USEREXTERNALID,
TEAMROLE,
REPCODE,
REPNAME,
DATACOUNTRY,
RECORDYEAR,
DTIMPORT,
TERRITORYCODEPSR,
TERRITORYCODESM,
MODULE,
DTADD,
ADDBY
FROM test_territorymster
WHERE DATACOUNTRY = 'Malaysia'
AND RECORDYEAR = '2017'
AND GACCODE = '721'; (run time input in code from text3.value)

Can someone please help? It should not be expecting "VALUES" keyword here right??

Thanks!!

Is This A Good Question/Topic? 0
  • +

Replies To: Error when inserting rows: Missing VALUES keyword

#2 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6380
  • View blog
  • Posts: 25,775
  • Joined: 12-December 12

Re: Error when inserting rows: Missing VALUES keyword

Posted 30 October 2017 - 12:25 AM

The second version is not the same as the first. You should print out the value of sSQL2 to confirm this. There is no space after the word SELECT nor quotes around the first value.
Was This Post Helpful? 0
  • +
  • -

#3 Shivani2890  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 29-October 17

Re: Error when inserting rows: Missing VALUES keyword

Posted 30 October 2017 - 01:01 AM

Thanks for pointing this out, I have adjusted the space and quotes.
Still getting the same error. :(/>

The sql query I am running in the DB is the same except that I want to take column GACCode inputs from the code during runtime.

I removed the insert part, and ran only the select part using the code and printed out the result of sSQL2, the results are displayes as expected. But when I add the Insert part it starts expecting a VALUES keyword.

This post has been edited by andrewsw: 30 October 2017 - 01:52 AM
Reason for edit:: removed previous quote, use the REPLY button

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6380
  • View blog
  • Posts: 25,775
  • Joined: 12-December 12

Re: Error when inserting rows: Missing VALUES keyword

Posted 30 October 2017 - 01:51 AM

Again, print out the value of sSQL2. You can then compare it to the working version and, preferably, also attempt to execute the statement directly.

You should also use prepared statements to protect against SQL injection, and give your controls more meaningful names than text2, text3.



Also, values like the year are presumably numbers so you do not need to surround them with quotes.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1