entering a query value to an INSERT INTO statement

Hi,

Wonder if any of you have time to assist a newbie.
I am trying to record (to an Access database) the number of clicks on
my asp page :

'insertStr= "INSERT INTO clicktrack (ItemID, ClickIP, ClickRefer)
VALUES ((SELECT ItemID FROM DocumentLibrary WHERE ItemLink = '" &link&
"'),'" & referer & "','" & strReferrer & "')"

the ItemID value should be an integer, taken from a table named
'DocumentLibrary' that has two columns : 'ItemID' and 'ItemLink'

Obviously the above syntax is incorrect. Should I be making two lines
of SQL statement instead? Then my next question would follow: how can I
do that? I have been learning SQL by myself through the internet, and I
have not found any example on how to execute two lines of SQL statement
in ASP.

Any help is appreciated.

Thanks,
Rin

Re: entering a query value to an INSERT INTO statement

rin.wijaya@gmail.com wrote:
> Hi,
>
> Wonder if any of you have time to assist a newbie.
> I am trying to record (to an Access database) the number of clicks on
> my asp page :
>
> 'insertStr= "INSERT INTO clicktrack (ItemID, ClickIP, ClickRefer)
> VALUES ((SELECT ItemID FROM DocumentLibrary WHERE ItemLink = '" &link&
> "'),'" & referer & "','" & strReferrer & "')"

>
> Obviously the above syntax is incorrect. Should I be making two lines
> of SQL statement instead?

No.
You can't combine VALUES and SELECT. VALUES () is used for providing a list
of, well, values to be inserted. Since you wish to append data retrieved
from another table, simply use the INSERT INTO ... SELECT syntax. A SELECT
statement can be used to provide calculated columns as well as table
columns. Try this sql statement in the Access Query Builder:

SELECT ItemID,'A' as [ConstantValue] FROM DocumentLibrary

Additionally, your use or dynamic sql is leaving you vulnerable to hackers
using sql injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

>
> the ItemID value should be an integer, taken from a table named
> 'DocumentLibrary' that has two columns : 'ItemID' and 'ItemLink'

Here is how i would run your query (you can eliminate my comments from this
code sample if you like):

insertStr= "INSERT INTO clicktrack (ItemID, ClickIP, ClickRefer) " & _
SELECT ItemID,?,? FROM DocumentLibrary WHERE ItemLink =?"

'See how this simplifies matters? For one thing, there is no
'need to worry about delimiters ...
'Now, look at the order in which the parameter markers (?) occur in.
'the sql string. You need
'to provide the parameter values in the same order in which they appear
'in your sql string - the values are provided using a variant array:

dim arParms
arParms=Array(referer, strReferrer, link)

'Now create a Command object and set its properties:
dim cmd
set cmd=createobject("adodb.command")
With cmd
.CommandText=insertStr
set .ActiveConnection=conn 'a previously-opened connection
.Execute ,arParms,129
'129 is the combination of 2 ADO constants:
' adCmdText (1) - always tell ADO the command type
' adExecuteNoRecords (128) - tell ADO not to create a recordset
End With
Set cmd=Nothing
conn.close: set conn=nothing


> Then my next question would follow: how can
> I do that? I have been learning SQL by myself through the internet,
> and I have not found any example on how to execute two lines of SQL
> statement in ASP.
>
Jet (Access), unlike SQL Server, does not support the execution of batched
queries, even if it was relevant for your situation. The best you can do is
execute your statments one at a time.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"