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

edit a recordset

I want to change a recordset from vb60 and tried the follwing excecute commando:

adoConnection.Execute "UPDATE Fragen_Zufall SET Fragen_Zufall.l = -1" &
_
                          "WHERE Fragen_Zufall.kennummer= " &
rs("kennummer") & ""

but couldn't find any change in the mdb.

also i tried the follwing code:

rs("l") = -1
rs.update

but couldn't also find any chnages in my table

the field "l" is an boolshen field (yes/no)

I hope somebody can help me there how i can change a recordset from vb60 in a mdb.


Harry
0
fragen
Asked:
fragen
  • 6
  • 3
  • 2
  • +5
1 Solution
 
mark2150Commented:
Try:

rs.edit
rs("l") = -1
rs.update

M
0
 
fragenAuthor Commented:
Hy mark!

thank you very much for your answer.
i forgot to tell, that i use in vb ado not dao.

so in ado there i cannot use
rs.edit only rs.editmode, but i don't find out how or if i can use rs.editmode to edit a recordset.

when i use rs.edit i get the following error:

error by compiling the code
methode or memberdata not found

i hope you can help me again.

HH
0
 
manojaminCommented:
If following is the correct statement in your code,

adoConnection.Execute "UPDATE Fragen_Zufall SET Fragen_Zufall.l = -1" & 
_
                          "WHERE Fragen_Zufall.kennummer= " &
rs("kennummer") & ""

Check following thins.

1)Don't you need space between "...-1" & " WHERE ..."

2) If kennummer is numeric, you don't need <& ""> (the last part) and make sure you type cast rs("kennumber") with appropriate variant. i.e. Cint(rs("kennumber")) or Clng(rs("kennumber")) etc..

3) If it is string, you needFragen_Zufall.kennummer= '" & 
rs("kennummer") & "'"

Hope this will help...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
grumpy147Commented:
Hi Fragen,

If you are using the method of
 
rs.Fields(...).value = NewValue
rs.Update

check that the lockmode of the RecordSet is not adLockBatchOptimistic; if so, you need to add the following line

rs.UpdateBatch


Hope this helps



0
 
st_steveCommented:
Do you knoe how to implement SQL statements in VB? I mean you declare one object as database and another as recordset and go from there....if you want some sample code...I could write some.let me know.....another problem with your code....you might have to "refresh" the recordset. If you use the method I just outlined, you could just easily call the refresh method:

rs.refresh

assuming you have a statement:

Dim rs as Recordset

Try it and see if it works...
0
 
smeggheadCommented:
fragen,

What I would try initially is to omit the 'UPDATE Fragen_Zufall SET Fragen_Zufall.l = -1'

and just check that ..
"select * from Fragen_Zufall WHERE Fragen_Zufall.kennummer= " & rs("kennummer")

returns a valid record.

If that does, then add a parameter onto the end of the execute statement, so it reads like..

Dim RecordsUpdated%
adoConnection.Execute "UPDATE Fragen_Zufall SET Fragen_Zufall.l = -1 WHERE Fragen_Zufall.kennummer= " &
rs("kennummer"),RecordsUpdated
msgbox RecordsUpdated

Also, in your statement, you have no space between your '= -1where Fra'

Good luck

0
 
fragenAuthor Commented:
'I use the following connection:

Dim sConnectString As String
dim adoconnection as adodb.connection
dim rs as new recordset
dim m_sql as string
   
    sConnectString = "Provider=MSDataShape;" & _
    "Data Provider=Microsoft.Jet.OLEDB.3.51;" & _
    "Persist Security Info=False;" & _
    "Data Source=" & _
    App.Path & _
    "\PRUefungsfragen.mdb;" & _
    "Jet OLEDB:Database Password=tauchen"
   
   
   Set adoConnection = New ADODB.Connection
   adoConnection.Open sConnectString

' then i open a recordset like:

        rs.Open m_sql$, adoConnection, adOpenDynamic


I change my update code like:

   'this code work now:
   adoConnection.Execute "UPDATE Fragen_Zufall SET Fragen_Zufall.l = -1 " & _
                          "WHERE Fragen_Zufall.kennummer= " & CInt(rs("kennummer")) & ""
   
   'but i cannot edit with the command update like:
   
   rs("l").Value = -1
   rs.Update


hy steve:
thanks for your idea

rs.refresh

but an ado recordset dosn't know the option refresh.
I tried rs.save but also without any changes in my mdb table.

i hope somebody can inform me how i can change an data also with the update command.


Thanks a lot
HH
0
 
smeggheadCommented:
Hi, Just checking that you haven't got a reference to DAO in your list of references ???? If you have, it could be using the DAO 'recordset' rather than ADO, I always prefix my declarations with 'adodb.'

0
 
st_steveCommented:
Ok...sorry about the confusion...if you're using an ADO object...you have to tell it specifically....like:

"ADO Object".Recordset.Refresh

I think other experts have better ideas than me....so you better try their ideas first. :)

Tell me the method which works...ok? I'm trying to implement a database server on Lunix as well...so all these might help for me... :)
0
 
fragenAuthor Commented:
'I use the following connection:

Dim sConnectString As String
dim adoconnection as adodb.connection
dim rs as new recordset
dim m_sql as string
   
    sConnectString = "Provider=MSDataShape;" & _
    "Data Provider=Microsoft.Jet.OLEDB.3.51;" & _
    "Persist Security Info=False;" & _
    "Data Source=" & _
    App.Path & _
    "\PRUefungsfragen.mdb;" & _
    "Jet OLEDB:Database Password=tauchen"
   
   
   Set adoConnection = New ADODB.Connection
   adoConnection.Open sConnectString

' then i open a recordset like:

        rs.Open m_sql$, adoConnection, adOpenDynamic


I change my update code like:

   'this code work now:
   adoConnection.Execute "UPDATE Fragen_Zufall SET Fragen_Zufall.l = -1 " & _
                          "WHERE Fragen_Zufall.kennummer= " & CInt(rs("kennummer")) & ""
   
   'but i cannot edit with the command update like:
   
   rs("l").Value = -1
   rs.Update


hy steve:
thanks for your idea

rs.refresh

but an ado recordset dosn't know the option refresh.
I tried rs.save but also without any changes in my mdb table.

i hope somebody can inform me how i can change an data also with the update command.


Thanks a lot
HH
0
 
fragenAuthor Commented:
Thanks a lot for your answers.
I had an reference to DAO. After i killed this reference and i tried again

rs("l").value = -1
rs.update

also nothing changed in my table.

Also i would trie:
"ADO Object".Recordset.Refresh

but i don't know what can be an "ADO Object" in my case.

i cannot find an option when i want to use
rs.
adoconnection.
e.g.

i hope getting an soloution of you.
thanks again
hh
0
 
mhjordanCommented:
To get rs to be an ado recordset, allowing for the adodb properties and methods, you need to change your dim statement:

dim rs as new recordset

to the following:

Dim rs As New ADODB.Recordset

This makes rs an ADO recordset.  Then, when you want to refresh the recordset, all you have to do is:

rs.Recordset.Refresh

I think this may be part of your problem, because it's not specifically being defined as ADO, according to your Dim statement.

Then, when you do your update, do the following:

rs.Update


So, the flow of it would be something like:

--After the connection to the DB is established:
Sub GetAndUpdateDB()
Dim qy as new ADODB.Command
Dim rs as new ADODB.Recordset

   'Set the sql statement
   qy.CommandText="My SQL statement to get Recordset"
   'Retrieve/open recordset
rs.Open qy

   'Set new field value
   rs(field index #)= "New Value"
   rs.Update

End Sub
0
 
mhjordanCommented:
I should also add that once you have updated the recordset (after the rs.Update statement), you should do an rs.Recordset.Refresh statement.

In this case, rs is going to be your ado object.

Make sense?
0
 
fragenAuthor Commented:
hy mhjordan!

the fault was
Dim rs As New ADODB.Recordset

now i can with the follwing code update a data in a recordset

rs("l").value = -1
rs.update

but it is still not possible to use

rs.Recordset.Refresh

ado don't know rs.recordset.

hh
0
 
rinkelCommented:
To refresh a record contained recordset, you should need just use the statement "rs.requery" after running all those updating command of the recordset.
0
 
fragenAuthor Commented:
i have added the command rs.requery but can still not use rs.recordset.refresh because rs don't know the option recordset.

hope again of an detailed answer.
hh
0
 
smeggheadCommented:
Use the resync or requery methods of the recordset.

The requery method is the equivalant of closing the recordset and re-opening it with exactly the same SQL and query options.

The resync method will update the recordset, but will fail if any records have been inserted / deleted, since the recordset was initially opened.

Good luck

Smegg.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now