|
Home > Archive > IIS ASP > April 2006 > Passing an variable to within an sql statement
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Passing an variable to within an sql statement
|
|
| I.am.the.Buddha@gmail.com 2006-04-27, 7:52 am |
| 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 2006-04-27, 7:52 am |
|
I.am.the.Buddha@gmail.com 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
|
|
|
|
|