Results 1 to 14 of 14

Thread: [RESOLVED] Delete specific Data

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    222

    Resolved [RESOLVED] Delete specific Data

    I Write this code for delete data.

    Code:
    con.Execute "DELETE * from Dad where SrNo = '" & Me.txtsr & "' And DadItems = "" & Me.txtitem  & " '"
    There is no data delete with this code.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Delete specific Data

    debug this query....what is actually sent to the database?

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Delete specific Data

    Code:
    DadItems = "" & Me.txtitem  & " '"
    You do realize you're including a space on the inside of the single quote there, right? so instead of "text" you're sending "text " ... the two are not equal, so if there's no space in the field in the data, that's why it's not deleting, it's not matching the criteria.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Delete specific Data

    Also there is an issue with the quoting has a double quote where it should be single
    Code:
    DadItems = "" & Me.txtitem  & " '"

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    222

    Re: Delete specific Data

    debug.print
    Code:
    Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\nusaiba\Database\InvData.mdb;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;

    Now I write code without space.

    Code:
    con.Execute "DELETE * from Dad where SrNo = '" & Me.txtsr & "' And DadItems = '" & Me.txtitem & "'"
    no change.

  6. #6
    gibra
    Guest

    Re: Delete specific Data

    You should check your sql string, before to execute the command:

    Code:
    Dim sql As String
    sql = "DELETE * from Dad where SrNo = '" & Me.txtsr & "' And DadItems = '" & Me.txtitem & "'"
    
    Debug.Print sql
    
    con.Execute sql
    shows us the Debug.Print result

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    222

    Re: Delete specific Data

    Code:
    DELETE * from Dad where SrNo = '1145' And DadItems = 'Admin Form'

  8. #8
    Lively Member
    Join Date
    May 2004
    Location
    right here
    Posts
    87

    Re: Delete specific Data

    Hello,

    Shouldn't the query be:
    DELETE FROM table_name WHERE condition;
    So you would use:
    DELETE FROM Dad WHERE SrNo = '1145' And DadItems = 'Admin Form'
    If Not Now Then When

    If Not Here Then Where

  9. #9
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Delete specific Data

    if SrNo is a numeric field in the db then you need to loose the single quotes around it.

    I'm not sure about the * I know it is not needed, can't remember off the top of my head if it causes a problem when there

    If the first thing I mentioned is your problem then it should be giving you a type mismatch error
    If the second is a problem then you should get a syntax error

    Of course if you are using on error resume next it will not tell you what error you are getting and instead just doesn't do anything.

  10. #10
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Delete specific Data

    Like DM says about the SrNo field...

    Anyway, look into your Dad table...IS there a record that has an SrNo equal to 1145; AND does that same record have a the DadItems field equal to "Admin Form"?

    Also, you may want to look at your DB structure. If you have unique SrNo's (assuming that is short for "Serial Number"), then you don't need the ' and DadItems = "Admin Form"' at all.

    If you are using MS Access as your DB, it does not matter if you include the asterisk (*) or not in the Delete command.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    222

    Re: Delete specific Data

    Thanks all.

  12. #12
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: [RESOLVED] Delete specific Data

    Resolved? How? Let future Thread Browsers see your resolution.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    222

    Re: [RESOLVED] Delete specific Data

    I write this code:
    Code:
    sql = "DELETE from Dad where SrNo = " & Me.txtsr & " And DadItems = '" & Me.txtitem & "'"

  14. #14
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: [RESOLVED] Delete specific Data

    So it would seem that what you were getting was a type mismatch error on your query by trying to compare a text value to a numeric field.
    I am guessing that you are using On Error Resume Next or other method of ignoring errors keeping you from having saw that error message pop up when executing the query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width