• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1480
  • Last Modified:

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

0
mrong
Asked:
mrong
  • 7
  • 6
1 Solution
 
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
 
MilleniumaireCommented:
Try adding the following before the objConn.close statement:

objConn.CommitTrans
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
MilleniumaireCommented:
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now