CJWSoft Support Info: Problem after swapping from Access to SQL

Active Info

Memberlist

Help

ASPBanner Unlimited Version 8.0
CJWSoft Support Info : ASPBanner Unlimited Version 8.0

Info: Problem after swapping from Access to SQL

Author

Message

<< Prev Info | Next Info >>

Graham T
New User

Joined: June/03/2009
Location: United Kingdom
Online Status: Offline
Info: 4

Added: October/30/2009 at 1:20pm \| IP Logged

Chris

I have been running ASPBanner with Access for quite some time, and just in the process of upgrading to running with MS SQL 2000.

I have created the database using the scripts and uploaded the data to the database, using DTS.

It appears to be seeing the database, as when I log into ASP Banner, it states powered by MS SQL, however I have a couple of problems.

The only file I have changed is the aspbanner_unlimited_config.asp and have altered the two lines:

BannerConnectionString=Provider=sqloledb;Data Source=sql2000.dotnetted.co.uk,5252;Initial Catalog=databdev_01;User Id=xxxxxxxx;Password=xxxxxxxx;
BannerDatabaseType=MSSQL

After uploading this, I have ran the http://www.databasedev.co.uk/data/config/aspbanner_unlimited .asp

The following directory on the webserver and all of the folders within it need (R,W,X,D) Permissions set for the anonymous webserver account. These permissions can generally only be set by your hosting company.

D:\websites\localuser\databdev\data\

The setting below sets up the data connection information.

BannerConnectionString

BannerConnectionString=Provider=sqloledb;Data Source=sql2000.dotnetted.co.uk,5252;Initial Catalog=databdev_01;User Id=xxxxxxxx;Password=xxxxxxxx;

Is there anything else that is required to be changed when converting from one to the other, as now no banners are displaying on the site.

Also, it appears there is another fault, as when I go into reports I get an error:

Banners For ( 468x60 )

Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

/aspbanner/zone_overview_report.asp, line 310

Name

Status

Type

Weight

Begins

Ends

Limit

Imp

Clicks

ClickThru

Hope you can help

Regards

Graham

cwilliams
Admin Group

CJWSoft Web Software Developer

Joined: April/06/2004
Online Status: Offline
Info: 1769

Added: October/30/2009 at 1:27pm \| IP Logged

well with part of it I have no idea... with the char date thing it's definetly some sort of uk date issue.. which can be a real nightmare..

prob happening because of using access then sql... and the sql server has uk setting going on with it.. etc etc

so, I'm still thinking..

If I were you I would manually look over the database values and look for anything weird in there that maybe did not copy of right in the date fields.. also check all the field types and verify they got set to what the scripts try to set them to..

oh.. one last thing.. did you try running the system and setting up some banners before you copied data from the old access database into it.. did it work ok then ? I mean ya reallyu have to do thta and make sure things are working right before you want to import data from another setup..

__________________

Best Regards, Christopher Williams www.CJWSoft.com

Graham T
New User

Joined: June/03/2009
Location: United Kingdom
Online Status: Offline
Info: 4

Added: November/04/2009 at 7:36am \| IP Logged

Chris

I have been away and done a clean install of all folders, and brand new install of SQL.

I ran the scripts to set the database up, and then added new data from the admin login of ASP Banner, so have not imported any data into the database.

I have set up a zone, and created a banner using the date pickers for Begin/End date. When I then look at the zone overview report, I still get the error:

Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

/aspbanner/zone_overview_report.asp, line 310

When I look at server info/specs, it reports:

Server LCID Regional Setting (2057)

Is there anything that can be changed script wise, or do I need to change something in the database?

Cheers

Graham

cwilliams
Admin Group

CJWSoft Web Software Developer

Joined: April/06/2004
Online Status: Offline
Info: 1769

Added: November/04/2009 at 9:55am \| IP Logged

try this..

erase all database data from the banners and banner_stats tables... so you're starting from scratch again

then in the config_inc.asp file find this bit of code

' Used to save and query dates in a format that will work the same with all server regional settings
Function InternationalDate(ExistingDate)
   If IsDate(ExistingDate) = True Then
      DIM ExistingDay, ExistingMonth, ExistingYear
      ExistingDay = Day(ExistingDate)
      ExistingMonth = Month(ExistingDate)
      ExistingYear   = Year(ExistingDate)
      InternationalDate = ExistingYear & "-" & Right(Cstr(ExistingMonth + 100),2) & "-" & Right(Cstr(ExistingDay + 100),2)
   Else
      InternationalDate = Null
   End If
End Function

and try changing it to this.. which basically changes the date function so it does nothing..

' Used to save and query dates in a format that will work the same with all server regional settings
Function InternationalDate(ExistingDate)
   InternationalDate = CDate(ExistingDate)
End Function

__________________

Best Regards, Christopher Williams www.CJWSoft.com

Graham T
New User

Joined: June/03/2009
Location: United Kingdom
Online Status: Offline
Info: 4

Added: November/05/2009 at 5:59am \| IP Logged

Chris

Thanks, works a treat now.

Appreciate the help.

Graham

If you wish to make a comment to this info you must first login
If you are not already registered you must first register

Printable version

Info Jump

You cannot add new info in this area
You cannot add to info in this area
You cannot delete your info in this area
You cannot edit your info in this area
You cannot create polls in this area
You cannot vote in polls in this area