ADODB.recordset

[kaczak1108]

Hi everyone, i have a small problem in my code.
when i execute my code i get a error msg: " 3705" operation can,t execute when object is open

Sub delete_dane()

Dim conn
Dim str As String

GetDbConnection conn
Set rs = CreateObject("ADODB.recordset")
cn.CursorLocation = adUseClient
rs.Open "select table_hgbst_elm.objid, table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.objid join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where table_hgbst_show.title in ('Level 1') and hgbst_lst2hgbst_show=536871644", conn
str = rs.GetString(, , "", "", "")
MsgBox (str)
Set rs.ActiveConnection = Nothing
rs.Close
conn.Close
If str <> "" Then ''zmienić numer z tabeli lst hgbst_lst2hgbst_show na x_uls_order_title

cn.CursorLocation = adUseClient
Set rs = CreateObject("ADODB.recordset")
rs.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 5'))", conn
Set rs.ActiveConnection = Nothing
rs.Close
conn.Close
rs.Open "delete from table_hgbst_show where title in('Level 5') and objid in (select distinct table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 5'))", conn
rs.Close
rs.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 4'))", conn
rs.Close
rs.Open "delete from table_hgbst_show where title in('Level 4') and objid in (select distinct table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 4'))", conn
rs.Close
rs.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 3'))", conn
rs.Close
rs.Open "delete from table_hgbst_show where title in('Level 3') and objid in (select distinct table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 3'))", conn
rs.Close
rs.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 2'))", conn
rs.Close
rs.Open "delete from table_hgbst_show where title in('Level 2') and objid in (select distinct table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 2'))", conn
rs.Close
rs.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 1'))", conn
rs.Close
rs.Open "delete from table_hgbst_show where title in('Level 1') and objid in (select table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.objid join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where table_hgbst_show.title in ('Level 1') and hgbst_lst2hgbst_show=1342180973)", conn
rs.Close
rs.Open "delete from mtm_hgbst_elm0_hgbst_show1 where hgbst_elm2hgbst_show in(select hgbst_show2hgbst_elm from mtm_hgbst_elm0_hgbst_show1 mtm where not exists (select * from table_hgbst_show sh where sh.objid=mtm.hgbst_show2hgbst_elm and sh.objid=1342180973))", conn
rs.Close
rs.Open "delete from mtm_hgbst_elm0_hgbst_show1 where hgbst_elm2hgbst_show in(select hgbst_show2hgbst_elm from mtm_hgbst_elm0_hgbst_show1 mtm where not exists (select * from table_hgbst_show sh where sh.objid=mtm.hgbst_show2hgbst_elm and sh.chld_prnt2hgbst_show=1342180973))", conn
End If
End Sub

can anyone help me?

[TysonLPrice]

Which line is causing the error?

[kaczak1108]

i solve this: now looks like :

Sub delete_dane()

Dim conn
Dim str As String

GetDbConnection conn
Set rs = CreateObject("ADODB.recordset")
rs.Open "select table_hgbst_elm.objid, table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.objid join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where table_hgbst_show.title in ('Level 1') and hgbst_lst2hgbst_show=536871644", conn

str = rs.GetString(, , "", "", "")
MsgBox (str)
rs.Close
conn.Close
If str <> "" Then ''zmienić numer z tabeli lst hgbst_lst2hgbst_show na x_uls_order_title
conn.Open
Set rs1 = CreateObject("ADODB.recordset")
rs1.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 5'))", conn
Set rs2 = CreateObject("ADODB.recordset")

rs2.Open "delete from table_hgbst_show where title in('Level 5') and objid in (select distinct table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 5'))", conn

Set rs3 = CreateObject("ADODB.recordset")
rs3.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 4'))", conn

Set rs4 = CreateObject("ADODB.recordset")

rs4.Open "delete from table_hgbst_show where title in('Level 4') and objid in (select distinct table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 4'))", conn

Set rs5 = CreateObject("ADODB.recordset")
rs5.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 3'))", conn

Set rs6 = CreateObject("ADODB.recordset")
rs6.Open "delete from table_hgbst_show where title in('Level 3') and objid in (select distinct table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 3'))", conn

Set rs7 = CreateObject("ADODB.recordset")
rs7.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 2'))", conn

Set rs8 = CreateObject("ADODB.recordset")
rs8.Open "delete from table_hgbst_show where title in('Level 2') and objid in (select distinct table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 2'))", conn

Set rs9 = CreateObject("ADODB.recordset")
rs9.Open "delete from table_hgbst_elm where title is not null and objid in (select table_hgbst_elm.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.chld_prnt2hgbst_show join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where  hgbst_lst2hgbst_show=1342180973 and table_hgbst_show.title in ('Level 1'))", conn
Set rs10 = CreateObject("ADODB.recordset")
rs10.Open "delete from table_hgbst_show where title in('Level 1') and objid in (select table_hgbst_show.objid from table_hgbst_show Join mtm_hgbst_elm0_hgbst_show1 on table_hgbst_show.objid = mtm_hgbst_elm0_hgbst_show1.hgbst_show2hgbst_elm join table_hgbst_lst on table_hgbst_lst.hgbst_lst2hgbst_show=table_hgbst_show.objid join table_hgbst_elm on mtm_hgbst_elm0_hgbst_show1.hgbst_elm2hgbst_show=table_hgbst_elm.objid where table_hgbst_show.title in ('Level 1') and hgbst_lst2hgbst_show=1342180973)", conn
Set rs11 = CreateObject("ADODB.recordset")
rs11.Open "delete from mtm_hgbst_elm0_hgbst_show1 where hgbst_elm2hgbst_show in(select hgbst_show2hgbst_elm from mtm_hgbst_elm0_hgbst_show1 mtm where not exists (select * from table_hgbst_show sh where sh.objid=mtm.hgbst_show2hgbst_elm and sh.objid=1342180973))", conn
Set rs12 = CreateObject("ADODB.recordset")
rs12.Open "delete from mtm_hgbst_elm0_hgbst_show1 where hgbst_elm2hgbst_show in(select hgbst_show2hgbst_elm from mtm_hgbst_elm0_hgbst_show1 mtm where not exists (select * from table_hgbst_show sh where sh.objid=mtm.hgbst_show2hgbst_elm and sh.chld_prnt2hgbst_show=1342180973))", conn
End If
End Sub

[kaczak1108]

but i have another problem:

 Public Function inserthgbstelement(title As String, status As String, rank As Integer) As String
    
    Dim conn
    Dim funObjid_elm
    GetDbConnection conn
    Set rs_elm = CreateObject("ADODB.recordset")
    funObjid_elm = pobierz_objid(ptype_id_inserthgbstelement)
    
    rs.Open "insert into table_hgbst_elm(OBJID, TITLE, S_TITLE, RANK, STATE, DEV, INTVAL) values (" + funObjid_elm + ", '" + title + "', Upper('" + title + "'), " + rank + ", '" + status + "', "",0)", conn
    inserthgbstelement = funObjid_elm
End Function

show me error: type mismatch

[AndyLD]

It would help to know the full error message and the setup you are working on. Is it VB express, which version? Probably the error message tells you more than you tell us.

[Shaggy Hiker]

Actually, for a Type Mismatch, the error may not tell you anything useful at all. SQL errors often tend to be a bit opaque. However, we probably can't help much with it, either. The error tells you all it can, which is that you are supplying something that doesn't fit into the type in the DB field. The only thing you can do is examine what you are passing in and what the DB fields are.