Morne
|
| Posted: 05/31/2005, 1:51 AM |
|
I am trying to select records from one table and insert them into another table. The first SQLa works (302 records), but if I get to the resulting table, I don't see anything that has gone through.
I tried the insert statement with default values, and it works, but within the recordset it does not! Is my formatting wrong, or are the recordset values wrong?? have been trying for hours to fix this!!
Any help would be great
- win XP with ORA9i
SQLa = "SELECT * FROM I_PURCHASEORDERITEMS WHERE POLINEACTIVEFLAG = 1"
LN = "x25"
TD = Now()
Set TempShipxConn = New clsDBshopora
'Open the connection
TempShipxConn.Open
' Open the recordset
Set TempRecordSet = TempShipxConn.Execute (SQLa) 'DBConnection1.Execute(SQL)
' If TempShipxConn.Errors.Count = 0 Then
While NOT TempRecordSet.EOF
PN = CCGetValue(TempRecordSet,"PURCHASEORDERNUMBER")
PI = CCGetValue(TempRecordSet,"PURCHASEORDERITEMNUM")
PQ = CCGetValue(TempRecordSet,"ORDEREDCASEQUANTITY")
PP = CCGetValue(TempRecordSet,"ORDEREDCASEUOM")
PU = CCGetValue(TempRecordSet,"UNITQUANTITY")
PS = CCGetValue(TempRecordSet,"UNITNOTSHIPPED")
PA = CCGetValue(TempRecordSet,"POLINEACTIVEFLAG")
UC = CCGetUserID()
SQLx = "INSERT INTO TEMP_SHIPS (LOADNUMBER, PONUM, POITEM, POCASEQTY, POCASEUOM, POUNITS, PONOTSHIPPED, POLINEACTIVE, USERCODE, TDATE) " &_
"VALUES ("& CCtoSQL(LN,ccsText) & ","& CCtoSQL(PN,ccsText) & ","& CCtoSQL(PI,ccsText) & ","& CCtoSQL(PQ,ccsFloat) & "," & CCtoSQL(PP,ccsText) &_
"," & CCtoSQL(PU,ccsFloat) & ","& CCtoSQL(PS,ccsFloat) & ","& CCtoSQL(PA,ccsInteger) & ","& CCtoSQL(UC,ccsInteger) & ","& CCtoSQL(TD,ccsDate) & ")"
' ccsDate, ccsBoolean, ccsInteger, ccsFloat, ccsText, ccsMemo
TempShipxConn.Execute (SQLx)
TempRecordSet.MoveNext
Wend
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 05/31/2005, 2:01 AM |
|
Maybe add somewhere:
Response.Write SQLx
Response.End
Then copy the SQL that will be displayed and paste it into your database to see if can be executed at all.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
Morne
|
| Posted: 05/31/2005, 2:41 AM |
|
ORA-00984: column not allowed here
error I get once the connection tries to execute
|
|
|
|
Nicole
Posts: 586
|
| Posted: 05/31/2005, 6:01 AM |
|
Here is what Orcale say regarding this error
ORA-00984: column not allowed here
Cause: A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement.
Action: Check the syntax of the statement and use column names only where appropriate.
http://ora-00984.ora-code.com/
_________________
Regards,
Nicole |
|
|
|
Oper
Posts: 1195
|
| Posted: 05/31/2005, 5:52 PM |
|
what peterr said:
put that code before executing Execute Statment
Response.Write SQLx
Response.End
so you could analize the SQL created, somethign shoudl be wrong in teh SQL
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
willibob
|
| Posted: 06/14/2005, 2:37 AM |
|
This is maybe a bit late but here's my twopenneth worth.
Firstly I develop using VBScript and SQL Server so I'm not sure if this is going to be applicable...
When creating SQL statements (for SQL Server anyway) , string values have to be surrounded by single quotes otherwise the database assumes that you are referencing a field/column so I would suspect that one of the CCtoSQL functions is returning a string value without the single quotes.
Also could you not do a bulk insert using an append query such as:
SQLx = "INSERT INTO TEMP_SHIPS (LOADNUMBER, PONUM, POITEM, POCASEQTY,
POCASEUOM, POUNITS, PONOTSHIPPED, POLINEACTIVE, USERCODE, TDATE) SELECT 'x25',
PURCHASEORDERNUMBER, PURCHASEORDERITEMNUM, ORDEREDCASEQUANTITY,
ORDEREDCASEUOM, UNITQUANTITY, UNITNOTSHIPPED, POLINEACTIVEFLAG, " & CCGetUserID() & ","
& Now() & " FROM I_PURCHASEORDERITEMS WHERE POLINEACTIVEFLAG = 1"
Hope you got it resolved.
Bill
|
|
|
|
|
