Solved

VB: runtime error 3705 (Urgent)

Posted on 2004-04-07
14
539 Views
Last Modified: 2013-12-25
I am getting this error:
runtime error 3705: operation is not allowed when the object is open


at this pointin my prog:
rs.Open "insert into [transaction] values '" & List31.Text & "', '" & Adodc1.Recordset.Fields(0) & "'", cn

before this, rs.open is executed in some other event as:
rs.Open "select [Issue Date],[Return Date] from [transaction] where [Roll no]='" & List31.Text & "'", cn, adOpenDynamic, adLockBatchOptimistic

I need this answer urgently. if any other detail is required, please tell
0
Comment
Question by:hag1
  • 8
  • 4
  • 2
14 Comments
 
LVL 6

Accepted Solution

by:
mmusante earned 150 total points
ID: 10775033
You cannot open a recordset if it is already open ... close if before re-opening
0
 
LVL 6

Assisted Solution

by:mmusante
mmusante earned 150 total points
ID: 10775050
try this ...

if rs.state <> adStateClosed then rs.close
rs.Open "insert into [transaction] values '" & List31.Text & "', '" & Adodc1.Recordset.Fields(0) & "'", cn
0
 

Author Comment

by:hag1
ID: 10775080
ok, solved.
I will keep the thread open in case i have to ask another silly question.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10775100
try this:

if rs.State = 1 then
    rs.close
end if
rs.Open "insert into [transaction] values '" & List31.Text & "', '" & Adodc1.Recordset.Fields(0) & "'", cn

and in the other location:

if rs.state = 1 then
    rs.close
end if
rs.Open "select [Issue Date],[Return Date] from [transaction] where [Roll no]='" & List31.Text & "'", cn, adOpenDynamic, adLockBatchOptimistic


That will check if the Recordset is already open, and if so, will close it before opening it again.

AW
0
 

Author Comment

by:hag1
ID: 10775614
When I use :
adodc4.recordsource " select [copies present] from books where[book id]='" & Adodc1.Recordset.Fields(0) & "' "

i get the error:
Invalid use of property and .fields is selected in the above statement

I read the corresponding help, but could not figure it out.

What mistake am I making?
0
 
LVL 6

Assisted Solution

by:mmusante
mmusante earned 150 total points
ID: 10775636
May be
adodc4.recordsource = " select [copies present] from books where[book id]='" & Adodc1.Recordset.Fields(0) & "' "
0
 

Author Comment

by:hag1
ID: 10775704
That was indeed the error.

gr8 for pointing it out.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:hag1
ID: 10780464
This code was working without any problem

With Adodc1
.RecordSource = "select * from books where id='" & Adodc1.Recordset.Fields(0) & "'"

.Recordset.Fields(8).Value = .Recordset.Fields(8).Value + 1
.Refresh
.RecordSource = "select * from books where [copies present]>0"
.Refresh
End With


Now i deleted a column of books table thru enterprise manager.
therefore, in the above code i changed fields(8) to fields(7) and now it doesnt work
ie: It does not show any error, but no change is made in the field value
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10780503
which field did you delete form the Table, before or after what WAS field 8 before you deleted the field?

Can you list ALL of the fields in the original table, and ALL of the fields in the current table?

AW
0
 

Author Comment

by:hag1
ID: 10780533
old table:
id,title,author,publication,subject,year,cost,total copies, copies present

current table:
id,title,author,publication,subject,cost,total copies, copies present

All the columns above are in order
0
 

Author Comment

by:hag1
ID: 10780651
I was using the debugger.
for ;
.Recordset.Fields(8).Value = .Recordset.Fields(8).Value + 1
The lhs value of recordset.fields(8).value gets modified as required
When .refresh is executed, the above modification is undone
0
 

Author Comment

by:hag1
ID: 10780783
I think u must ignore the comment i made that it was working right previously. i think that the problem was there from the beginning, so it has mothing to do with deleting a column.
0
 
LVL 6

Expert Comment

by:mmusante
ID: 10781265
Can't you refer to the columns by name?

.Recordset.Fields("copies present").Value

or

.Recordset.Fields("[copies present]").Value

if spaces in column names give you any problem change your query to this:

SELECT id,title,author,publication,subject,cost,[total copies] AS total_copies, [copies present] AS copies_present FROM Books ...
0
 

Author Comment

by:hag1
ID: 10783814
After the select query, I added adodc4.recordset.update which solved the problem.

I am grateful to u people at EE for helping when I needed it the most. I have now learnt quite a few things in VB.

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

706 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

17 Experts available now in Live!

Get 1:1 Help Now