SqlConnection problem - MySQL

Discussion in 'ASP .Net' started by Alan T, Jul 19, 2010.

  1. Alan T

    Alan T Guest

    I have installed ODBC driver for MySQL.
    This is my Web.Config:

    <add name="spotitConnectionString"
    connectionString="DSN=MySQL_server;UID=spotit;description=Connection to
    remote MySQL;server=access;database=spotit;port=3306;pwd=traffic54"
    providerName="System.Data.Odbc"/>


    I had an exception of the connection string of the DSN unrecognized.



    This is my method:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Configuration;
    using System.Runtime.InteropServices;
    using System.Web.UI.WebControls;
    using System.Data.Odbc;
    using System.Data;
    using System.Data.SqlClient;

    protected DataSet ExecuteDataSet(string query)
    {
    SqlConnection connection;
    SqlCommand cmd;
    SqlDataAdapter da;

    connection = null;
    cmd = null;
    DataSet ds = new DataSet();
    da = new SqlDataAdapter();

    try
    {
    cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.Text;

    da.SelectCommand = (SqlCommand)cmd;

    connection = new SqlConnection(GetConnectionString());
    cmd.Connection = connection;
    connection.Open();

    // fill the dataset
    da.Fill(ds);
    }
    catch
    {
    throw;
    }
    finally
    {
    if (da != null)
    da.Dispose();
    if (cmd != null)
    cmd.Dispose();
    // implicitly calls close()
    connection.Dispose();
    }
    return ds;
    }


    So I think I cannot us SqlConnection for MySQL?
    What object should I use instead of SqlConnection?
    Alan T, Jul 19, 2010
    #1
    1. Advertising

  2. Alan T

    Man T Guest


    >> What object should I use instead of SqlConnection?

    >
    > Use either MySqlConnection or OdbcConnection.
    >
    > I think the MySqlConnection is a download from the mysql site, but I'm not
    > sure as I don't use it myself.
    >
    > Personally I'd advocate using OdbcConnection (and OdbcDataReader etc) as
    > you can then later change the type of database you are connected to
    > without any change other than the connection string. I use OdbcConnection
    > to connect to both MySQL and SQL Server (plus a few others).


    Hi,

    Could you give me some code that I can use to replace in my original coding?
    Man T, Jul 19, 2010
    #2
    1. Advertising

  3. Alan T

    Simon Whale Guest

    http://dev.mysql.com/downloads/connector/net/ - that is the download site

    there is not much to change with this connector, but mainly sqlconnection to
    mysqlconnection etc

    "Man T" <> wrote in message
    news:uY2e$...
    >
    >>> What object should I use instead of SqlConnection?

    >>
    >> Use either MySqlConnection or OdbcConnection.
    >>
    >> I think the MySqlConnection is a download from the mysql site, but I'm
    >> not sure as I don't use it myself.
    >>
    >> Personally I'd advocate using OdbcConnection (and OdbcDataReader etc) as
    >> you can then later change the type of database you are connected to
    >> without any change other than the connection string. I use OdbcConnection
    >> to connect to both MySQL and SQL Server (plus a few others).

    >
    > Hi,
    >
    > Could you give me some code that I can use to replace in my original
    > coding?
    >
    Simon Whale, Jul 19, 2010
    #3
  4. Alan T

    Alan T Guest

    Hi,

    I have installed both .NET connector and ODBC driver.
    Since the connecting string format for MySQL is the same, how does ASP.NET
    knows which one to use to connect?

    > http://dev.mysql.com/downloads/connector/net/ - that is the download site
    >
    > there is not much to change with this connector, but mainly sqlconnection
    > to mysqlconnection etc
    >
    >>>> What object should I use instead of SqlConnection?
    >>>
    >>> Use either MySqlConnection or OdbcConnection.
    >>>
    >>> I think the MySqlConnection is a download from the mysql site, but I'm
    >>> not sure as I don't use it myself.
    >>>
    >>> Personally I'd advocate using OdbcConnection (and OdbcDataReader etc) as
    >>> you can then later change the type of database you are connected to
    >>> without any change other than the connection string. I use
    >>> OdbcConnection to connect to both MySQL and SQL Server (plus a few
    >>> others).

    >>
    >> Hi,
    >>
    >> Could you give me some code that I can use to replace in my original
    >> coding?
    Alan T, Jul 20, 2010
    #4
  5. Alan T

    Cubaman Guest

    On Jul 20, 1:18 am, "Alan T" <> wrote:
    > Hi,
    >
    > I have installed both .NET connector and ODBC driver.
    > Since the connecting string format for MySQL is the same, how does ASP.NET
    > knows which one to use to connect?
    >
    > >http://dev.mysql.com/downloads/connector/net/- that is the download site

    >
    > > there is not much to change with this connector, but mainly sqlconnection
    > > to mysqlconnection etc

    >
    > >>>> What object should I use instead of SqlConnection?

    >
    > >>> Use either MySqlConnection or OdbcConnection.

    >
    > >>> I think the MySqlConnection is a download from the mysql site, but I'm
    > >>> not sure as I don't use it myself.

    >
    > >>> Personally I'd advocate using OdbcConnection (and OdbcDataReader etc) as
    > >>> you can then later change the type of database you are connected to
    > >>> without any change other than the connection string. I use
    > >>> OdbcConnection to connect to both MySQL and SQL Server (plus a few
    > >>> others).

    >
    > >> Hi,

    >
    > >> Could you give me some code that I can use to replace in my original
    > >> coding?


    Hello:
    I think your're confused with connectors. Odbc connections is a
    diferent technology than .Net data providers.
    Aspnet knows wich class to instantiate basically because you explicity
    write it in code.

    MySqlConnection class is diferent from OdbcConnection class.
    Take a look at here:
    http://www.geekpedia.com/tutorial139_Connecting-to-MySQL-with-Csharp-and-ODBC.html

    Best regards.
    Cubaman, Jul 20, 2010
    #5
  6. Alan T

    Leon Peon Guest

    Connector/Net

    Hi,

    use the MySQL - Connector/Net

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


    With something like that you can access the DB.
    ----------------------------------------------------------------

    public void check_DB_Daten(string dbserver, string dbuser, string dbpass, string dbport, string dbname, string dbtabelle)
    {
    List<string> ausgabe_dbcheck = new List<string>();
    string MyConString = "server=" + dbserver + ";" +
    "uid=" + dbuser + ";" +
    "database=" + dbname + ";" +//"database=devsql;" +
    "port=" + dbport + ";" +
    "pwd=" + dbpass + ";";
    MySqlConnection connection = new MySqlConnection(MyConString);
    try
    {
    MySqlDataReader Reader;
    MySqlCommand mycommand = new MySqlCommand("SHOW DATABASES", connection);
    connection.Open();
    Reader = mycommand.ExecuteReader();
    while (Reader.Read())
    {
    for (int i = 0; i < Reader.FieldCount; i++)
    {
    ausgabe_dbcheck.Add(Reader.GetValue(i).ToString());
    }
    }
    Reader.Close();
    connection.Close();
    //---------------------------------------------------------------------
    //List<string> myList f?ttert hier einen string per ForEach Funktion
    string dbausgabe = "Die Verbindung ist OK!\n\nSHOW DATABASES:\n\n";
    ausgabe_dbcheck.ForEach(delegate(String name)
    {
    dbausgabe += name + "\n";
    });
    //MessageBox.Show(dbausgabe, "Verbindung OK");
    //---------------------------------------------------------------------
    dbverbindung_ok = true;
    }
    catch (MySql.Data.MySqlClient.MySqlException ex)
    {
    MessageBox.Show(ex.Message, "DB Fehler aufgetreten!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    dbverbindung_ok = false;
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message, "DB Fehler aufgetreten!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    dbverbindung_ok = false;
    }
    finally
    {
    if (connection != null)
    {
    connection.Close();
    }
    }
    }

    ----------------------------------------------------------

    Leon-Peon



    Alan T wrote:

    SqlConnection problem - MySQL
    19-Jul-10

    I have installed ODBC driver for MySQL
    This is my Web.Config

    <add name="spotitConnectionString
    connectionString="DSN=MySQL_server;UID=spotit;description=Connection t
    remote MySQL;server=access;database=spotit;port=3306;pwd=traffic54
    providerName="System.Data.Odbc"/

    I had an exception of the connection string of the DSN unrecognized


    This is my method

    using System
    using System.Collections.Generic
    using System.Linq
    using System.Web
    using System.Configuration
    using System.Runtime.InteropServices
    using System.Web.UI.WebControls
    using System.Data.Odbc
    using System.Data
    using System.Data.SqlClient

    protected DataSet ExecuteDataSet(string query

    SqlConnection connection
    SqlCommand cmd
    SqlDataAdapter da

    connection = null
    cmd = null
    DataSet ds = new DataSet()
    da = new SqlDataAdapter()

    tr

    cmd = new SqlCommand(query)
    cmd.CommandType = CommandType.Text

    da.SelectCommand = (SqlCommand)cmd

    connection = new SqlConnection(GetConnectionString())
    cmd.Connection = connection
    connection.Open()

    // fill the datase
    da.Fill(ds)

    catc

    throw

    finall

    if (da != null
    da.Dispose()
    if (cmd != null
    cmd.Dispose()
    // implicitly calls close(
    connection.Dispose()

    return ds


    So I think I cannot us SqlConnection for MySQL
    What object should I use instead of SqlConnection?

    Previous Posts In This Thread:


    Submitted via EggHeadCafe - Software Developer Portal of Choice
    Six Free Visual Studio 2010 MSDN Memberships Giveaway
    http://www.eggheadcafe.com/tutorial...al-studio-2010-msdn-memberships-giveaway.aspx
    Leon Peon, Jul 20, 2010
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mark

    Can't open a SqlConnection

    Mark, May 9, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    712
  2. Nick
    Replies:
    0
    Views:
    412
  3. WebDevHokie

    Re: Reflection and SqlConnection

    WebDevHokie, Jul 10, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    431
    WebDevHokie
    Jul 10, 2003
  4. Rick Spiewak

    Re: SqlConnection problem

    Rick Spiewak, Aug 27, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    391
    Rick Spiewak
    Aug 27, 2003
  5. Michael D Murphy

    Help -- SQLConnection Problem

    Michael D Murphy, Sep 3, 2005, in forum: ASP .Net
    Replies:
    19
    Views:
    637
    Daniel Walzenbach
    Sep 6, 2005
Loading...

Share This Page