ADODB Update with SQL

[arvin2006]

hi everyone. i have problem with SQL UPDATE command, with Add and Save Record, Delete Record and Search record, it works well, I have this code:

for SAVE Command button

squery = "": squery = "SELECT * FROM tblEmp WHERE EmpID=" & txtEmpID.Text & ""
Call ExecuteCommand
With rs
.Open squery, conn, adOpenStatic, adLockPessimistic
'.AddNew
.Fields("EmpID") = txtEmpID.Text
.Fields("LName") = txtLast.Text
.Fields("FName") = txtFirst.Text
.Fields("MName") = txtMiddle.Text
.Fields("Pos") = txtPos.Text
.Update
End With

for SEARCH Command Button

squery = "": squery = "SELECT * FROM tblEmp WHERE EmpID=" & txtEmpID.Text & ""
Call ExecuteCommand
With Me
.txtLast = rs!LName
.txtFirst = rs!FName
.txtMiddle = rs!MName
.txtPos = rs!POs
End With
squery = "": squery = "SELECT * FROM tblComp WHERE EmpID=" & txtEmpID.Text & ""
Call ExecuteCommand
With Me
.txtComp = rs!CName
.txtLoc = rs!Loc
End With

but with EDIT/UPDATE command button, how could I use SQL UPDATE statement?

sytax: UPDATE tblName
SET column1=value, column2=value
WHERE somecolumn=somevalue

how could I use this as above SQL (SAVE/SEARCH)..
I have txtboxes, how could i update the records contained in the textboxes displayed by the SEARCH(command button) query?

thanks..

[javmedia]

With rs
.Open squery, conn, adOpenStatic, adLockPessimistic
.Edit
.Fields("EmpID") = txtEmpID.Text
.Fields("LName") = txtLast.Text
.Fields("FName") = txtFirst.Text
.Fields("MName") = txtMiddle.Text
.Fields("Pos") = txtPos.Text
.Update
End With

[arvin2006]

good morning.. thank you for the reply!

i just forgot the ".Edit" command.

i am using SQL UPDATE statement with this syntax:

UPDATE tblEmp SET LName=" & txtLast.Text & ",FName=" & txtFirst.Text & ",MName=" & txtMiddle.Text & ",Pos=" & txtPos.Text & " WHERE EmpID=" & txtEmpID.Text & ""

and it generates error,

anyway, thanks...

[arvin2006]

but wait, .Edit is neither method nor data member of ADODB Recordset.

[arvin2006]

thread closed. already found the solution. thanks...

[pankaj.garg]

but wait, .Edit is neither method nor data member of ADODB Recordset.

Hi Arvin2006,

though this is an old post..but i am stuck at same problem as you were that time...".edit" is neither method nor data member of ADODB recordset...so how do i edit my record??

Plz help..

Pankaj

[arvin2006]

With rs
.Open squery, conn, adOpenStatic, adLockPessimistic
.Edit <--- REMOVE THIS
.Fields("EmpID") = txtEmpID.Text
.Fields("LName") = txtLast.Text
.Fields("FName") = txtFirst.Text
.Fields("MName") = txtMiddle.Text
.Fields("Pos") = txtPos.Text
.Update
End With

[pankaj.garg]

Thanks...problem solved..
i was giving wrong arguments for open connections..

regards,

Pankaj

With rs
.Open squery, conn, adOpenStatic, adLockPessimistic
.Edit <--- REMOVE THIS
.Fields("EmpID") = txtEmpID.Text
.Fields("LName") = txtLast.Text
.Fields("FName") = txtFirst.Text
.Fields("MName") = txtMiddle.Text
.Fields("Pos") = txtPos.Text
.Update
End With

[danmae]

With rs
.Open "SELECT * FROM employee where fname = '" & Me.txtsearch + "' ", con, adOpenStatic, adLockPessimistic

Me.txtaddress.Text & ",contact=" & Me.txtcontact.Text & ",pass=" & Me.txtpassword.Text & " WHERE fname=" & Me.txtsearch.Text & "", con
.Fields("lname") = Me.txtlname.Text
.Fields("address") = Me.txtaddress.Text
.Fields("pass") = Me.txtpassword.Text
.Fields("contact") = Me.txtcontact.Text