how to delete a record

[meLiel]

**how can i delete a record on a database?

im using this codes:

Private Sub cmdDelete_Click()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    Dim rsDelete As ADODB.Recordset
    Set rsDelete = New ADODB.Recordset

    MsgBox "Are you sure you want to delete?", vbYesNo, "Santo Niño Census Info System"
    ' i dont know next..
End Sub

please help..im new to vb..thanks

[cigoL..:)]

are you sure your heading the right way!? This might help you. it's in vb.net.

Click This!

I think it was discuss in another thread. this might help you..

[Jx_Man]

Try this :

Private Sub cmdDelete_Click()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    Dim rsDelete As ADODB.Recordset
    Set rsDelete = New ADODB.Recordset
    Dim Query As String

    If MsgBox("Are you sure you want to delete?", vbYesNo, "Santo Niño Census Info System") = vbYes Then
        Query = "DELETE FROM Users WHERE IdUser ='" & Trim(txtId) & "'"
        con.Execute Query, , adCmdText
    End If
End Sub

[RonalBertogi]

The code below assumes that you have already established your database connection. To delete a particular record the code is:

con.Execute "DELETE FROM <<tablename>> WHERE <<fieldFilter>>='discardable'"

NOTES:

1. fieldFilter is the name of field in the table where you are to delete records.
2. fieldFilter can take different systax. It can be a string like in this example where single-quotes are mandatory. If it's a number then the quotes is not needed. If your fieldFilter is a boolean, the syntax should be this: <<fieldFilter Is True>>. When you specify your actual tablename and fieldFilter name, omit these << and these >>.

[RonalBertogi]

The code below assumes that you have already established your database connection. To delete a particular record the code is:

con.Execute "DELETE FROM <<tablename>> WHERE <<fieldFilter>>='discardable'"

NOTES:

1. fieldFilter is the name of field in the table where you are to delete records.
2. fieldFilter can take different systax. It can be a string like in this example where single-quotes are mandatory. If it's a number then the quotes is not needed. If your fieldFilter is a boolean, the syntax should be this: <<fieldFilter Is True>>. When you specify your actual tablename and fieldFilter name, omit these << and these >>.

[AndreRet]

Most above contains the answer. Remember to return a record first that will be deleted otherwise the first record in the table will be deleted, not the one you wanted. So, to do some error trapping -

Private Sub cmdDelete_Click()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    Dim rsDelete As ADODB.Recordset
    Set rsDelete = New ADODB.Recordset
    Dim Query As String, strId As String ''taking string, id might not always be integer...

    ''Assuming that you get the client id from a text box...
    If txtId.Text = vbNullString Then
        Msgbox "Please enter an id first.", vbOkOnly, vbInformation

        Exit Sub
            Else
        strId = txtId.Text
    If MsgBox("Are you sure you want to delete?", vbYesNo, "Santo Niño Census Info System") = vbYes Then
        Query = "DELETE FROM Users WHERE IdUser ='" & strId & "'" ''IdUser must be replaced by your field name...
        con.Execute Query, , adCmdText
    End If
End Sub

[meLiel]

thanks for all the replies

@ andreret:
what does this run time error mean?
"Operation is not allowed when the object is closed."

i tried the codes and it highlighted this part:

con.Execute Query, , adCmdText

sorry for my late response..we lived in diffeent time zones..im n southeast asia..:)

[RonalBertogi]

@meLiel,

As what I've said in my previous post, you have to make sure that you have successfully established the connection to your database before doing any database operation (e. g. querying, udpating, deleteing, etc) thus the error.

Here is a simple procedure to do that:

Dim conDB As ADODB.Connection ' it is sometimes ideal to make this a global variable when
                              ' you will be just accessing a single database

In your startup procedure (either in Sub Main() or in your Form_Initialize() procedure) set this following code:

Set conDB = new ADODB.Connection
conDB.CursorLocation = adUseClient

'Be sure to handle errors properly in case they occur
On Error Goto abort_app
conDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<<Your_Database_Path>>;Persist Security Info=False"
Exit Sub

abort_app:
    MsgBox "Error opening database"
    Unload Me

NOTE: Make appropriate modification in the previous code. Such code was in the context of the Form_Initialize event of your Visual Basic form.

Now that you hvae already made the connection, you can do whatever you want to your database. Of course, it will still depend on the features provided by the provider.

Now, to delete a record, it's just as simple as

DELETE FROM <<tablename>> WHERE <<fieldFilter>>='discardable'

What makes sure that you won't delete the records you don't want to is the filter. You can add more filters to narrow the selection of records you want to delete like this:

DELETE FROM myTable WHERE record1='invalidrecord' AND record2=0 AND IsIncorrect Is True

[AndreRet]

The error is because the connection/recordset is closed. We need to open it first...

I'll be assuming that you are using MS Access as database... The following connection can then be used (as per RonalBertogi above, thanx!)

Private Sub cmdDelete_Click()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection

    Dim Query As String, strId As String ''taking string, id might not always be integer...

    ''Open your connection...
    con.Open "provider = microsoft.jet.oledb.4.0;persist security info=false;data source = " & App.Path & "\YourDatabaseNameHere.mdb"
    con.CursorLocation = adUseClient

    ''We do not need to open a recordset here, the con.execute query will do the work...

    ''Assuming that you get the client id from a text box...
    If txtId.Text = vbNullString Then
        Msgbox "Please enter an id first.", vbOkOnly, vbInformation
        Exit Sub
            Else
        strId = txtId.Text
    If MsgBox("Are you sure you want to delete?", vbYesNo, "Santo Niño Census Info System") = vbYes Then
        Query = "DELETE FROM Users WHERE IdUser ='" & strId & "'" ''IdUser must be replaced by your field name...
        con.Execute Query, , adCmdText
    End If
End Sub

No problem on the late reply, it's totally understandable :)

[meLiel]

i had open my connection and still it gives me this error..
"No value given for one or more required parameters."

Private Sub cmdDelete_Click()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    Dim rsDelete As ADODB.Recordset
    Set rsDelete = New ADODB.Recordset
    Dim Query As String, strId As String 

    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Asus\Desktop\VBTHESIS_liel2\ListofMembers.mdb;Persist Security Info=False"


    If txtEnterID = vbNullString Then
        MsgBox "Please enter an ID Number first.", vbOKOnly, "Santo Niño Census Info System"
        txtEnterID.SetFocus
        Exit Sub
            Else
        strId = txtEnterID
    If MsgBox("Are you sure you want to delete?", vbYesNo, "Santo Niño Census Info System") = vbYes Then
        Query = "DELETE FROM census WHERE txtEnterID ='" & strId & "'"
        con.Execute Query, , adCmdText
    End If
    End If
End Sub

[RonalBertogi]

Query = "DELETE FROM census WHERE txtEnterID ='" & strId & "'"
Are you sure txtEnterID field exists in your census table? Query filter names must be existing fields in the database's tables. If it don't exist you will get this error. And more thing, data must match with the data type of that field. If it's a string then you would pass string data in single quotes. Numbers don't have and MUST not be enclosed in quotes.

To be sure, open your database and look at your table, in this case the census. Select the field/(s) that would contain the unique data and use them as your filters in your delete query.

[meLiel]

i found my error..

Query = DELETE FROM census WHERE txtEnterID = '" & strId & "'"

I replace txtEnterID to ID..

txtEnterID is not on my table..

thanks for the help :))))

[RonalBertogi]

How nice miLiel!!! You're from Manila and I'm here in Leyte sitting in front of my PC and we're talking like we are somewhere else. ja ja ja ja!!!