Passing an variable to within an sql statement

Discussion in 'ASP General' started by I.am.the.Buddha@gmail.com, Apr 24, 2006.

  1. Guest

    I am still learning asp and sql.
    I am having trouble with passing a variable to within an sql statement.
    I am sure it is something simple like misuse of quotes.

    Since it may help if i say what i am trying to generally accomplish
    here.
    This is an access database for products and customers. We have a
    counter for each product (such as ProdCountLB, ProdCountSH, etc for the
    field names in a table called ProdCount).
    This worked fine when i explicitly addressed them. Now that i have made
    the explicit names of the field into a variable (ProdCountVar), I need
    to use it in the SQL statment.
    It fails at this point an di have tried several variations of quotes
    and ampersands to no avail.

    Any suggestions? Thank you
    Here is a snippet of the code.
    (pCode is variabel that gives teh vatule of LB, SH..etc and is combined
    with the phrase ProdCount to form ProdCountVar)

    Dim OBJdbConnection, ProdCountVar

    ProdCountVar=ProdCount&pCode

    Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
    OBJdbConnection.Open "DSN=tbe"

    CountQuery = "SELECT * FROM ProdCount "
    Set CQS = OBJdbConnection.Execute(CountQuery)
    ProdCounts = CQS(&ProdCountVar&)
    NewProdCount = ProdCounts+1
    CountBack = "UPDATE ProdCount SET
    "&ProdCountVar&"='"&NewProdCount&"' WHERE CountIdent=1"
     
    , Apr 24, 2006
    #1
    1. Advertising

  2. Mike Brind Guest

    wrote:
    > I am still learning asp and sql.
    > I am having trouble with passing a variable to within an sql statement.
    > I am sure it is something simple like misuse of quotes.
    >
    > Since it may help if i say what i am trying to generally accomplish
    > here.
    > This is an access database for products and customers. We have a
    > counter for each product (such as ProdCountLB, ProdCountSH, etc for the
    > field names in a table called ProdCount).
    > This worked fine when i explicitly addressed them. Now that i have made
    > the explicit names of the field into a variable (ProdCountVar), I need
    > to use it in the SQL statment.
    > It fails at this point an di have tried several variations of quotes
    > and ampersands to no avail.
    >
    > Any suggestions? Thank you
    > Here is a snippet of the code.
    > (pCode is variabel that gives teh vatule of LB, SH..etc and is combined
    > with the phrase ProdCount to form ProdCountVar)
    >
    > Dim OBJdbConnection, ProdCountVar
    >
    > ProdCountVar=ProdCount&pCode


    If ProdCount is a phrase (string) it should be in quotes

    >
    > Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
    > OBJdbConnection.Open "DSN=tbe"
    >
    > CountQuery = "SELECT * FROM ProdCount "


    You don't need to select all columns at all, just the one you one to
    update, which is assigned to the variable ProdCountVar. You have not
    specified which row you want to return, so all rows will be returned.
    When you reference the record in the line after next to increment the
    value by 1, it will only work with the first record returned - every
    time.

    > Set CQS = OBJdbConnection.Execute(CountQuery)
    > ProdCounts = CQS(&ProdCountVar&)


    You really need to leave spaces around your ampersands. Not essential,
    but it makes it so much easier to read.

    > NewProdCount = ProdCounts+1
    > CountBack = "UPDATE ProdCount SET
    > "&ProdCountVar&"='"&NewProdCount&"' WHERE CountIdent=1"


    NewProdCount is a number, but you have delimited it as a string.


    Try this:

    <%

    ProdCountVar = "ProdCount" & pCode

    Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
    OBJdbConnection.Open "DSN=tbe"

    CountQuery = "SELECT " & ProdCountVar & " FROM ProdCount WHERE
    CountIdent=1"
    Set CQS = OBJdbConnection.Execute(CountQuery)
    NewProdCount = CQS(" & ProdCountVar & ")+1
    CountBack = "UPDATE ProdCount SET " & ProdCountVar & " = " &
    NewProdCount & " WHERE CountIdent=1"

    %>

    --
    Mike Brind
     
    Mike Brind, Apr 25, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Patrice
    Replies:
    1
    Views:
    1,664
    =?Utf-8?B?REZyYW5rbHlu?=
    May 13, 2004
  2. Froefel
    Replies:
    1
    Views:
    790
    Mark Rae [MVP]
    Jul 4, 2007
  3. Julia Jacobson
    Replies:
    2
    Views:
    1,056
    Gregory Ewing
    Aug 30, 2010
  4. billb

    passing variable to sql statement in asp.net 2.0?

    billb, Jul 10, 2006, in forum: ASP .Net Web Controls
    Replies:
    1
    Views:
    188
    Alessandro Zifiglio
    Jul 12, 2006
  5. Thomas
    Replies:
    6
    Views:
    122
    Joost Diepenmaat
    Aug 26, 2008
Loading...

Share This Page