Deleting Specific Records How?
Hello ,
I have this simple program and just wondering why it behaves like this,
I just want to delete a single record on a table
but instead of deleting only one, it deleted all the fields on it what do i missing here?
Here is my sample code:
DELETE CODE:
Private Sub Command3_Click()
Dim rs As New ADODB.Recordset
Dim rsView As New ADODB.Recordset
rs.Open "Select * From jobrecord" & _
" Where trim(left(plateno, len('" & Text1.Text & "'))) = trim('" & Text1.Text & "')", connAuto, adOpenDynamic, adLockOptimistic
If MsgBox("Are you sure you want to delete selected record.?", vbExclamation + vbYesNo) = vbYes Then
connAuto.Execute "Delete From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
End If
rsView.Open "Select * From JobReportQuery " & _
" Where trim(left(plateno, len('" & Text1.Text & "'))) = trim('" & Text1.Text & "')", connAuto, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rsView
End Sub
please see attached file screen 1 and screen 2.
hoping for your help guys..
Thanks naz.
Re: Deleting Specific Records How?
VB Code:
connAuto.Execute "Delete * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
for delete
Re: Deleting Specific Records How?
Hi shak
Still have the same output it still delete all records.
when i click the delete button, just want to delete specific records.
Re: Deleting Specific Records How?
VB Code:
rs.Open "select * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
While Not rs.EOF
rs.Delete
rs.MoveNext
Wend
Re: Deleting Specific Records How?
Put your sql statement into a variable then do a
Debug.Print yourvariablename
Then post that. It is amazing how many error are solved by just that technique.
Re: Deleting Specific Records How?
thanks for that, but i dont get it right, what i mean is that why is it the top record are always selected "rs.movenext" should move to the next record right?
for example:
record 1
record 2 ---------- i want to delete this record
record 3
instead deleting this record all records 1,2 and 3 are selected/delete.
Hmm....?
why do i get this error " Items cannot be found the collection corresponding to the requested name or ordinal."
thanks naz
Re: Deleting Specific Records How?
You can't do it that way using movenext. Once you delete a record the next record becomes the current cursor position. Try this:
VB Code:
SQL = "Delete * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
Debug.Print SQL
Then post what it shows...
Re: Deleting Specific Records How?
have you checked the value of plateno for your records in the table...
Re: Deleting Specific Records How?
Thanks ran, correct me if i am wrong but is this right one to insert it?
[/Highlight]Private Sub Command3_Click()
Private Sub Command3_Click()
Dim rs As New ADODB.Recordset
Dim rsView As New ADODB.Recordset
SQL = "Delete * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
Debug.Print SQL
' rs.Open "Select * From jobrecord" & _
' " Where trim(left(plateno, len('" & Text1.Text & "'))) = trim('" & Text1.Text & "')", connAuto, adOpenDynamic, adLockOptimistic
' If MsgBox("Are you sure you want to delete selected record.?", vbExclamation + vbYesNo) = vbYes Then
' connAuto.Execute "Delete * From jobrecord " & _
' "Where plateno = '" & rs.Fields("plateno") & "'"
' End If
' rsView.Open "Select * From JobReportQuery " & _
' " Where trim(left(plateno, len('" & Text1.Text & "'))) = trim('" & Text1.Text & "')", connAuto, adOpenDynamic, adLockOptimistic
' Set DataGrid1.DataSource = rsView
End Sub
[Highlight=VB]
i know that this has a lot of correction to be made, thanks
Re: Deleting Specific Records How?
You can leave all the other code in there but you also need
Dim SQL as String
Then put a breakpoint on the next line after the Debug.Print Statement to see the results. Copy and paste the information in the post.
Re: Deleting Specific Records How?
ya, now post the debug window's text....that is the Sql statement...
Re: Deleting Specific Records How?
Hi ganesh
I send him a query
VB Code:
connAuto.Execute "Delete * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
bu why this query is not working?
Re: Deleting Specific Records How?
Delete * From jobrecord Where plateno = 'XXX-XXX'
here's what i get after putting some breakpoint
now i see so all the data which is = to xxx-xxx is now deleted thats why, there is no
records left is that right?
big help ran. ganes right i have to check the value first.
Re: Deleting Specific Records How?