Solved

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

Posted on 2009-05-06
13
1,354 Views
Last Modified: 2013-12-18
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

0
Comment
Question by:mrong
  • 7
  • 6
13 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24313810
How is your transaction being commited?
0
 

Author Comment

by:mrong
ID: 24313853
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24313966
Try adding the following before the objConn.close statement:

objConn.CommitTrans
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mrong
ID: 24314083
Once I added that line, I got error "No transaction is active" while I tried to run the script.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24314131
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
 

Author Comment

by:mrong
ID: 24314252
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24314440
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
 

Author Comment

by:mrong
ID: 24314577
I tried your suggestion adding error handling code. but gave me syntax error
at line 1->On Error GoTo Err_Handler
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24314763
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
 

Author Comment

by:mrong
ID: 24314833
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
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 125 total points
ID: 24314927
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
 

Author Comment

by:mrong
ID: 24316758
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24316778
Your welcome, I'm pleased I could help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question