Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    33

    Unanswered: Parameterised Queries

    Hello,

    I am using ADO 2.8 and am trying to pass parameters to a query I have written. I am using a parameterised query as the parameter comes from user input, so this will protect is against SQL injection. The code is:


    -----------------------------------------------------
    Dim Cmd As ADODB.Command
    Dim para As ADODB.Parameter

    Set Cmd = New ADODB.Command

    Cmd.CommandType = adCmdText
    Cmd.NamedParameters = True
    Cmd.CommandText = "" & _
    "SELECT PersonName " & _
    "FROM People " & _
    "WHERE PersonName LIKE @PersonLike"

    Set personlike = New ADODB.Parameter
    personlike.Name = "@GroupLike"
    personlike.Type = adVarChar
    personlike.Size = 255
    personlike.Value = Me.TextBox1 'User Input
    personlike.Direction = adParamInput


    Cmd.Parameters.Append personlike

    Rs.Open Source:=Cmd, CursorType:=adOpenDynamic, LockType:=adLockReadOnly
    ----------------------------------------------------------------------

    The problem I encounter is that I am told the parameter @PersonLike needs to be declared, and the only way I get around it is changing "@PersonLike" to "?" in the SQL statement. For this query, this isn't too much of an issue, but for more complex queries with several parameters, using a question mark for each on will make the SQL difficult to read and maintain.

    Does anyone know how I can link parameters to their position in the SQL statement by name?

    Thanks,

    Mike

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    personlike.Name = "@GroupLike"
    Copy and paste error?
    George
    Home | Blog

  3. #3
    Join Date
    May 2008
    Posts
    33
    Yes! Sorry, Code should read:

    -----------------------------------------------------
    Dim Cmd As ADODB.Command
    Dim para As ADODB.Parameter

    Set Cmd = New ADODB.Command

    Cmd.CommandType = adCmdText
    Cmd.NamedParameters = True
    Cmd.CommandText = "" & _
    "SELECT PersonName " & _
    "FROM People " & _
    "WHERE PersonName LIKE @PersonLike"

    Set personlike = New ADODB.Parameter
    personlike.Name = "@PersonLike"
    personlike.Type = adVarChar
    personlike.Size = 255
    personlike.Value = Me.TextBox1 'User Input
    personlike.Direction = adParamInput


    Cmd.Parameters.Append personlike

    Rs.Open Source:=Cmd, CursorType:=adOpenDynamic, LockType:=adLockReadOnly
    ----------------------------------------------------------------------

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you stop right now and start using stored procedures, you be way ahead rather than trying to keep doing this
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2008
    Posts
    33
    Hello, why exactly are stored procedures better? I have read up quite a lot on stored procedures vs parameterised queries and it just seems that they are pretty much on an equal footing, with the main difference being an organisational issue.

    If the query is application specific, why create a stored procedure? Just embed the query in the application and save having hundreds of stored procedures in the database.

    As a reference:
    http://www.theserverside.net/news/th...hread_id=31953

    Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can't find the article I was thinking of that Adam wrote but this is one.
    http://sqljunkies.com/WebLog/amachan...0/26/4823.aspx Same thing - organisational. Saving hundreds of stored procedures in the database assumes that this is a goal of good design and also does not account for the hundreds of decentralised embedded SQL stataments.

    Can't help on the ADO - I use sprocs

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Michael - just on a point of netiquette - please could you link to any cross posts you have going on? People often put in work to help and some of this can be a waste of their time if the same work has already been done elsewhere.
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103152
    Please let us know if you have this on other forums.
    Please also post any conclusions\ solutions on all forums.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by michael.appleton
    Hello, why exactly are stored procedures better? I have read up quite a lot on stored procedures vs parameterised queries and it just seems that they are pretty much on an equal footing, with the main difference being an organisational issue.

    If the query is application specific, why create a stored procedure? Just embed the query in the application and save having hundreds of stored procedures in the database.

    As a reference:
    http://www.theserverside.net/news/th...hread_id=31953

    Thanks
    That article makes a few incorrect assumptions of its own. There is a middle ground between creating a stored procedure for SELECT/UPDATE/INSERT for every single table on an individual basis which exposes every single field, and creating stored procedures that represent logical elements of your BLL. It is completely asinine to assume that the only possible implementation for stored procedures is to replace direct DML statements against a single table. I've never seen any system, anywhere, that implements what he's proposing as a typical sproc-based DAL.

    Furthermore, it is trivial to create optional parameters within a stored procedure, I have no idea where he got that from.


    In this case, a sproc would fit the bill just fine. It could be my affinity for the ASP.NET provider model, but this looks like a good candidate for a "GetUserByName" method and corresponding sproc.
    Last edited by Teddy; 05-20-08 at 12:26.
    oh yeah... documentation... I have heard of that.

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

  9. #9
    Join Date
    May 2008
    Posts
    33
    Sorry pootle flump, not linking the threads was selfish of me.

    I also asked the question on a VB forum:

    http://www.vbforums.com/showthread.p...11#post3233111

    Apparently, the names of the parameters don't matter when running the query, only the order they are appended in, so my problem of not using names is essentially irrelivent.

    As always, thanks for the help.

Posting Permissions

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