ADODB.recordset

Printable View

May 10th, 2013, 04:22 AM
kaczak1108

ADODB.recordset

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

Code:

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?
May 10th, 2013, 04:57 AM
TysonLPrice

Re: ADODB.recordset

Which line is causing the error?
May 10th, 2013, 05:01 AM
kaczak1108

Re: ADODB.recordset

i solve this: now looks like :

Code:

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
May 10th, 2013, 05:03 AM
kaczak1108

Re: ADODB.recordset

but i have another problem:

Code:

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
May 10th, 2013, 10:54 AM
AndyLD

Re: ADODB.recordset

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.
May 10th, 2013, 01:48 PM
Shaggy Hiker

Re: ADODB.recordset

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.