locked
Need guidance on MySql RRS feed

  • Question

  • User1171402804 posted

    A little history, I got this website template a long while back, it was originally suppose to deploy using MS Access. However I recently set it up on my Windows Server 2003 machine. It worked quite well, well somewhat. After any kind of changes made in the DB, the DB I guess stopped communicating with asp.net. After researching the dilimeia I came to the conclusion that is was because Access is not suppose to be used for web applications. Okay, so no big deal, I will just port everything over to MySql. However I been having troubles with that.

    I am completely new to asp.net as well as mysql. SO please go slow with the help :)

    After some tweaking with connection strings in the web.config file, I finally got the website back online (self host intranet), but I noticed that it does not seem that MySql is communicating with asp.net. Nothing that is in the DB is showing on the site.

    Things I have done:

    Dropped mysql.data.dll into my bin folder

    removed the string in the web config file that was for MS Access and replaced with this:

    <connectionStrings>
        <add name="mySql" connectionString="server=localhost;database=telemarketing;user id=[user name];pwd=[Password];" providerName="MySql.Data.MySqlClient"/>
      </connectionStrings>


    Installed MySQL Net Connector

    Ported over all MS Access DB over to MySQL DB (used BullZip)

    Since I am so new to this, I am racking my head, am I missing something? Please help, need this back quickly.

    Do not know if it matters, but this is what I have running:

    Windows Server 2003, MySQL 5.0, MySQL GUI Kit, MySQL Net Connector 6.6.5, ASP.NET 2.0, .NET Framework 2.0, AJAX extensions,

    Tuesday, April 30, 2013 6:50 PM

All replies

  • User1124521738 posted

    while MySQL can be used with asp.net, the fact that you converted an access database would cause me to ask why didn't you consider MSSQL server (express is free) and has a more natural conversion path than MySQL would as well as being a far more common and therefore supported configuration.

    Access can be used with asp.net web apps, though permissions and security can get "goofy". If you convert to another database platform, expect a significant effort of re-writing or at least validating queries. As recent as last month I've gotten Access-based ASP.Net and Classic ASP apps working on both Windows 2008 R2 and Windows 2003, it's all about the app pool and the containing folder privileges.

    There's a good overview of MySQL with asp.net at http://www.christianasp.net/usingmysql50withaspnet-part1.aspx and http://www.codeproject.com/Tips/355977/Csharp-ASP-NET-MySQL-Connection-Tutorial-with-MySQ

    Tuesday, April 30, 2013 7:45 PM
  • User1171402804 posted

    Thank you for informing that, was afraid of re-writing queries.

    I still have all my original files (web config, and the Access DB), if I was to go back to that format, what would you think my problem was, rather how do I fix the problem I was having?

    Thanks

    Tuesday, April 30, 2013 8:56 PM
  • User1124521738 posted

    well what kind of error messages were you getting?

    turn off any custom error pages to reveal them http://msdn.microsoft.com/en-us/library/h0hfz6fc%28v=vs.71%29.aspx

    you might need to look at code for try/catch blocks to see if errors are being swallowed http://msdn.microsoft.com/en-us/library/0yd65esw%28v=vs.80%29.aspx

    I nearly always use dsn-less connection strings which I read in its entirity from the web.config

    in <appSettings> block: <add key="mail_db" value=driver={Microsoft Access Driver (*.mdb)};DBQ=C:\dev\data\mail.mdb;UID=;PWD="/>

    http://forums.asp.net/t/1086024.aspx/1

    http://www.4guysfromrolla.com/webtech/070399-1.shtml

    http://www.dailyfreecode.com/forum/reference-connection-string-webconfig-288.aspx

    in code, something like this to open the database:

    	string strDBConnectString = ConfigurationManager.AppSettings["mail_db"].ToString();
    
    	OdbcConnection oConn = new OdbcConnection();
    
    	oConn.ConnectionString = strDBConnectString;
    	oConn.Open();
    

    I usually place the database somewhere outside of the webroot say c:\website\data\ and grant the iuser account and everyone users full rights (placing outside of the website path is important as otherwise nasties can easily get uploaded).

    http://forums.aspfree.com/asp-development-5/setting-permissions-access-database-step-step-guide-96087.html

    http://stackoverflow.com/questions/11616620/how-to-read-write-a-microsoft-access-database-with-asp-net-without-folder-permis

    http://serverfault.com/questions/276806/how-to-set-permissions-for-asp-net-apps-to-run-under-iis6-with-windows-authentic

    http://www.powerasp.net/content/hintstips/permissions.asp (classic but folder permissions can still apply)

    Tuesday, April 30, 2013 9:31 PM
  • User1171402804 posted

    Not getting any error messages what so ever. What is happening is I go to the page, where I am able to log in. I log in where I can manipulate data, contact and contact info. Everything is fine for a little while, however after I enter in data and navigate to a different page, it seems that either Access stops working, or asp.net stops getting information from. I used Proccess Monitor to filter out all stuff from w3wp.exe. There I see several different kind of errors, one which is looking for system.mdb in c:\\winnt\system32\inetserv. Which looking there I never saw that file, decided to search for it, and it never came up. The other thing I noticed is that intermittenly the temp idb file of the Access DB will say success and other times it will say not granted.

    That is alot of reading you supplied, so I will get right on that, and try different things, unless you can think of something else that would be more relevant with what is going on with Access and ASP.NET

    Tuesday, April 30, 2013 9:48 PM
  • User1171402804 posted

    Ok, so far I have assigned new permissions to the folder where the Access DB is located at, added a user IUSR, was not there before but now is. Also added a new app pool of local system. Same thing is still happening.

    BTW, no my DB is not in the root folder of the website. The way things are setup is:

    Website Location: c:\\documents and settings\user\desktop\telemarketing

    DB Location: c:\\documents and settings\user\desktop\telemarketing\App_Data

    All with a virtual path into IIS 6.0

    Tuesday, April 30, 2013 10:43 PM
  • User1124521738 posted

    Access isn't actually running, you're just using a database driver to access the tables and views in the MDB file. If it just up and stops working it could be system resources (which it sound's like you are monitoring, how are those levels going?) or could be bad code that is perhaps in a try/catch block which swallows the errors. 

    Does it start working again if you reverse your path? If it does, I'd be leaning more towards bad code or bad database paths that should have been pointed at a config entry but instead got pointed at an programmatic assigned path.

    Website Location: c:\\documents and settings\user\desktop\telemarketing

    DB Location: c:\\documents and settings\user\desktop\telemarketing\App_Data

    I'd suggest moving the telemarketing folder to c:\telemarketing and move app_data out of there and call it c:\telemarketingdata as App_Data is currently a subdirectory of the main website. Currently the data folder is a sub folder of the website, regardless of the virtual folder you mapped it to.

    I'd also suggest creating a whole new IIS site and point it at the new c:\telemarketing folder and not use any virtual folders update your config file to point at the new c:\telemarketingdata path.

    using a hostfile and host header will allow you to test the site until you get a DNS or WINS entry that other computers can use to locate the site. Some code just doesn't play well except as a root level site. If you have the source code, put in break points and see if you can find the database logic in the areas that don't play nice. Sounds like there could be some hardcoded paths in the code that point at invalid locations.  I've used this hostfile trick as far back as windows 2000.  Note, it only work for site resolution on computers that have had the hostfile entry made on them, so doing the hostfile on computer A will do no good accessing from computer B.

    http://support.microsoft.com/kb/228760

    http://support.microsoft.com/kb/324287

    http://stackoverflow.com/questions/5165979/setting-up-a-host-file-faking-urls

    http://www.mojoportal.com/adding-a-host-name-to-the-hosts-file-for-local-testing

    Wednesday, May 1, 2013 12:19 AM
  • User1171402804 posted

    Just shut my server down for now, so I cant monitor them as it stands. About 20 mins ago I actually moved the Telemarketing folder, and created a new virtual path for it in IIS. Right now it is at C:\\www\telemarketing.

    Not sure if I follow you on system resources, but if you mean like RAM, CPU, etc, then yes I started monitoring those as well.

    CPU at its peak goes to 59%, RAM between 50 - 75%, Network gets about 50% maybe 60% did not monitor that to closely, and File page load says 1.02GB. If you mean by monitoring the processes, only monitored that one process, is there another I should monitor?

    If by reverse my path you mean by either refreshing or go back one page in history, then no, it does not start working after that. Even though, I been thinking its some bad code.

    Wednesday, May 1, 2013 12:30 AM
  • User1124521738 posted

    and created a new virtual path for it in IIS. Right now it is at C:\\www\telemarketing.

    I'd REALLY suggest getting away from virtual paths and do a whole new IIS site instance (see earlier post and notes about hostfiles).

    and this isn't the first time you've done it, any particular reason you type c:\\ and not c:\ ???

    I don't know how the code is set to work so it could be naively written stuff that puts things in session and then wipes itself when the code starts failing. I think you are going to need to make friends with the debugger and attempt to use the source code and repro the steps that crap out in IIS. You can use IIS as the site hosting with the debugger so that you are doing and apples-to-apples test.

    Wednesday, May 1, 2013 12:43 AM
  • User1171402804 posted

    I think you might be right, as far as the debugger. Found a new error in how things are being displayed. But I wont get into that. I am goign to get this things straightened out some how. But because I need that DB now, I probable will just put some forms together within Access so I can start using it for what I need, and when I can work on the Online part of it all.

    Lol, IDK, didnt even realize I was putting \\ and not \ after the c:

    Wednesday, May 1, 2013 12:58 AM
  • User2108829372 posted

    I think it should work using mysql-connector-net-6.5.6 one think you need to make sure you specify the port if mysql is not running in default port (3306)

    const string MYSQLCONNECTIONSTRING = "server=192.168.1.1;uid=root;pwd=root;database=sample_db;port=3306;";
    using (MySqlConnection connection = new MySqlConnection(MYSQLCONNECTIONSTRING))
    {
       connection.Open();
    }
    

    Wednesday, May 1, 2013 4:07 AM