Welcome to WebmasterWorld Guest from 54.242.105.196

Forum Moderators: ocean10000

INSERTing records into the database

syntax error?

   
1:23 pm on May 8, 2006 (gmt 0)

10+ Year Member



I am inserting records into a database of links being clicked on certain pages of my site.

I use the following code, which inserts a record of every link (a href) clicked on the page:

<%@ Language=VBScript %>
<!--#include file="config.asp" -->
<%
'Resume on error, last if/then err test will handle errors
On Error Resume Next

'Set response properties
Response.ContentType="text/xml"
Response.Charset="utf-8"

'URL querystring parameters
InputURL = Request.QueryString("url")
InputLabel = Request.QueryString("label")
InputLinkID = Request.QueryString("id")
InputTarget = Request.QueryString("target")

'Build Query that inserts the click into data file (without inserting date, as set as default in DB)
sSQL = "INSERT INTO LinkClickThroughs " & _
"(sURL, sLabel, sLinkID, sTarget) " & _
"VALUES " & _
"(""" & InputURL & """,""" & InputLabel & """,""" & InputLinkID & """,""" & InputTarget & """);"

'Create connection to database and fill recordset with query contents
set Conn = server.createobject("ADODB.Connection")
Conn.Open connectString
Conn.Execute sSQL
Conn.close
set Conn = nothing

'Test for any errors.
If Err Then
'There was an error
Response.Write("<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" & vbCrLf)
Response.Write("<rsp stat=""fail"">" & vbCrLf)
Response.Write("<err code=""100"" msg=""Request not complete"" />" & vbCrLf)
Response.Write("</rsp>" & vbCrLf)
Else
'Record was added without error
Response.Write("<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" & vbCrLf)
Response.Write("<rsp stat=""ok"">" & vbCrLf)
Response.Write("<url>""" & InputURL & """</url>" & vbCrLf)
Response.Write("<id>""" & InputLinkID & """</id>" & vbCrLf)
Response.Write("<target>""" & InputTarget & """</target>" & vbCrLf)
Response.Write("<label>""" & InputLabel & """</label>" & vbCrLf)
Response.Write("</rsp>")
End If
%>

The problem with this is that it is writing thousands of records a day to the database.

I only want to track certain links, so applied an IF statement, however this does not appear to work, so I am wondering if I have gone wrong somewhere....

The IF statement is as follows (contained in the same code as above...)

If InputTarget = "http://www.example.com/example.htm" OR InputTarget = "http://www.example.com/example1.htm" & _
OR InputTarget = "http://www.example.com/example2.htm" OR InputTarget = "http://www.example.com/example3.htm" & _
OR InputTarget = "http://www.example.com/example4.htm" Then
'Build Query that inserts the above ONLY clicks into data file (without inserting date, as set as default in DB)
sSQL = "INSERT INTO LinkClickThroughs " & _
"(sURL, sLabel, sLinkID, sTarget) " & _
"VALUES " & _
"(""" & InputURL & """,""" & InputLabel & """,""" & InputLinkID & """,""" & InputTarget & """);"

'Create connection to database and fill recordset with query contents
set Conn = server.createobject("ADODB.Connection")
Conn.Open connectString
Conn.Execute sSQL
Conn.close
set Conn = nothing
End If

Is there a problem with this code, and the positioning of the IF statement, or is it something that I am overlooking.

Thanks for looking

Graham

2:25 pm on May 9, 2006 (gmt 0)

10+ Year Member



I see one thing. In the database, your InputLinkID field, is it a varchar or int column? If int, take the quotes out from around the variable. Also, what is the error you are receiving?
2:39 pm on May 9, 2006 (gmt 0)

10+ Year Member



macrost

Thanks for looking, however I think I have just resolved the issue.

I had inadvertanly place & within my SQL statement:

If InputTarget = "http://www.example.com/example.htm" OR InputTarget = "http://www.example.com/example1.htm" & _ 
OR InputTarget = "http://www.example.com/example2.htm" OR InputTarget = "http://www.example.com/example3.htm" & _
OR InputTarget = "http://www.example.com/example4.htm" Then

rather than just adding _ (underscores) for the line continuation.

This appears to have fixed the issue, and the database is inserting only the records that I want it to.

Graham

 

Featured Threads

Hot Threads This Week

Hot Threads This Month