Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    1

    Unanswered: Access Database Query with Question Mark .NET

    I realize this should be easy.

    However how do you access a query like the following from .NET?

    This is a Query in MS Access 2003 or 2007 that contains a Question Mark (unnamed parameter). Let's assume it is named 'GetStuffromDudes'

    Here is the raw SQL: SELECT avail.MLS_CODE, avail.AV_DESCAD, CLASS.CL_DESC, CLASS.CL_DESCSUB, customer.CP_NAME, customer.CP_EMAIL, MFG.MF_NAME, avail.AV_DESCLN, avail.AV_UED, avail.AV_MACLASS, avail.AV_MATYPE
    FROM ((avail LEFT JOIN MFG ON avail.AV_MFG = MFG.MF_CODE) INNER JOIN CLASS ON (avail.AV_MACLASS = CLASS.CL_CLASS) AND (avail.AV_MATYPE = CLASS.CL_TYPE)) INNER JOIN customer ON avail.AV_KOOPER = customer.CP_KOOPER
    WHERE (((avail.MLS_CODE)=[?]))
    ORDER BY avail.MLS_CODE;

    In .NET how do I call 'GetStuffFromDudes' and add in the unnamed parameter and then retreive data?

    I've tried the AccessDataSource class. I also tried OleDbConnection/OleDBCommand objects, and then tried adding a parameter. But neither had success...

    Edit:
    I don't seem to be able to delete this, but I figured it out. The issue was with something else.
    I used OleDBCommand object and then have it be a Stored Procedure Type. Then add an unnamed parameter to it... waalaah.


    Robert
    Last edited by robertkjr3d; 10-31-10 at 20:33. Reason: I figured this out

  2. #2
    Join Date
    Sep 2006
    Posts
    265
    You could also implement QueryStrings which create unique URL's. Sometimes is is handy to create Session variables using a QueryString.

    Simon

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as this isn't strictly speaking an Access question it could be a problem
    does the .net connection allow you to build filters, parameters or whatever they decide to call them
    ..so I'd want to look at the available properties of the .NET object
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    You use the QueryString and put this into your SQL statement thus:

    Code:
    ... FROM ArtistsQuery WHERE ArtistsQuery.[Artist] = '" & Request.QueryString("Artist") & "' "
    Simon

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Please do not ever pass unsanitized values to your persistence layer. Always assume any information coming from the end user is not to be trusted.

    Anyway, looks like the original poster figured it out...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2006
    Posts
    265
    If you follow that logic, you wouldn't give users a computer!

    Simon

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, my job would be quite a bit easier if it weren't for those pesky users...

    But seriously, it is well advised to always assume any external input could have malicious intent and you must defend yourself accordingly. Opting for a prepared statement vs unsanitized inline SQL goes a LOOOONG way towards that end. Failure to do so is an open invitation for shifty eyed hooligans to see what manner of hijinks they can pull off by way of sql injection. On that note, in my "security curious" days, rarely would I stumble upon a site with wide open sql injection vulnerabilities that hadn't ALREADY been compromised before I ever got there. Food for thought...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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