[RESOLVED] how to add data on multiple recordsets at once on vb6?

[apnomid]

gud day every one,just asking for some advice regarding on my project, as I mention on the title, i need to add data to multiple recordsets with just one click, the problem is don't know how to do it, whenever put recordset1.addnew under recordset2.addnew it seems that the recordset2.addnew is erasing the data that I stored on recordset1... heres my code below

Form load:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & App.Path & "\SchedDB.mdb"
receventname.CursorLocation = adUseClient

receventname.Open "Select * from EventInfo", conn, adOpenDynamic, adLockOptimistic

Set Me.eventaddname.DataSource = receventname
Me.eventaddname.DataField = "EventName"
Set Me.eventkeyy.DataSource = receventname
Me.eventkeyy.DataField = "EventKey"



receventname.AddNew

recpack.Open "Select * from Packages", conn, adOpenDynamic, adLockOptimistic

Set Me.eventaddname.DataSource = recpack
Me.eventaddname.DataField = "EventName"
Set Me.packname.DataSource = recpack
Me.packname.DataField = "PackageName"
Set Me.packprice.DataSource = recpack
Me.packprice.DataField = "PackagePrice"

recpack.AddNew

command save:
receventname.update
recpack.update

I tried putting .addnew along with .update but doesn't work

Can anyone help me?If possible make it more clear lol XD
thanks in advance....

[Spooman]

apn

I presume that by "one" click, you have a Sub Command1_Click() or something of that ilk.

If so, then within that sub it seems to me that you'd need to

1. open recordset1
   
   • save stuff to it
   • close it
2. open recordset2
   
   • save stuff to it (even if it is the same)
   • close it

HTH
Spoo

[dilettante]

Really tough to say because of the weird attempts at mixing data binding and procedural logic. We can't even tell what kinds of controls he is data binding to, let alone why.

It's a Tums Festival.

[techgnome]

He also calls .Addnew and then doesn't actually add anything, then calls .Update.... so I'm not sure what he's doing. IT also looks like he keeps resetting the data source on what is probably an ADODC more than once...

Set Me.eventaddname.DataSource = receventname
Me.eventaddname.DataField = "EventName"
Set Me.eventkeyy.DataSource = receventname
Me.eventkeyy.DataField = "EventKey"



receventname.AddNew

recpack.Open "Select * from Packages", conn, adOpenDynamic, adLockOptimistic

Set Me.eventaddname.DataSource = recpack
Me.eventaddname.DataField = "EventName"

O.o

?????????


-tg

[apnomid]

apn

I presume that by "one" click, you have a Sub Command1_Click() or something of that ilk.

If so, then within that sub it seems to me that you'd need to

1. open recordset1
   
   • save stuff to it
   • close it
2. open recordset2
   
   • save stuff to it (even if it is the same)
   • close it

HTH
Spoo

yeah i have, i've just summarized it by putting label(formload and comand save) but those are controls of my program,i also declared my recordset(receventname), i also did try to do what you have just said but another error appeared hehe...

[apnomid]

based on teacher's teachings, i've came up with this logic, that program works fine without 2nd recordset,it's just everything crumbles when i try to throw data into another recordset,

[Spooman]

apn

Sorry you're still stuck.

It might have to do with the "stuff" .. is it available to both recordsets?
Perhaps you could post your current code

BTW, when doing so, use the CODE wrapper feature .. the # button at the right of the menu bar
I've done so here on your OP code (original post) to give you an idea

Form load:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & App.Path & "\SchedDB.mdb"
receventname.CursorLocation = adUseClient

receventname.Open "Select * from EventInfo", conn, adOpenDynamic, adLockOptimistic

Set Me.eventaddname.DataSource = receventname
Me.eventaddname.DataField = "EventName"
Set Me.eventkeyy.DataSource = receventname
Me.eventkeyy.DataField = "EventKey"



receventname.AddNew

recpack.Open "Select * from Packages", conn, adOpenDynamic, adLockOptimistic

Set Me.eventaddname.DataSource = recpack
Me.eventaddname.DataField = "EventName"
Set Me.packname.DataSource = recpack
Me.packname.DataField = "PackageName"
Set Me.packprice.DataSource = recpack
Me.packprice.DataField = "PackagePrice"

recpack.AddNew

command save:

receventname.update
recpack.update

EDIT

BTW, do TG's comments in post #4 make sense to you?

Spoo

[ChrisE]

Hi Spoo,

I think Begintrans etc.. could be an option for this.

this is a DAO sample ..

Private Sub Command15_Click()
Dim dbPath As String
Dim db As Database
dbPath = App.Path & "\NWIND.mdb"
Set db = DBEngine.Workspaces(0).OpenDatabase(dbPath, False)

Dim rsKunden As Recordset
Dim rsBestell As Recordset

Set rsKunden = db.OpenRecordset("tbl_Kunden", dbOpenTable)
Set rsBestell = db.OpenRecordset("tbl_Bestellung", dbOpenTable)


On Error GoTo ErrHandler



Workspaces(0).BeginTrans
'Add a new Customer:
rsKunden.AddNew
'EDIT : there is a Autoincrement No. in tbl_Kunden
'that be pass to the statement below
rsKunden.Fields("KD_Firma") = "ZZFirm"      'Text1.Text
rsKunden.Fields("KD_Strasse") = "ZZStreet"   'Text2.Text
'etc....

'Add in Detailtable further Information about Customer
'pass the ID from above
rsBestell.AddNew
rsBestell.Fields("Kd_ID") = rsKunden!KD_LfdNr '<--pass the ID from tblKunden
rsBestell.Fields("BE_Name") = "test"
'etc....

rsKunden.Update
rsBestell.Update
Workspaces(0).CommitTrans

Exit Sub

ErrHandler:
Workspaces(0).Rollback
MsgBox "Error with Data transaction....."


End Sub

for ADO it will have to be modified

regards
Chris

[apnomid]

this is my Declaration:

Dim conn As New ADODB.Connection


Dim receventname As New ADODB.Recordset
Dim recpack As New ADODB.Recordset

as for my Form Load:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & App.Path & "\SchedDB.mdb"

receventname.CursorLocation = adUseClient

receventname.Open "Select * from EventInfo", conn, adOpenDynamic, adLockOptimistic

        Set Me.eventaddname.DataSource = receventname
            Me.eventaddname.DataField = "EventName"
        Set Me.eventkeyy.DataSource = receventname
            Me.eventkeyy.DataField = "EventKey"


    
receventname.AddNew



recpack.CursorLocation = adUseClient

recpack.Open "Select * from Packages", conn, adOpenDynamic, adLockOptimistic

        Set Me.eventaddname.DataSource = recpack
            Me.eventaddname.DataField = "EventName"
        Set Me.packname.DataSource = recpack
            Me.packname.DataField = "PackageName"
        Set Me.packprice.DataSource = recpack
            Me.packprice.DataField = "PackagePrice"


recpack.AddNew

and then my Save Button:

receventname.Update
recpack.Update
MsgBox "Saved"
Unload Me

[apnomid]

ahm based on my construction, how would you like to construct it if it's ADO?
i would really like to have an advice T^T (if i can ask for more, same code hehehe...)tnx......

[techgnome]

To be honest, most of us would probably dump the ADODC control. Personally, I'd create a Command object, set the command text to an insert statement that will insert the data into the table where it needs to go, then execute it. Repeat for each table or set of tables where it needs to go. In fact, I'd probably create a sub for each table where the data needs to be saved, then call it, passing in the data to save through parameters.

-or-

I'd simply get the initial data into a recordset, disconnect it, then add to it as needed. When the user clicks save, I'd reconnect the recordsets and then use .UpdateBatch on each one to save back to the database, disconnect it, and carry on.

I've always found using the ADODC a bit cumbersome, which sadly soured my taste for binding for many years.

-tg

[ChrisE]

ahm based on my construction, how would you like to construct it if it's ADO?
i would really like to have an advice T^T (if i can ask for more, same code hehehe...)tnx......

here with ADO, a little you will have to do yourself

Private Sub Command2_Click()
   Dim sSQL As String
   Dim CT_ID As Long 'AutoID in table tbl_Contact
   Dim AD_ID As Long 'AutoID in table tbl_Adresse
   
      On Error GoTo Fehler
   
      Cn.BeginTrans
      
      sSQL = "Insert Into tbl_Contact (CT_City) Values ('Frankfurt')"
      Cn.Execute sSQL
      CT_ID = GetNewID(Cn)
      
      sSQL = "Insert Into tbl_Adresse (AD_Adresse) Values ('Frankstrasse')"
      Cn.Execute sSQL
      AD_ID = GetNewID(Cn)
      
      sSQL = "Insert Into tbl_Person (PE_Name, PE_CT_ID, PE_AD_ID) " & _
             "Values ('Chris', " & CT_ID & ", " & AD_ID & ")"
      Cn.Execute sSQL
      
      Cn.CommitTrans
      Exit Sub
      
Fehler:
      Cn.RollbackTrans
End Sub

'Get ID from new Record
'from Access version 2000 and up
Public Function GetNewID(Cn As ADODB.Connection) As Long
   Dim Rs As ADODB.Recordset
   Dim NewID As Long
      Set Rs = New ADODB.Recordset
      Rs.Open "Select @@Identity As ID", Cn, , , adCmdText
      NewID = Rs.Fields("ID").Value
      Rs.Close
      Set Rs = Nothing
      GetNewID = NewID
End Function

regards
Chris

[IkkeEnGij]

receventname.CursorLocation = adUseClient

receventname.Open "Select * from EventInfo", conn, adOpenDynamic, adLockOptimistic

a dynamic client-side cursor ?

[apnomid]

To be honest, most of us would probably dump the ADODC control. Personally, I'd create a Command object, set the command text to an insert statement that will insert the data into the table where it needs to go, then execute it. Repeat for each table or set of tables where it needs to go. In fact, I'd probably create a sub for each table where the data needs to be saved, then call it, passing in the data to save through parameters.

-or-

I'd simply get the initial data into a recordset, disconnect it, then add to it as needed. When the user clicks save, I'd reconnect the recordsets and then use .UpdateBatch on each one to save back to the database, disconnect it, and carry on.

I've always found using the ADODC a bit cumbersome, which sadly soured my taste for binding for many years.

-tg

oh i see, gonna try that tactics, ahm actually i can't say anything back to why i'm using adodc,because for now this is the only gun that my teacher gave us lol, so i have to endure it XD, btw thanks for your advice really really appreciate ^_____^

[apnomid]

here with ADO, a little you will have to do yourself

Private Sub Command2_Click()
   Dim sSQL As String
   Dim CT_ID As Long 'AutoID in table tbl_Contact
   Dim AD_ID As Long 'AutoID in table tbl_Adresse
   
      On Error GoTo Fehler
   
      Cn.BeginTrans
      
      sSQL = "Insert Into tbl_Contact (CT_City) Values ('Frankfurt')"
      Cn.Execute sSQL
      CT_ID = GetNewID(Cn)
      
      sSQL = "Insert Into tbl_Adresse (AD_Adresse) Values ('Frankstrasse')"
      Cn.Execute sSQL
      AD_ID = GetNewID(Cn)
      
      sSQL = "Insert Into tbl_Person (PE_Name, PE_CT_ID, PE_AD_ID) " & _
             "Values ('Chris', " & CT_ID & ", " & AD_ID & ")"
      Cn.Execute sSQL
      
      Cn.CommitTrans
      Exit Sub
      
Fehler:
      Cn.RollbackTrans
End Sub

'Get ID from new Record
'from Access version 2000 and up
Public Function GetNewID(Cn As ADODB.Connection) As Long
   Dim Rs As ADODB.Recordset
   Dim NewID As Long
      Set Rs = New ADODB.Recordset
      Rs.Open "Select @@Identity As ID", Cn, , , adCmdText
      NewID = Rs.Fields("ID").Value
      Rs.Close
      Set Rs = Nothing
      GetNewID = NewID
End Function

regards
Chris

oh thanks, but there are some codes i do not understand,for example begintrans,committrans, so gonna have to self study first hehe, thanks sir,

[apnomid]

i have modified my construction for now, but in the area where two "eventname" shares, the one that is in the receventname(1st recordset) doesn't save but recpack(2nd recorset) does,

[IkkeEnGij]

so here we are, 4 days and 16 posts later
and you still do not have what you want

had you, on the first day of your question, put the database here (compacted and zipped)
together with the code you already had
and clearly explained what you wanted
your problem would, in all probability, have been solved on the very first day

[techgnome]

so here we are, 4 days and 16 posts later
and you still do not have what you want

had you, on the first day of your question, put the database here (compacted and zipped)
together with the code you already had
and clearly explained what you wanted
your problem would, in all probability, have been solved on the very first day

Well the problem is simple: he's trying to add data to multiple recordsets by binding multiple ADODCs ... which ... I think most of us have decided isn't the way to go... so now he's been given a few different ways of going about achieving the same result - the next step is to select one of those methods and use it.

-tg

[IkkeEnGij]

yes, but:

actually i can't say anything back to why i'm using adodc,because for now this is the only gun that my teacher gave us lol, so i have to endure it XD

so if one has to use adodc's because teacher wants so, then one has to use adodc's (seems logical to me)

[apnomid]

thanks everyone! i just solved my problem inspired by mr.spoo,mr.Chris and specially mr.TG!(many thanks) but seems i annoyed some of you and im begging pardon for that, still i thank every one who gave effort to reply this thread hehehe...

[ChrisE]

Hi,

,because for now this is the only gun that my teacher gave us lol, so i have to endure it XD

well in my opinion your Teacher gave you a Bow and Arrow whilst gun's are around.

regards
Chris

[IkkeEnGij]

Hi,
well in my opinion your Teacher gave you a Bow and Arrow whilst gun's are around.
regards
Chris

or, could be that teacher knows guns are to dangerous in the hands of the inexperienced

@ apnomid:
glad you got it solved....
care to tell us how ?

[apnomid]

Hi,



well in my opinion your Teacher gave you a Bow and Arrow whilst gun's are around.

regards
Chris

haha you're ryt XD

[apnomid]

or, could be that teacher knows guns are to dangerous in the hands of the inexperienced

@ apnomid:
glad you got it solved....
care to tell us how ?

it may really sound pretty dumb but, i've just noticed it recently the i'm using a single container(name.datasource and name.datafield) i think its "data binding" that all of you called,

Set Me.eventaddname.DataSource = receventname
Me.eventaddname.DataField = "EventName"

Set Me.eventaddname.DataSource = recpack
Me.eventaddname.DataField = "EventName"

i really thought that it was possible somehow and i ran step by step simulation as to how my program's flow(i think it took me 2hours staring at the screen), so i came up with creating another container(name.text and get it .visible=false to manage UI). That way, they both have their own container.

it's the least i can manage for the time being using those and just only those codes hehe....