Solved

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

Posted on 2009-05-06
13
1,364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Password_rules_securitty.. 12 46
Moving Oracle Database to other server 2 45
Get the Y value of a line at X in a chart in VBA 28 71
oracle query 4 27
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.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
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…

739 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