By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Please go thru this code,,,,,UPDATE QUERY....

100+
P: 103
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEdit_Click()
  2. mName = Trim(ListView1.SelectedItem)
  3. mesge = MsgBox("Are you Sure you Want to Update " & mName & "?", vbOKCancel, "ERROR")
  4. '=MsgBox "You have selected the employee" & mName & "Continue", vbOKCancel, "PAYROLL"
  5. If mesge = vbOK Then
  6.     rs.Open "select * from empdetail where name = '" & mName & "'", conn, adOpenStatic, adLockOptimistic
  7.     Form1.txtid.Text = rs!id
  8.     Form1.txtname.Text = rs!Name
  9.     Form1.txtwhr.Text = rs!whours
  10.     Form1.txtrate.Text = rs!Rate
  11.     Form1.txtorate.Text = rs!otrate
  12.     txtid.Enabled = False
  13.     'txtname.SetFocus
  14.     Form1.txtname.SetFocus
  15.     rs.Close
  16. Set rs = Nothing
  17.  
  18.    Else
  19. conn.Execute "update empdetail set Name= '" & txtname & "',whours='" & txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "'"
  20. MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS"
  21.    End If
  22.  
the code above is to update the fields other than id in aparticular form,please go thru it,that is not working???
Jan 10 '08 #1
Share this Question
Share on Google+
9 Replies


QVeen72
Expert 100+
P: 1,445
Hi,

Not very clear as what you want to do here...
You ask a "Want to update", If user clicks OK, then You are displaying the records, or else, You are Running an Update Query..

What you are wanting to do here...?

REgards
Veena
Jan 10 '08 #2

100+
P: 103
Hi,
Not very clear as what you want to do here...
You ask a "Want to update", If user clicks OK, then You are displaying the records, or else, You are Running an Update Query..
What you are wanting to do here...?
REgards
Veena
i click one data from a listview then clicks ok,after that i can change all fields except id,which is a diasbled textbox,then after that i click update...then i need the message "updated"
Jan 10 '08 #3

debasisdas
Expert 5K+
P: 8,127
You need to add the WHERE clause to the update query and pass the id to it.
Jan 10 '08 #4

QVeen72
Expert 100+
P: 1,445
Hi,

OK, in that case, Keep 2 seperate buttons: "Edit" and "Update"

Wtite this code in Edit_Click Event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEdit_Click()
  2.     mName = Trim(ListView1.SelectedItem)
  3.     rs.Open "select * from empdetail where name = '" & mName & "'", conn, adOpenStatic, adLockOptimistic
  4.     Form1.txtid.Text = rs!id
  5.     Form1.txtname.Text = rs!Name
  6.     Form1.txtwhr.Text = rs!whours
  7.     Form1.txtrate.Text = rs!Rate
  8.     Form1.txtorate.Text = rs!otrate
  9.     txtid.Enabled = False
  10.     Form1.txtname.SetFocus
  11.     rs.Close
  12.     Set rs = Nothing
  13. End Sub
  14.  
And Now Write This Code in Update_Click event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdate_Click()
  2.     mName = Trim(ListView1.SelectedItem)
  3.     mesge = MsgBox("Are you Sure you Want to Update " & mName & "?", vbOKCancel, "ERROR")
  4.     If mesge = vbOK Then
  5.         conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &  txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = '" &     trim(txtID.Text) & "' "
  6.     MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS"
  7.     End If
  8. End Sub
  9.  
Both can be Clubed and done in Single Command button, depending on the Caption, but this will be easy to debug for beginner..


Regards
Veena
Jan 10 '08 #5

100+
P: 103
Hi,

Both can be Clubed and done in Single Command button, depending on the Caption, but this will be easy to debug for beginner..
Regards
Veena
veena,
that means we can combine both codes as i have done in #1,if so ny bugs occurs or should have to add enough codings to for that being under a single command button,nythng wrong please explain........regarding that...Nyway Thanks in advance for ur reply
Jan 10 '08 #6

QVeen72
Expert 100+
P: 1,445
Hi,

Yes, you can Club Both Functions in One Command button . In Form Load or design time make the caption of cmdEdit = "Edit"
and change your code this way :

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEdit_Click()
  2. mName = Trim(ListView1.SelectedItem)
  3. If cmdEdit.Caption = "Edit" Then
  4.     rs.Open "select * from empdetail where name = '" & mName & "'", conn, adOpenStatic, adLockOptimistic
  5.     Form1.txtid.Text = rs!id
  6.     Form1.txtname.Text = rs!Name
  7.     Form1.txtwhr.Text = rs!whours
  8.     Form1.txtrate.Text = rs!Rate
  9.     Form1.txtorate.Text = rs!otrate
  10.     txtid.Enabled = False
  11.     Form1.txtname.SetFocus
  12.     rs.Close
  13.     Set rs = Nothing
  14.     cmdEdit.Caption = "Update"
  15. Else
  16.     mesge = MsgBox("Are you Sure you Want to Update " & mName & "?", vbOKCancel, "ERROR")
  17.     If mesge = vbOK Then
  18.         conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &     txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = '" & txtID.Text & "'"
  19.         MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS"
  20.    End If
  21.    cmdEdit.Caption = "Edit"
  22.     Form1.txtid.Text = ""
  23.     Form1.txtname.Text =""
  24.     Form1.txtwhr.Text = ""
  25.     Form1.txtrate.Text = ""
  26.     Form1.txtorate.Text = ""
  27. End If
  28.  
Regards
Veena
Jan 10 '08 #7

100+
P: 103
Hi,

Yes, you can Club Both Functions in One Command button . In Form Load or design time make the caption of cmdEdit = "Edit"
and change your code this way :

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEdit_Click()
  2. mName = Trim(ListView1.SelectedItem)
  3. If cmdEdit.Caption = "Edit" Then    rs.Open "select * from empdetail where name = '" & mName & "'", conn, adOpenStatic, adLockOptimistic
  4.     Form1.txtid.Text = rs!id
  5.     Form1.txtname.Text = rs!Name
  6.     Form1.txtwhr.Text = rs!whours
  7.     Form1.txtrate.Text = rs!Rate
  8.     Form1.txtorate.Text = rs!otrate
  9.     txtid.Enabled = False
  10.     Form1.txtname.SetFocus
  11.     rs.Close
  12.     Set rs = Nothing
  13.     cmdEdit.Caption = "Update"
  14. Else
  15.     mesge = MsgBox("Are you Sure you Want to Update " & mName & "?", vbOKCancel, "ERROR")
  16.     If mesge = vbOK Then
  17.         conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &     txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = '" & txtID.Text & "'"        MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS"
  18.    End If
  19.    cmdEdit.Caption = "Edit"
  20.     Form1.txtid.Text = ""
  21.     Form1.txtname.Text =""
  22.     Form1.txtwhr.Text = ""
  23.     Form1.txtrate.Text = ""
  24.     Form1.txtorate.Text = ""
  25. End If
  26.  
Regards
Veena
the control moves from,the first cmdedit.caption =edit to the update statement
where i have kept the caption in design time to edit,error showing cannot convert varchar to numeric,
Jan 10 '08 #8

QVeen72
Expert 100+
P: 1,445
Hi,

Well, You must be having ID as numeric field, change the update statement to :

Expand|Select|Wrap|Line Numbers
  1. conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &     txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = " _
  2. &  Val(txtID.Text)
  3. MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS
  4.  
Regards
Veena
Jan 10 '08 #9

100+
P: 103
Hi,

Well, You must be having ID as numeric field, change the update statement to :

Expand|Select|Wrap|Line Numbers
  1. conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &     txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = " _
  2. &  Val(txtID.Text)
  3. MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS
  4.  
Regards
Veena
Thanx VEENA ,now its working!!!
Jan 10 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.