Passing an variable to within an sql statement

[I.am.the.Buddha]

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"

 

[Mike Brind]

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"

%>