By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 1,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Passing an variable to within an sql statement

P: n/a
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 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

I.*************@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

Apr 25 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.