homepage Welcome to WebmasterWorld Guest from 174.129.103.100
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
INSERTing records into the database
syntax error?
Graham




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

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

 

macrost




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

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?

Graham




msg:945975
 2:39 pm on May 9, 2006 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved