locked
Setup for Visual Studio 2008 with remote MySQL TCPIP ODBC connection RRS feed

  • Question

  • I have an issue that is standing in the way of my progress on a particular application.  I need to develop an aspx application in VB.net.  The problem seems to be a problem with the fact I my remote database is MySQL (vs SQL server) and Visual Studio generates SQL code consistent with SQL Server, not MySQL when visually configuring databound controls.   I have the following configuration:

     

    Development system:

                PC running Windows XP with latest service packs.

                Visual Studio 2008.

                I have installed the MySQL ODBC connector / driver that is found here:

    http://dev.mysql.com/downloads/connector/odbc/#download

     

    Target system:

                Windows 2008 Server (virtual machine, remotely hosted)

                IIS for Windows 2008.

                MySQL Server version 5.1.47 running standard TCPIP connections

                Same MySQL ODBC drive installed on this server

     

    On each system, I have created identical System DSN items through ODBC administrator using the MySQL option.

     

    What is working:  I can connect to my database server remotely from any standard TCPIP / ODBC remote administration program such as Navicat for MySQL, Sequel Pro (on Mac).  My VB.net code otherwise works as expected when uploaded to my server.  When I manually code database connections and do things the old, VB.asp way, everything works as expected.

     

    But I need databound VB.net controls for my application.  My first attempts at this did not work, then I was shown that I needed to go into IIS and R click the folder from which I am attempting to run the application and select ‘convert to application’.  After this was done, simple connections through data bound VB.net controls appeared to work.  For example, a simple Gridview example worked perfectly, picking up multiple pages of data from a table.

     

    HOWEVER,

     

    The next step was to use the advanced Gridview features which include Edit.  This is where my problem occurs.  Within VS 2008, I add a Gridview object to a new VB.net page.  I then select the data source for the grid / Configure Data Source . . . / New Connection / Use System Data Source Name /  (select the mysql system DSN) / Configure the Select Statement / (select ‘specify columns from a table or view’, select the table / columns *).

     

    When I select ‘Advanced SQL Generation Options’, the ‘Generate Insert, update, and delete statements’ is GRAYED OUT.  It is essential to use this to auto generate the UPDATE statement.

     

    This then reveals a select statement that is displayed but cannot be edited. 

    SELECT * from [junk]

    and this is where things fail as it fails on the next page under TEST QUERY.  I don’t think MySQLlikes the square brackets and the query fails.  I suspect this is why Advanced SQL Generations Options is grayed out – because the system is using the above query.

     

    ON THE OTHER HAND, if I use a SQL server DSN to connect to an identical database on a different remote server running SQL Server, the Data Source Configuration goes well and allows me to do the Advanced SQL Generation Options and then everything works on the Gridview INCLUDING edit.

     

    Having a primary key in the table that is selected with the query is essential for the Gridview setup to work and this is often pointed out in forum discussions of the problems with gridview.

     

    Here is my table on the MySQL table:

     

    CREATE TABLE `junk` (

      `id` mediumint(9) NOT NULL AUTO_INCREMENT,

      `myname` varchar(32) DEFAULT NULL,

      PRIMARY KEY (`id`)

    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

     

    I am stuck at this point. 

     

    Visual Studio clearly has a problem with using the remote MySQL connection vs the remote SQL server connection.  It seems that VS NEEDS TO KNOW it is dealing with MySQL at some level and generate appropriate SQL code (like not using square brackets [] around object names.  However I do not know how to configure this and LOTS of searching forums, etc,has not revealed this.

     

     

    Hoping this might solve some problems, I downloaded a 3rd party Gridview product and it installed into the VS toolbox easily.  However on configuration of this new control, it uses the same VS data source dialogs which dead end in the grayed out 'Advanced SQL generation options'

     

    • Moved by Andrew.Wu Thursday, May 5, 2011 3:44 AM off-topic (From:Visual Studio Setup and Installation)
    Tuesday, May 3, 2011 5:48 AM

All replies

  • I just installed MySQL Connector / Net

    Don't know if this will help, but saw this as a suggestion.  Here is info on it:

    http://dev.mysql.com/downloads/connector/net/

    Next I followed some suggestions I found elsewhere.  Created new project. Went into references and added dll's from the MySQL directory which is from the installation of the ODBC driver, I believe.  The dll's from the connector / net installation would not be accepted.  I then added 'namespace' references - all that I could find that were related to MySQL.

    And . . . still not luck - grayed out 'Advanced SQL generation options' as above.

    help

    Tuesday, May 3, 2011 6:47 AM
  • Hi dejswa,

    Since your issue is more related with the web developing, I would like to redirect you to the ASP.NET Froum: http://forums.asp.net/default.aspx/16?Data+Access for better support, where you can contact more ASP.NET experts.

    Thanks for your understanding.

    Best Regards,


    Andrew Wu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 5, 2011 3:44 AM