dcsimg
Results 1 to 20 of 20

Thread: ADO Querying a table based on a field without a key

  1. #1

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    ADO Querying a table based on a field without a key

    I cannot seem to extract a record based on the value of a string field which does not have a key.
    I also cannot make this field a key field as there are null values in it.

    My query is:

    Code:
    SELECT * from serverfolders WHERE foldername='" & myfoldername & "'"
    The record exists and the opening of the recordset does not give any error. It only says rs.EOF.
    Am I missing something?

    Thanks
    PK

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

    Re: ADO Querying a table based on a field without a key

    1-what does your statement look like in debug.print window (meaning, the actual query sent)
    2-key should not make a difference
    3-what do you mean 'It only says rs.EOF'?

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,952

    Re: ADO Querying a table based on a field without a key

    It may also help to know what DB you are using and what field type you are using.
    Could it be that your db is set for case sensitivity or that you are runnign into an issue with leading or trailing spaces?

  4. #4

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: ADO Querying a table based on a field without a key

    I am using Sql Server 2012 and the field is varchar(100). The query is in exacly the same upper lower case sequence as the field data:
    Code:
    SELECT * FROM serverfolders WHERE foldername='Documents\DocumentsIMS'
    It should not return .EOF, but one record.

    PK

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,354

    Re: ADO Querying a table based on a field without a key

    What's returned from Select * from serverfolders ?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,336

    Re: ADO Querying a table based on a field without a key

    I thought maybe that back slash may have been an escape character but it is not:

    create table ##serverfolders(foldername Varchar(100))
    insert into ##serverfolders(foldername) values('Documents\DocumentsIMS')
    SELECT * FROM ##serverfolders WHERE foldername='Documents\DocumentsIMS'

    Does it work outside your program in management studio?
    Please remember next time...elections matter!

  7. #7

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: ADO Querying a table based on a field without a key

    In SSMS it also does not return any records.

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,336

    Re: ADO Querying a table based on a field without a key

    Quote Originally Posted by Peekay View Post
    In SSMS it also does not return any records.
    Then how can you say "The record exists"?
    Please remember next time...elections matter!

  9. #9
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    18,461

    Re: ADO Querying a table based on a field without a key

    Quote Originally Posted by Peekay View Post
    I am using Sql Server 2012 and the field is varchar(100). The query is in exacly the same upper lower case sequence as the field data:
    Code:
    SELECT * FROM serverfolders WHERE foldername='Documents\DocumentsIMS'
    It should not return .EOF, but one record.

    PK
    Excuse me, no insult intended....

    If you run that exact same query, as is, inside SQL Server2012, does it return 1+ records?

    If so, are you sure your connection string (connection object) is connecting to the same database & table or is it maybe connecting to a different one used for debugging and you forgot to change it back?

    Also, not sure it was asked, is this ADO or DAO? May not matter, but as long as we are asking questions....
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  10. #10

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: ADO Querying a table based on a field without a key

    If you run that exact same query, as is, inside SQL Server2012, does it return 1+ records?
    I replied to TysonLPrice this:

    In SSMS it also does not return any records
    If so, are you sure your connection string (connection object) is connecting to the same database & table or is it maybe connecting to a different one used for debugging and you forgot to change it back?
    I am doing it on my localhost where I have only one database by that name.

    Also, not sure it was asked, is this ADO or DAO? May not matter, but as long as we are asking questions....
    The heading of this thread says ADO

    My table is attached

    Name:  SQL table folders.jpg
Views: 56
Size:  9.4 KB



    I know I am doing something stupid somewhere.

    PK

  11. #11
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,336

    Re: ADO Querying a table based on a field without a key

    Please show us the code. Use code tags.
    Please remember next time...elections matter!

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,502

    Re: ADO Querying a table based on a field without a key

    Right.... 'Documents\DocumentsIMS' doesn't exist... 'Documents/DocumentsIMS' does....

    -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??? *

  13. #13
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,336

    Re: ADO Querying a table based on a field without a key

    Quote Originally Posted by techgnome View Post
    Right.... 'Documents\DocumentsIMS' doesn't exist... 'Documents/DocumentsIMS' does....

    -tg
    heh heh - you get the prize. So much for "The record exists"
    Please remember next time...elections matter!

  14. #14

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: ADO Querying a table based on a field without a key

    Thanks so much. I knew I would be embarassed.
    PK

  15. #15
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    648

    Re: ADO Querying a table based on a field without a key

    Quote Originally Posted by Peekay View Post
    I cannot seem to extract a record based on the value of a string field which does not have a key.
    Others have found the root cause of your problem.

    Let me just add that this query will get progressively slower as your table increases in size.

    Quote Originally Posted by Peekay View Post
    I also cannot make this field a key field as there are null values in it.
    You should still be able to put an index on this field, which will [greatly] help, even if it's not a formal "key" that other tables can reference.

    Also, if the values are empty strings, then you can find them with:
    Code:
    where x = ''
    If they really are NULLs, though, you'll need to use ...
    Code:
    where x IS NULL
    ... to find them.

    Unlike Oracle, SQL Server does NULLs correctly.

    Regards, Phill W.

  16. #16
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,336

    Re: ADO Querying a table based on a field without a key

    Quote Originally Posted by Peekay View Post
    Thanks so much. I knew I would be embarassed.
    PK
    I feel that way about ten times a day
    Please remember next time...elections matter!

  17. #17

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: ADO Querying a table based on a field without a key

    TysonLPrice,
    Glad you know the feeling. Though ... there's one (like me) born every minute. Some people here find it scintillating and others frustrating.

    Phill.W,
    I have in the meantime deleted all the null records, so I can now make that field a primary key.

    Thank you for all you folks who are always willing to help me. I appreciate.

    Thanks
    PK

  18. #18
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,354

    Re: ADO Querying a table based on a field without a key

    I would add a int column as the Clustered PK and create an unique nonclustered index on that field. Making a varchar field that can hold anything is not a create choice for a clustering key in my humble opinion. The PK is normally created as clustered and since it is a varchar that mean it will always try and order the table as per the value in the field. Since it could have a value between two other values already existing it will cause page fragmentation and will ultimately result in degraded performance
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  19. #19

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    373

    Re: ADO Querying a table based on a field without a key

    Gary,

    Thanks. I am not well versed in keys and indexes.
    Presently I have an 'ID' field which must be unique and then the 'foldername' field which must be unique.
    The problem I have is that this table holds the folders and subfolders of the server, which may be 1000 maximum. I, however, need space between foldernames and numbers to push in folders and subfolders alphabetically.
    I would have number 1 to 10 as first level parent folders, then 1000 to 1100 as second level, then 10000 to 11000 as third level and so on, most of them accommodating vacancies which can be added later.
    The best I have now are indices on 'ID' and 'foldername'. Although they need to be unique, they should accept any entry in both fields including nulls consistent with being unique.
    If you have a good proposal I would like to hear.
    It may sound crazy, but the filesystem objects does not have security on the server files and folders, so I need to set up a parallel table to keep tabs on the userlevel (1 to 9) which may enter the folder or view the file.

    PK

  20. #20
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,354

    Re: ADO Querying a table based on a field without a key

    There is no need to do that.... The order comes back as you specify with an order by clause it makes no difference as to how it is stored on disk
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width