#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    10
    Rep Power
    0

    Error when deleting multiple records in a database


    Hello Board,

    I get this message when trying to delete:

    The column prefix 'username' does not match with a table name or alias name used in the query.

    I am not sure what this error is. I checked all over the place for the problem. Can someone help.

    Thanks in advance.
    Last edited by zwarte; February 2nd, 2003 at 02:54 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Oct 2000
    Location
    Back in the real world.
    Posts
    5,966
    Rep Power
    191
    Hello User!

    The command you used to delete the columns is invalid.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    10
    Rep Power
    0
    Hello M.Hirsch,

    This is the command I am using:

    DELETE FROM Table WHERE username=someusername OR username=someusername OR username = someusername

    This a multiple delete

    Thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Oct 2000
    Location
    Back in the real world.
    Posts
    5,966
    Rep Power
    191
    i knew it...

    DELETE FROM Table WHERE username='someusername' OR username='someusername' OR username = 'someusername';
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    10
    Rep Power
    0
    Tried that, same error message.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Oct 2000
    Location
    Back in the real world.
    Posts
    5,966
    Rep Power
    191
    to explain my assumptions (i never did a single line of ASP or VBScript):
    The error message seems to tell that somehow data from a column or variable ("info@interviewprep") got into the query in a place / way that it is being interpreted as column name (-prefix).

    Are you running another query before or after this one?
    Can we have some lines of your code?
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    10
    Rep Power
    0
    dim num
    dim xide
    dim whr
    dim sql
    whr=""
    xide=split(request.form("checkbox"),",",-1,1)
    for each num in xide
    if whr<>"" then whr=whr & " OR "
    whr=whr & "username=" & num
    next
    sql="DELETE FROM Table WHERE "
    sql=sql & whr

    set Command1 = Server.CreateObject("ADODB.Command")
    Command1.ActiveConnection = "dsn=mydsn"
    Command1.CommandText = sql
    Command1.CommandType = 1
    Command1.CommandTimeout = 0
    Command1.Prepared = true
    Command1.Execute()
    Response.Write(sql)

    Hope this helps.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Oct 2000
    Location
    Back in the real world.
    Posts
    5,966
    Rep Power
    191
    you stil didnīt put my corrections in there...

    whr=whr & "username='" & num & "'"
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    10
    Rep Power
    0
    Yeah, I did. I just didn't post that copy in. I revereted to original that gave me problems, cause this is doing the same.

    Thanks for your concern.

    -Jay
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Oct 2000
    Location
    Back in the real world.
    Posts
    5,966
    Rep Power
    191

    Unhappy


    Sorry, then this problem is beyond my scope.

    Maybe anyone else here can help? (i know there is few people here doing ASP, but maybe someone new here?)
  20. #11
  21. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    10
    Rep Power
    0
    No worries! I used what you suggested and switched criteria to match and ID. For some reason, @ symbol was the problem.

    I appreciate everything. Thumbs up.

    Cheers,

    Version
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Oct 2000
    Location
    Back in the real world.
    Posts
    5,966
    Rep Power
    191
    thinking about it again. the "@" is the problem. is it a special char in ASP? like defining a variable or array or something?

    in php/mysql, you would have to escape the string with mysql_escape_string() or something similar to make it compatible to the databasesīs expected format.

    there should be a similar command for ASP that should solve your problem.

    tnx for the flowers
  24. #13
  25. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    Philladelphia
    Posts
    23
    Rep Power
    0
    Originally posted by M.Hirsch
    thinking about it again. the "@" is the problem. is it a special char in ASP? like defining a variable or array or something?

    in php/mysql, you would have to escape the string with mysql_escape_string() or something similar to make it compatible to the databasesīs expected format.

    there should be a similar command for ASP that should solve your problem.

    tnx for the flowers

    the trick is IIRC to put a ' into the string before the @.

    so the final string owuld look like "name'@place.com"
    that's what you use in SQL to add ' to the string "that's it" would need too look like "that''s it" (with the middle '' being 2 of ' )

IMN logo majestic logo threadwatch logo seochat tools logo