I am working in Webmatrix trying to use a db to keep track of visitors to 2 pages on my site using their ip address. (i know cookies would be better to id unique visitors).
I have set up a table with 6 columns...id, visitor ip, and 4 columns to hold number of visits and last date each of the 2 page was visited by the ip. All colums are type nchar.
I am lead to believe that a visitor's ip may be behind a proxy ip on occasion so request two server varible ip values and if two are returned use the one that is most likely the visitors but if only one is returned use it. That part works ok.
Next I query the db to see if ip exits in my db and if it does put the record values in var's initialize to "".
Next If the ip is not in my db I ATTEMPT to add it but get the following parsing error when I try to run for test:
--------------------------------------------
There was an error parsing the query. [ Token line number = 1,Token line offset = 113,Token in error = ) ]
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 1,Token line offset = 113,Token in error = ) ]
Source Error:
Line 60: var SQLINSERT = "INSERT INTO SitesVisits ( SiteVisitCount, CensoredCount, DateHomePageVisit, DateCensoredGalleryVisit, VisitorIP,) VALUES ((@0, @1, @2, @3, @4)";
Line 61: var db = Database.Open("dbname");
Line 62: db.Execute(@SQLINSERT, ipPerServer, FirstVisitCount, CGVisitCount, MonthDayYear, CGVisitDate, UserIP); Line 63:
---------------------------
I have no idea what is going on and can find nothing of help doing a goolgle search of all or pieces of the error statement. I originally had a couple columns in my table as type int and one as type date but have changed all to a the more generic type nchar
to trying to find the problem but nothing I have done helps.
I have put in a few lines to print results as I work down thru the code being sure one part works before moving onto the next. I have commented out the else if second part of the if statement where in the error occures until i can get the first part working.
I think some of the code is unnecessarily redundent and will clean up if I can get the damn thing to run. In the copy of my code I have substited "dbname" for the actual name of my data base.
What seems like it should be a simple bit of coding has turned into a major headache and I have no idea what else to try. I hope someone can point me in the direction of a solution.
Thanks, Richard.
@{
// to record dateoflast visit and number of visits to site by an ip
//FOLLOWING 2 LINES ..USE FOR TESTING .. COMMENT OUT WHEN UPLOADED
var ServerProxyIP=" 75.70.67.15";
var ServerUserIP="";
// FOLLOWING 2 LINES ..COMMENT OUT FOR TESTING .. USE WHEN UPLOADED
//var ServerProxyIP= Request.ServerVariables["REMOTE_ADDR"];
//var userip=Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
// get current date in format Month/Day/year
var MonthDayYear= DateTime.Now.ToShortDateString();
var ipPerServer="";
var UserID="";
var UserIP="";
var HPVisitCount="";
var CGVisitCount="";
var HPVisitDate="";
var CGVisitDate="";
if(String.IsNullOrEmpty(ServerProxyIP) & String.IsNullOrEmpty(ServerUserIP)){
ipPerServer=""; // no ip avail from sever
}else if(String.IsNullOrEmpty(ServerUserIP)){
ipPerServer=ServerProxyIP; //ServerUserIP var is empty use proxyip
//<p> is Server Proxy ip @ipPerServer</p>
}else{
ipPerServer=ServerUserIP; //ServerProxyIP is avail...use it
//<p> is Server User ip @ipPerServer</p>
}
if(ipPerServer != ""){ // it IP was obtained from server
// open db and find record matching ipPerServer if it exists
var db = Database.Open("dbname");
var selectQueryString = "SELECT * FROM SiteVisits WHERE VisitorIP = @ipPerServer";
var data = db.Query(selectQueryString);
foreach(var row in data){
UserID =@row.id;
HPVisitCount = @row.SiteVisitCount;
CGVisitCount = @row.CensoredCount;
HPVisitDate = @row.DateHomePageVisit;
CGVisitDate = @row.CensoredGalleryVisit;
UserIP = @row.VisitorIP;
}
if (UserID==""){
<p>current visitor ip not found in db @UserIP</p>
} else {
<p>current visitor ip found in db @UserIP</p>
}
}
if(UserIP==""){ //first visit for user with ipPerServer - enter new record in db
var FirstVisitCount = "1";
var SQLINSERT = "INSERT INTO SitesVisits ( SiteVisitCount, CensoredCount, DateHomePageVisit, DateCensoredGalleryVisit, VisitorIP,) VALUES ((@0, @1, @2, @3, @4)";
var db = Database.Open("dbname");
db.Execute(@SQLINSERT, ipPerServer, FirstVisitCount, CGVisitCount, MonthDayYear, CGVisitDate, UserIP);
@*} else if(FileVisitDate != MonthDayYear){ //user in db - if date of last visit not today update date and interate visit count
var UPDATERECORD = "UPDATE SitesVisits (DateHomePageVisit=@1, SiteVisitCount=@2) WHERE id=@0";
var incVisitCount = numberofpagevisits + 1;
var db = Database.Open("dbname");
db.Execute(@UPDATERECORD, UserID, MonthDayYear, incVisitCount );
*@
}
This will loop through every row in the result and only when it has finished will it compare the UserID value (generated from the last row) to an empty string. I'm not sure that's actually what you intend?
Thank You ignatandrei
and Mike for pointing out my errors. By Correcting the things you found and a couple others I was able to get the code to do what I wanted. As I am sure you can tell I am a DIYer just dabbling in a bit of site building most of which is a cut and paste
adaptation of code snippets from here and there. I guess in my old age I am not as on top of the little nuances and don't notice when an extra charater that makes a world of difference in what happens ends up in my code. Thanks for pointing out some of them.
Thanks you Very Much for sharing your time and expertise!
MisterRPH
Member
13 Points
10 Posts
parsing error in db EXECUTE
Jan 07, 2012 07:33 PM|LINK
I am working in Webmatrix trying to use a db to keep track of visitors to 2 pages on my site using their ip address. (i know cookies would be better to id unique visitors).
I have set up a table with 6 columns...id, visitor ip, and 4 columns to hold number of visits and last date each of the 2 page was visited by the ip. All colums are type nchar.
I am lead to believe that a visitor's ip may be behind a proxy ip on occasion so request two server varible ip values and if two are returned use the one that is most likely the visitors but if only one is returned use it. That part works ok.
Next I query the db to see if ip exits in my db and if it does put the record values in var's initialize to "".
Next If the ip is not in my db I ATTEMPT to add it but get the following parsing error when I try to run for test:
--------------------------------------------
There was an error parsing the query. [ Token line number = 1,Token line offset = 113,Token in error = ) ]
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 1,Token line offset = 113,Token in error = ) ]
Source Error:
Line 60: var SQLINSERT = "INSERT INTO SitesVisits ( SiteVisitCount, CensoredCount, DateHomePageVisit, DateCensoredGalleryVisit, VisitorIP,) VALUES ((@0, @1, @2, @3, @4)"; Line 61: var db = Database.Open("dbname"); Line 62: db.Execute(@SQLINSERT, ipPerServer, FirstVisitCount, CGVisitCount, MonthDayYear, CGVisitDate, UserIP);Line 63:
---------------------------
I have no idea what is going on and can find nothing of help doing a goolgle search of all or pieces of the error statement. I originally had a couple columns in my table as type int and one as type date but have changed all to a the more generic type nchar to trying to find the problem but nothing I have done helps.
I have put in a few lines to print results as I work down thru the code being sure one part works before moving onto the next. I have commented out the else if second part of the if statement where in the error occures until i can get the first part working. I think some of the code is unnecessarily redundent and will clean up if I can get the damn thing to run. In the copy of my code I have substited "dbname" for the actual name of my data base.
What seems like it should be a simple bit of coding has turned into a major headache and I have no idea what else to try. I hope someone can point me in the direction of a solution.
Thanks, Richard.
@{
// to record dateoflast visit and number of visits to site by an ip
//FOLLOWING 2 LINES ..USE FOR TESTING .. COMMENT OUT WHEN UPLOADED
var ServerProxyIP=" 75.70.67.15";
var ServerUserIP="";
// FOLLOWING 2 LINES ..COMMENT OUT FOR TESTING .. USE WHEN UPLOADED
//var ServerProxyIP= Request.ServerVariables["REMOTE_ADDR"];
//var userip=Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
// get current date in format Month/Day/year
var MonthDayYear= DateTime.Now.ToShortDateString();
var ipPerServer="";
var UserID="";
var UserIP="";
var HPVisitCount="";
var CGVisitCount="";
var HPVisitDate="";
var CGVisitDate="";
if(String.IsNullOrEmpty(ServerProxyIP) & String.IsNullOrEmpty(ServerUserIP)){
ipPerServer=""; // no ip avail from sever
}else if(String.IsNullOrEmpty(ServerUserIP)){
ipPerServer=ServerProxyIP; //ServerUserIP var is empty use proxyip
//<p> is Server Proxy ip @ipPerServer</p>
}else{
ipPerServer=ServerUserIP; //ServerProxyIP is avail...use it
//<p> is Server User ip @ipPerServer</p>
}
if(ipPerServer != ""){ // it IP was obtained from server
// open db and find record matching ipPerServer if it exists
var db = Database.Open("dbname");
var selectQueryString = "SELECT * FROM SiteVisits WHERE VisitorIP = @ipPerServer";
var data = db.Query(selectQueryString);
foreach(var row in data){
UserID =@row.id;
HPVisitCount = @row.SiteVisitCount;
CGVisitCount = @row.CensoredCount;
HPVisitDate = @row.DateHomePageVisit;
CGVisitDate = @row.CensoredGalleryVisit;
UserIP = @row.VisitorIP;
}
if (UserID==""){
<p>current visitor ip not found in db @UserIP</p>
} else {
<p>current visitor ip found in db @UserIP</p>
}
}
if(UserIP==""){ //first visit for user with ipPerServer - enter new record in db
var FirstVisitCount = "1";
var SQLINSERT = "INSERT INTO SitesVisits ( SiteVisitCount, CensoredCount, DateHomePageVisit, DateCensoredGalleryVisit, VisitorIP,) VALUES ((@0, @1, @2, @3, @4)";
var db = Database.Open("dbname");
db.Execute(@SQLINSERT, ipPerServer, FirstVisitCount, CGVisitCount, MonthDayYear, CGVisitDate, UserIP);
@*} else if(FileVisitDate != MonthDayYear){ //user in db - if date of last visit not today update date and interate visit count
var UPDATERECORD = "UPDATE SitesVisits (DateHomePageVisit=@1, SiteVisitCount=@2) WHERE id=@0";
var incVisitCount = numberofpagevisits + 1;
var db = Database.Open("dbname");
db.Execute(@UPDATERECORD, UserID, MonthDayYear, incVisitCount );
*@
}
<p>@DateTime.Now ----- @MonthDayYear</p>
<p>@ServerProxyIP @ServerUserIP</p>
}
ignatandrei
All-Star
135023 Points
21648 Posts
Moderator
MVP
Re: parsing error in db EXECUTE
Jan 07, 2012 07:43 PM|LINK
at least 2 error spotted fast
1. What's this comma here?
2.You open 2 paranthesis, close one.
Mikesdotnett...
All-Star
154915 Points
19866 Posts
Moderator
MVP
Re: parsing error in db EXECUTE
Jan 07, 2012 09:26 PM|LINK
Some more errors:
That should be:
if(String.IsNullOrEmpty(ServerProxyIP) && String.IsNullOrEmpty(ServerUserIP)){ //double ampersand
Improvement:
You can use the IsEmpty() extension method for clarity:
if(ServerProxyIP.IsEmpty() && ServerUserIP.IsEmpty()){
The Database helper doesn't recognise named parameters. You must use numbers starting at 0:
var selectQueryString = "SELECT * FROM SiteVisits WHERE VisitorIP = @0";
You must pass the parameter value in as part of the Database.Query call:
var data = db.Query(selectQueryString, ipPerServer);
This will loop through every row in the result and only when it has finished will it compare the UserID value (generated from the last row) to an empty string. I'm not sure that's actually what you intend?
Remove the @ sign prior to the SQLINSERT variable:
db.Execute(SQLINSERT, ipPerServer, FirstVisitCount, CGVisitCount, MonthDayYear, CGVisitDate, UserIP);
I'd recommend starting again, in all honesty. Add each piece of logic a bit at a time, and test it as you go. That way, if it breaks, you know where.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
MisterRPH
Member
13 Points
10 Posts
Re: parsing error in db EXECUTE
Jan 08, 2012 04:51 AM|LINK
Thank You ignatandrei and Mike for pointing out my errors. By Correcting the things you found and a couple others I was able to get the code to do what I wanted. As I am sure you can tell I am a DIYer just dabbling in a bit of site building most of which is a cut and paste adaptation of code snippets from here and there. I guess in my old age I am not as on top of the little nuances and don't notice when an extra charater that makes a world of difference in what happens ends up in my code. Thanks for pointing out some of them.
Thanks you Very Much for sharing your time and expertise!
Richard