Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Unanswered: sql with parameter

    Hello!

    I work with Access, Vis. Studio 2005, VB.

    I have a sub with parameter CName.
    This parameter is part of sql:

    "SELECT * FROM Users WHERE Users.Name = 'CName'".

    I'm trying CName with and without '. In both cases I got errors.

    Please help.

  2. #2
    Join Date
    Nov 2005
    Posts
    113
    What is 'CName'?
    Is it a textfeld?
    Try this:
    "SELECT * FROM Users WHERE Users.Name Like '" & "'" & CName & "';"

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    This is probably an issue more related to the programming environment you're using, but it seems you need to include the value of the variable to the SQL, not the name of it.
    Code:
    "SELECT * FROM Users WHERE Users.Name = '" & CName & "'"
    Should you have challenges with single quotes within this name, the VBA version could be
    Code:
    "SELECT * FROM Users WHERE Users.Name = '" & Replace(CName, "'", "''") & "'"
    Roy-Vidar

  4. #4
    Join Date
    Jan 2007
    Posts
    7
    Bingo dear Roy-Vidar!

    I am new in VB, could you explain more why
    "SELECT * FROM Users WHERE Users.Name = CName".

    is not a variable, why should I use Replace.

    Thanks a lot

  5. #5
    Join Date
    Jan 2007
    Posts
    7
    Hello Julita!

    Thank You, but your solution doesnt work. The right solution you may find here by Roy-Vidar.

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    "SELECT * FROM Users WHERE Users.Name = 'CName'" - is a string. If this is assigned to a variable

    CName = "test"
    strSql = "SELECT * FROM Users WHERE Users.Name = 'CName'"

    Then you could do

    debug.print strSQL

    and the result in the immediate pane (ctrl+g), would be

    SELECT * FROM Users WHERE Users.Name = 'CName'

    You need to concatenate values of the variable into the SQL

    CName = "test"
    strSql = "SELECT * FROM Users WHERE Users.Name = '" & CName & "'"
    debug.print strSQL
    SELECT * FROM Users WHERE Users.Name = 'test'

    The replace, single quotes are text delimiters - they are needed when you pass a litteral to a text field. Should there be any occurances of single quote within the string, they would need to be doubled up, for Jet to accept it.

    I e, assuming the above assignement

    CName = "O'Brian"

    would give syntax error when executed, while

    CName = "O''Brian" ' two single quotes between O and B

    would work, hence the Replace to make such changes dynamic.
    Roy-Vidar

  7. #7
    Join Date
    Jan 2007
    Posts
    7

    Just thank You so much

    Thank thank You so much.
    I took this ex. from the book "Programming Micrososft Visual Basic .NET for Microsoft Access Databases by Rick Dobson ".
    You know I tried to convince him that there is (are) mistakes in his book, but he says he is a busy man.

    Thank You again.

Posting Permissions

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