Urgent: Can't update record(Oracle 10g) from VBScript

I have the following vbscript which update the oracle 10g table, but it doesn't update the data.
Any ideas? Thanks.
***ROW_ID is a number field



Dim objConn
Dim connstr
Set objConn = CreateObject("ADODB.Connection")
set rs = createobject("adodb.recordset")
connstr="DSN=xxx;SRVR=yyy;UID=zzz;PWD=password"
objConn.Open connstr
 
query = "select * from tbl1 where send is null;"
rs.open query, objConn
do until rs.eof
 
  query1 = "update tbl1 set send = 'Y' where ROW_ID ="& rs("ROW_ID")
  objConn.execute query1
  rs.movenext
  loop
  rs.close
objConn.close

Open in new window

mrongAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MilleniumaireConnect With a Mentor Commented:
Nice one.  I'm not sure if the semi colon at the end of the update statement was part of the problem (I don't have them in my code).  If so, I notice your select statement also has a semi colon, so make sure you leave that off also.

Am I right in saying that the select and update statements are simple versions of what will eventually be used?  If you are planning to use simple statements like this you could always use a single update statement such as:

update tbl1 set send = 'Y'
where send is null
0
 
MilleniumaireCommented:
How is your transaction being commited?
0
 
mrongAuthor Commented:
I thought it will be commited itself. If not, could you let me know the code I need to add to make it commit?
thanks.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MilleniumaireCommented:
Try adding the following before the objConn.close statement:

objConn.CommitTrans
0
 
mrongAuthor Commented:
Once I added that line, I got error "No transaction is active" while I tried to run the script.
0
 
MilleniumaireCommented:
Ah, just checked my code and I have a BeginTrans at the start of the script.  Try adding this to the script after objConn.Open:

objConn.BeginTrans

Just another thought - are you sure the update statement WILL update rows?  Have you tried running the update statement outside of the script?
0
 
mrongAuthor Commented:
I tried that update statement in SQL Plus and works fine.

Below is what I have for now, but still doesn't update the record.

Dim objConn
Dim connstr


Set objConn = CreateObject("ADODB.Connection")
 
set rs = createobject("adodb.recordset")

connstr="DSN=xxx;SRVR=yyy;UID=zzz;PWD=password"
objConn.Open connstr
objConn.BeginTrans
 
query = "select * from tbl1 where send is null;"
rs.open query, objConn
do until rs.eof
 
  query1 = "update tbl1 set send = 'Y' where ROW_ID ='2';"
  objConn.execute query1

  rs.movenext
  loop
  rs.close
objConn.CommitTrans
objConn.close

0
 
MilleniumaireCommented:
Try removing the semicolon from the end of the update statement.  You could also try adding some error handling code:

On Error GoTo Err_Handler

Dim objConn
Dim connstr


Set objConn = CreateObject("ADODB.Connection")
 
set rs = createobject("adodb.recordset")

connstr="DSN=xxx;SRVR=yyy;UID=zzz;PWD=password"
objConn.Open connstr
objConn.BeginTrans
 
query = "select * from tbl1 where send is null;"
rs.open query, objConn
do until rs.eof
 
  query1 = "update tbl1 set send = 'Y' where ROW_ID ='2';"
  objConn.execute query1

  rs.movenext
  loop
  rs.close
objConn.CommitTrans
objConn.close

Err_Handler:
  Err.Raise Err, Err.Description, Err.HelpFile, Err.HelpContext
0
 
mrongAuthor Commented:
I tried your suggestion adding error handling code. but gave me syntax error
at line 1->On Error GoTo Err_Handler
0
 
MilleniumaireCommented:
Not sure why this is giving a problem, although my code is encapsulated within Subs and Functions.

Did you try removing the semicolon from the end of the update statement?
0
 
mrongAuthor Commented:
I simplified the code to the following and it updated the record. I am adding the select query in the code and make the update stmt update the particular record.

Dim objConn
Dim connstr
Set objConn = CreateObject("ADODB.Connection")
 
set rs = createobject("adodb.recordset")
connstr="DSN=xxx;SRVR=yyy;UID=zzz;PWD=password"

objConn.Open connstr
objConn.BeginTrans
query1 = "update tbl1 set send = 'Y' where ROW_ID =2"

  objConn.execute query1
objConn.CommitTrans
objConn.close
0
 
mrongAuthor Commented:
Milleniumaire:

I believe it is the semi colon caused the problem. My code is working now. Thank you very much for your help. I am openning another question

0
 
MilleniumaireCommented:
Your welcome, I'm pleased I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.