I am aware there are some intricacies when connecting to an access database, but to me this one does not make sense.
I have an access database that I am querying from ASP.NET using C# to find the sum of all Byte values for a specified date range for a specified user. The database is fairly simple consisting of only one table that is periodically written to by a VBScript that gets the accesslog data from a text file that my proxy server writes to.
The problem I am facing is that when the I call the TCDSCmd.ExecuteScalar().ToString() method from ASP.NET it returns a Null value, However if I do a Response.Write with the SQL Query string and then copy and paste that into an access query it returns the correct value. Please find the code below.
string TotalCacheDataServed(string user, string startdateUtc, string enddateUtc)
{
string TCDSProv = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("SquidLogs.mdb");
string TCDSSql = "SELECT SUM(Bytes) FROM squidaccesslog where Ident='" + user +
"' AND TimeStamp BETWEEN #" + startdateUtc + "# AND #" + enddateUtc +
"# AND ActionCode LIKE '*HIT*'";
OleDbConnection TCDSConn = new OleDbConnection(TCDSProv);
OleDbCommand TCDSCmd = new OleDbCommand(TCDSSql, TCDSConn);
TCDSCmd.Connection.Open();
string TCDSstr = TCDSCmd.ExecuteScalar().ToString();
double TCDSdbl = double.Parse(TCDSstr);
TCDSCmd.Connection.Close();
return TCDSdbl;
}
If I modify this code to write the string variable "TCDSSql" to an <asp:label id="SqlQuery" runat="server" /> I get the following result:
SELECT SUM(Bytes) FROM squidaccesslog where Ident='lindsaybit\brett' AND TimeStamp BETWEEN #03/04/2005 6:18:00 AM# AND #03/08/2005 6:18:00 AM# AND ActionCode LIKE '*HIT*'
This query when run from MS Access produces the desired result but when called from ASP.Net produces a null value.
If I edit the string TCDSSql to exclude the AND ActionCode LIKE '*HIT*' clause it returns the correct result for edited string TCDSSql value but I need to include the ActionCode clause.
I am only new to C# and ASP.NET and would be very happy with any assistance
I have an access database that I am querying from ASP.NET using C# to find the sum of all Byte values for a specified date range for a specified user. The database is fairly simple consisting of only one table that is periodically written to by a VBScript that gets the accesslog data from a text file that my proxy server writes to.
The problem I am facing is that when the I call the TCDSCmd.ExecuteScalar().ToString() method from ASP.NET it returns a Null value, However if I do a Response.Write with the SQL Query string and then copy and paste that into an access query it returns the correct value. Please find the code below.
string TotalCacheDataServed(string user, string startdateUtc, string enddateUtc)
{
string TCDSProv = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("SquidLogs.mdb");
string TCDSSql = "SELECT SUM(Bytes) FROM squidaccesslog where Ident='" + user +
"' AND TimeStamp BETWEEN #" + startdateUtc + "# AND #" + enddateUtc +
"# AND ActionCode LIKE '*HIT*'";
OleDbConnection TCDSConn = new OleDbConnection(TCDSProv);
OleDbCommand TCDSCmd = new OleDbCommand(TCDSSql, TCDSConn);
TCDSCmd.Connection.Open();
string TCDSstr = TCDSCmd.ExecuteScalar().ToString();
double TCDSdbl = double.Parse(TCDSstr);
TCDSCmd.Connection.Close();
return TCDSdbl;
}
If I modify this code to write the string variable "TCDSSql" to an <asp:label id="SqlQuery" runat="server" /> I get the following result:
SELECT SUM(Bytes) FROM squidaccesslog where Ident='lindsaybit\brett' AND TimeStamp BETWEEN #03/04/2005 6:18:00 AM# AND #03/08/2005 6:18:00 AM# AND ActionCode LIKE '*HIT*'
This query when run from MS Access produces the desired result but when called from ASP.Net produces a null value.
If I edit the string TCDSSql to exclude the AND ActionCode LIKE '*HIT*' clause it returns the correct result for edited string TCDSSql value but I need to include the ActionCode clause.
I am only new to C# and ASP.NET and would be very happy with any assistance