delete record

help with deleting a record

Page 1 of 1

3 Replies - 38463 Views - Last Post: 08 February 2007 - 05:18 AM Rate Topic: -----

#1 jpamps  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 06-December 06

delete record

Posted 07 December 2006 - 12:53 AM

hi, i've been having trouble with deleting a record in a database. i'm very new to adodb, so i dont know much. welps.. this is what i have so far:

Private Sub cmdDelete_Click()

Dim MyConn As adodb.Connection
Dim rs As adodb.Recordset

Set MyConn = New adodb.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\List1.mdb;"
MyConn.Open

If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then 'check if you really want to delete this record
Exit Sub
Else

With MyConn
.BeginTrans
.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index)
.CommitTrans
End With

End If

rs.Close
Set rs = Nothing


how come this doesnt work? i get an error saying Syntax error in string query expression 'OrigTitle = 'whatever'. where 'whatever' is what the user selects in the first column of the ListView.

it then points to:
.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index)

so can anybody help me out with this? or give any other advise on how to delete a record? many thanks in advance for your help!

Is This A Good Question/Topic? 0
  • +

Replies To: delete record

#2 2much2young  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 14
  • Joined: 16-November 06

Re: delete record

Posted 07 December 2006 - 01:50 AM

Think you might need to add a bit at the end

.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index) & "'"
Was This Post Helpful? 0
  • +
  • -

#3 jpamps  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 06-December 06

Re: delete record

Posted 07 December 2006 - 09:50 PM

View Post2much2young, on 7 Dec, 2006 - 01:50 AM, said:

Think you might need to add a bit at the end

.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index) & "'"



YAY!!!! it works now!!! thanks a lot!!

This post has been edited by jpamps: 08 December 2006 - 12:20 AM

Was This Post Helpful? 0
  • +
  • -

#4 arun1516  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 08-February 07

Re: delete record

Posted 08 February 2007 - 05:18 AM

View Postjpamps, on 7 Dec, 2006 - 12:53 AM, said:

hi, i've been having trouble with deleting a record in a database. i'm very new to adodb, so i dont know much. welps.. this is what i have so far:

Private Sub cmdDelete_Click()

Dim MyConn As adodb.Connection
Dim rs As adodb.Recordset

Set MyConn = New adodb.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\List1.mdb;"
MyConn.Open

If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then 'check if you really want to delete this record
Exit Sub
Else





With MyConn
.BeginTrans
.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index)
.CommitTrans
End With

End If

rs.Close
Set rs = Nothing


how come this doesnt work? i get an error saying Syntax error in string query expression 'OrigTitle = 'whatever'. where 'whatever' is what the user selects in the first column of the ListView.

it then points to:
.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index)

so can anybody help me out with this? or give any other advise on how to delete a record? many thanks in advance for your help!





if you want to delete all the records from the database use this code to delete all the records from the database

conn.execute "delete from table name "
thats it ....
you can place a message box or anything you want to alert the user with




now if you want delete specific record from the database
here is the code

sql = " delete from table name where fields = '" & fieldname & "'"
( fields here is the database feildname and fieldname is the field on your form)
rs.open , sql , conn , openstatic , adlockpessimistic
with cmd
cmd is basically the command string we have defined in the module section
.activeconnection = conn
.commandtext = sql
.exexute
thats it .........
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1