Solved

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

Posted on 2009-05-06
13
1,282 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I comeā€¦
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now