| |
|
|
| |
|
Отображать: |
 Admin
|
 |
 |
| Присоединился: 18 Mar 2003 |
| Число сообщений: 36 |
| |
|
Convert data type error
Отправлено: 18 Mar 2003 07:12 PM |
This is a known problem with International Date handling on IBSW ( covered in previous posts ). The code is sending a string to the database where the sproc parameter is expecting a datetime. Based on the SQL Server locale versus the ASP.NET regional settings, the string which is being sent cannot be converted to a valid date by SQL Server. I am looking for a viable solution to this problem. In the short term:
SiteLog.ascx.vb
Sub BindData()
' Obtain PortalSettings from Current Context
Dim _portalSettings As PortalSettings = CType(HttpContext.Current.Items("PortalSettings"), PortalSettings)
Dim objAdmin As New AdminDB()
grdLog.DataSource = objAdmin.GetSiteLog(PortalId, "", "", "")
grdLog.DataBind()
End Sub
Did you try this:
SiteLog.ascx.vb
Sub BindData()
' Obtain PortalSettings from Current Context
Dim _portalSettings As PortalSettings = CType(HttpContext.Current.Items("PortalSettings"), PortalSettings)
Dim objAdmin As New AdminDB()
grdLog.DataSource = objAdmin.GetSiteLog(PortalId, "", "", "")
grdLog.DataBind()
End Sub |
//Igor
|
|
|
|
Admin
|
|
|
| Присоединился: 18 Mar 2003 |
| Число сообщений: 36 |
| |
|
Re: Convert data type error
Отправлено: 18 Mar 2003 07:13 PM |
Here is the stored procedure Getsitelog Kenny.
ALTER procedure GetSiteLog
@PortalId int,
@PortalAlias nvarchar(50),
@ReportType int = null,
@StartDate datetime = null,
@EndDate datetime = null
as
if @ReportType is null
select @ReportType = 1
if @StartDate is null
select @StartDate = min(DateTime) from SiteLog where PortalId = @PortalId
if @EndDate is null
select @EndDate = max(DateTime) from SiteLog where PortalId = @PortalId
if @ReportType = 1 /* page views per day */
begin
select 'Date' = convert(varchar,DateTime,102),
'Views' = count(*),
'Visitors' = count(distinct SiteLog.UserHostAddress),
'Users' = count(distinct SiteLog.UserId)
from SiteLog
where PortalId = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
group by convert(varchar,DateTime,102)
order by Date desc
end
else
begin
if @ReportType = 2 /* detailed site log */
begin
select SiteLog.DateTime,
'Name' =
case
when SiteLog.UserId is null then null
else Users.FirstName + ' ' + Users.LastName
end,
'Referrer' =
case
when SiteLog.Referrer like '%' + @PortalAlias + '%' then null
else SiteLog.Referrer
end,
'UserAgent' =
case
when SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
when SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
when SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
when SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
when SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
when SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
when SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
when SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
when SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
when SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
when SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
when SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
else SiteLog.UserAgent
end,
SiteLog.UserHostAddress,
Tabs.TabName
from SiteLog
left outer join Users on SiteLog.UserId = Users.UserId
left outer join Tabs on SiteLog.TabId = Tabs.TabId
where SiteLog.PortalId = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
order by SiteLog.DateTime desc
end
else
begin
if @ReportType = 3 /* user frequency */
begin
select 'Name' = Users.FirstName + ' ' + Users.LastName,
'Requests' = count(*),
'LastRequest' = max(DateTime)
from SiteLog
inner join Users on SiteLog.UserId = Users.UserId
where PortalID = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
and SiteLog.UserId is not null
group by Users.FirstName + ' ' + Users.LastName
order by Requests desc
end
else
begin
if @ReportType = 4 /* site referrals */
begin
select Referrer,
'Requests' = count(*),
'LastRequest' = max(DateTime)
from SiteLog
where SiteLog.PortalID = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
and Referrer is not null
and Referrer not like '%' + @PortalAlias + '%'
group by Referrer
order by Requests desc
end
else
begin
if @ReportType = 5 /* user agents */
begin
select'UserAgent' =
case
when SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
when SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
when SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
when SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
when SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
when SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
when SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
when SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
when SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
when SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
when SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
when SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
else SiteLog.UserAgent
end,
'Requests' = count(*),
'LastRequest' = max(DateTime)
from SiteLog
where PortalID = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
group by case
when SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
when SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
when SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
when SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
when SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
when SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
when SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
when SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
when SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
when SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
when SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
when SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
else SiteLog.UserAgent
end
order by Requests desc
end
else
begin
if @ReportType = 6 /* page views by hour */
begin
select 'Hour' = datepart(hour,DateTime),
'Views' = count(*),
'Visitors' = count(distinct SiteLog.UserHostAddress),
'Users' = count(distinct SiteLog.UserId)
from SiteLog
where PortalId = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
group by datepart(hour,DateTime)
order by Hour
end
else
begin
if @ReportType = 7 /* page views by week day */
begin
select 'WeekDay' = datepart(weekday,DateTime),
'Views' = count(*),
'Visitors' = count(distinct SiteLog.UserHostAddress),
'Users' = count(distinct SiteLog.UserId)
from SiteLog
where PortalId = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
group by datepart(weekday,DateTime)
order by WeekDay
end
else
begin
if @ReportType = 8 /* page views by month */
begin
select 'Month' = datepart(month,DateTime),
'Views' = count(*),
'Visitors' = count(distinct SiteLog.UserHostAddress),
'Users' = count(distinct SiteLog.UserId)
from SiteLog
where PortalId = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
group by datepart(month,DateTime)
order by Month
end
else
begin
if @ReportType = 9 /* page popularity */
begin
select 'Page' = Tabs.TabName,
'Requests' = count(*),
'LastRequest' = max(DateTime)
from SiteLog
inner join Tabs on SiteLog.TabID = Tabs.TabID
where SiteLog.PortalId = @PortalId
and SiteLog.DateTime between @StartDate and @EndDate
and SiteLog.TabId is not null
group by Tabs.TabName
order by Requests desc
end
else
begin
if @ReportType = 10 /* user registrations by date */
begin
select 'Date' = convert(varchar,CreatedDate,102),
'Users' = count(*)
from UserPortals
where PortalId = @PortalId
and CreatedDate between @StartDate and @EndDate
group by convert(varchar,CreatedDate,102)
order by Date desc
end
else
begin
if @ReportType = 11 /* user registrations by country */
begin
select Country,
'Users' = count(*)
from UserPortals
inner join Users on UserPortals.UserID = Users.UserID
where PortalId = @PortalId
and CreatedDate between @StartDate and @EndDate
group by Country
order by 'Users' desc
end
end
end
end
end
end
end
end
end
end
end
|
//Igor
|
|
|
|
|
Admin
|
|
|
| Присоединился: 18 Mar 2003 |
| Число сообщений: 36 |
| |
|
Re: Convert data type error
Отправлено: 18 Mar 2003 07:14 PM |
Public Function GetSiteLog(ByVal PortalId As Integer, ByVal PortalAlias As String, Optional ByVal ReportType As Integer = -1, Optional ByVal StartDate As String = "", Optional ByVal EndDate As String = "") As SqlDataReader
' Create Instance of Connection and Command Object
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
' Generate Command Object based on Method
Dim myCommand As SqlCommand = SqlCommandGenerator.GenerateCommand(myConnection, _
CType(MethodBase.GetCurrentMethod(), MethodInfo), _
New Object() {PortalId, PortalAlias, IIf(ReportType <> -1, ReportType, SqlInt16.Null), IIf(StartDate <> "", StartDate, SqlInt16.Null), IIf(EndDate <> "", EndDate, SqlInt16.Null)})
' Execute the command
myConnection.Open()
Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
' Return the datareader
Return result
End Function
|
//Igor
|
|
|
|
|
|
| |
| Перейти к: |
|
|
|
|
|
|