I have a web application which is running both in a development server and a staging server (hosted by someone else). I'm experiencing very sporadic problems with inserting new rows into all the tables on the staging server. Apparently, nothing has been changed at all on both servers, and I have not experience anything like what's happening on the staging server. I include the code segment (with some output for troubleshooting) below:
on error resume next
dim db, query, rs
set db = getConnection()
query = "SELECT MY_SEQ.nextval AS nextid FROM dual"
set rs = db.CreateDynaset(query, 0)
if Err.Number > 0 then
Response.write "2. " & Err.Description & "<br>"
end if
dim nextId : nextId = cint(rs.fields("nextid").value)
if Err.Number > 0 then
Response.write "3. " & query & "<br>"
Response.write "4. " & Err.Description & "<br>"
end if
query = "SELECT * FROM MY_TABLE"
set rs = db.dbCreateDynaset(query, 0)
if Err.Number > 0 then
Response.write "5. " & query & "<br>"
Response.write "6. " & Err.Description & "<br>"
end if
rs.addNew
rs.fields("user_id").value = nextId
...
rs.update
if Err.Number > 0 then
Response.write "7. " & Err.Description & "<br>"
end if
...
if Err.Number > 0 then
Response.end
end if
And this is what I got:
3. SELECT MY_SEQ.nextval AS nextid FROM dual
4. Object required
5. SELECT * FROM "MY_TABLE"
6. Object required
7. SQL execution error, ORA-01400: cannot insert NULL into ("MY_DB"."MY_TABLE"."USER_ID")
BTW, I'm using Oracle OO4O with database pool on IIS 5.0 on the staging server. Hope it helps.
This is what I have in the global.asa file:
<object runat="server" scope="application" id="OraSession" progid="OracleInProcServer.XOraSession"></object>
<script language="vbscript" runat="server">
sub application_onStart
oraSession.createDatabasePool 1, 40, 200, "database", "username/password", 0
end sub
</script>
And this is what I have in my getConnection function:
function getConnection
dim dbName : dbName = "database"
dim dbCredentials : dbCredentials = "username/password"
dim db : set db = oraSession.getDatabaseFromPool(0)
set getConnection = db
end function
The thing is, this used to work, but now it doesn't, although once in a while I do get to get it working correctly. So everything is extremely sporadic and unstable.
I have a corresponding edit page, which does a UPDATE instead of AddNew, and that doesn't give me any problems, which leads me to believe it has to do with getting the ID from SEQUENCE.
I am pulling my hair out now, and have no idea how to troubleshoot this further or what I might have overlooked. Would appreciate if any experts can help me out.
Thanks!
