conn.CreateDynaSet

[codehead]

Hello All:

Can I use a conn.CreateDynaSet to do an INSERT into an Oracle database, or should I be using a different type of connection?

Thanks!

 

[DotNetGnat]

dint quite understand your question. COuld you please elaborate a bit...

-DNG

 

[codehead]

Hi DNG,

I'm using conn.CreateDynaSet to get my result set from my Oracle database. When I use it to try to do an INSERT into my database, I get a "comment missing" error, which makes no sense (could be a syntax/typo though). If I use a conn.execute to do the INSERT, I get a "Object doesn't support this property or method" error message.

Thanks.

 

[DotNetGnat]

try this:

conn.ExecuteSQL yoursql

-DNG

 

[codehead]

No, that just gives me an "80" error message in my Web log.

Just to clarify, in my message above, I should have said the error message said "comma missing" not "comment missing".

Also, I'm using CreateObject("OracleInProcServer.XOraSession") to connect to my Oracle database.

Thanks.

 

[DotNetGnat]

did you try this:

conn.CreateDynaSet(yoursql,0)

-DNG

 

[codehead]

Actually, with conn.CreateDynaSet you have to use "Cint(0)" for some reason. Here is a simplified version of my code:

set insStmt = conn.CreateDynaSet("INSERT INTO TBL_REQUIREMENTS (POINT_OF_CONTACT, FAX) VALUES ('"&request("POC")&"','"&request("FAX")&"'",Cint(0))

This produces the Web log error message of:

"ORA-00917:_missing_comma"

Thanks.

 

[DotNetGnat]

may be there are some apostrophe's in the request variables...

try using replace() function to replace single quote with double quotes...

try this:

set insStmt = conn.CreateDynaSet("INSERT INTO TBL_REQUIREMENTS (POINT_OF_CONTACT, FAX) VALUES ('"&Replace(request("POC"),"'","''")&"','"&replace(request("FAX"),"'","''")&"'",Cint(0))

-DNG

 

[DotNetGnat]

also i would suggest you to hardcode some values and test your sql statement...response.write is also not a bad idea...

-DNG

 

[codehead]

I finally figured it out. It was just a syntax error. And your conn.ExecuteSQL does work (but no "set" statement is needed nor is the "Cint(0)").

Thanks again, DNG!

 

[DotNetGnat]

no problem...glad to be of help...

-DNG

 

[codehead]

Here is a good link for anyone else trying to use this functionality:

http://www.orafaq.com/faqoo4o.htm#DDL

 

[DotNetGnat]

nice link...thanks

-DNG