8 Replies - 4878 Views - Last Post: 03 January 2012 - 03:16 PM Rate Topic: -----

#1 Alive7   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 02-January 12

MySQL connector, building a fully functional desktop app. VS2010.

Posted 02 January 2012 - 04:18 PM

Hello everyone,
I'm a complete beginner in C# and though I have a basic understanding of programming itself (as I've coded in other languages), C# is something entirely new for me.

I'm currently creating a simple authorization program using Windows forms and MySQL (I'm using MySQL because it's kind of a 'standart' for PHP and I'm willing to be able to interact with a database through PHP). Now it consists of one form and for now it's enough as I want to simply test things out.
The main problem is with MySQL connector. I've downloaded it and added "MySQL.Data" reference to my project. I'm using MySQL server which comes with AppServ (web server). I've managed to set up the program but it works only on localhost. When I try to apply the external IP (the same PC only external IP) MySQL shows an error (# 1042). I've not managed to find a solution.

Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
//using MySql.Data;

namespace WindowsFormsApplication1
{
    public partial class AuthBox : Form
    {
        private DBConnect myConnect;

        public AuthBox()
        {
            InitializeComponent();
            myConnect = new DBConnect();
        }

        private void buttonLogIn_Click(object sender, EventArgs e)
        {
            string Username = textUser.Text;
            string Password = textPassword.Text;

            if (Username.Length <= 0 || Password.Length <= 0)
            {
                return;
            }

            bool Authorized = myConnect.FindMatch(Username, Password);

            if (Authorized)
            {
                labelStatus.Text = "AUTHORIZED";
            }
            else
            {
                labelStatus.Text = "NOT AUTHORIZED";
            }
        }
    }

    class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        private void Initialize()
        {
        //    server = "12.12.12.12"; // External IP.
            server = "localhost";     // Everything works fine with localhost.
            database = "CSharp";
            uid = "user";
            password = "pw";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }

        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;

                    case 1042:  // This is the error which shows up when the external IP is used.
                        MessageBox.Show("Couldn't resolve the given hostname !");
                        break;

                    default:
                        MessageBox.Show("Another failure ! " + ex.Number.ToString());
                        break;
                }
                return false;
            }
        }

        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool FindMatch(string user, string password)
        {
            string query = String.Format("SELECT user, password FROM tableInfo WHERE user='{0}' AND password='{1}'", user, password);
            bool _IsOK = false;

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                int count = 0;

                MySqlDataReader myReader = cmd.ExecuteReader();
                try
                {
                    while (myReader.Read())
                    {
                        count++;
                    }
                }
                finally
                {
                    myReader.Close();
                }                 

                MessageBox.Show(count.ToString() + " entry found !");

                if (count > 0)
                {
                    _IsOK = true;
                }
                else
                {
                    _IsOK = false;
                }

                this.CloseConnection();

                return _IsOK;
            }
            else
            {
                _IsOK = false;
            }

            return _IsOK;
        }
    }
}


I'm quite sure that it's not the code's fault but rather incorrect installation of MySQL connector or something like that.

I'm using Visual Studio 2010.
MySQL connector version: 5.2.7.
MySQL server version: 5.0.51b (AppServ) I'm not even sure if I can use web server's MySQL but it works (at least on localhost).
I'll provide any other necessary information.

As I am beginner in this whole thing it's pretty possible that I've done something absurd or misunderstood some concepts. I'll appreciate your help.

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL connector, building a fully functional desktop app. VS2010.

#2 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: MySQL connector, building a fully functional desktop app. VS2010.

Posted 02 January 2012 - 04:30 PM

I hope that I'm not wasting your time here, but from my experience when it comes to MySQL... If you are utilizing that on a shared hosted web server, the settings are more than likely set to Local only...
Was This Post Helpful? 0
  • +
  • -

#3 Alive7   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 02-January 12

Re: MySQL connector, building a fully functional desktop app. VS2010.

Posted 02 January 2012 - 04:44 PM

Well, currently everything is running on a personal PC and there are no problems to connect to the MySQL server and operate in it from another computer (using PHP). There is also a website running on that web server - everything is fine.
However, I don't know which settings do you mean and it's possible that a C# application has compatibility issues with web server's MySQL.
I'm using a web server in this whole process because I want to be sure that I'll be able to use this DB with PHP as well as it will be functional in a C# application.

Although, I'm open to suggestions on the MySQL server thing. Maybe I have to download a seperate MySQL server or anything like that ?
Was This Post Helpful? 0
  • +
  • -

#4 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: MySQL connector, building a fully functional desktop app. VS2010.

Posted 02 January 2012 - 04:51 PM

What I was meaning was that, accessing MySQL from a webserver from somewhere other than on that particular webserver, farm, or cloud is usually not permitted by default... At least from my experience...
Was This Post Helpful? 1
  • +
  • -

#5 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6246
  • View blog
  • Posts: 24,014
  • Joined: 23-August 08

Re: MySQL connector, building a fully functional desktop app. VS2010.

Posted 03 January 2012 - 03:52 AM

Error #1042 appears to be "Can't get host name for your address". If you're using an address not available in DNS, then that makes sense.

If you add the address you're using and a server name to the Windows hosts file (see here) that might do the trick.
Was This Post Helpful? 1
  • +
  • -

#6 Alive7   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 02-January 12

Re: MySQL connector, building a fully functional desktop app. VS2010.

Posted 03 January 2012 - 10:52 AM

Ok, I've done a little progress. Your suggestions were right. Fixed the problems with external IP and the MySQL server by creating a proper MySQL user (with necessary permissions) as well as a DB which allows connections from remote (or in this case - external) IPs.

Everything worked for a moment but then MySqlDataReader failed (if I'm correct). There are no errors during compilation, however, the program fails during runtime. Visual Studio was not really helpful with any messages, so I tried to re-do the code in order to get at least any information (the underlined line was also shown in Visual Studio itself).
Error messages: http://imageshack.us...r241324134.png/

Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using MySql.Data;

namespace WindowsFormsApplication1
{
    public partial class AuthBox : Form
    {
        private DBConnect myConnect;

        public AuthBox()
        {
            InitializeComponent();
            myConnect = new DBConnect();
        }

        private void buttonLogIn_Click(object sender, EventArgs e)
        {
            string Username = textUser.Text;
            string Password = textPassword.Text;

            if (Username.Length <= 0 || Password.Length <= 0)
            {
                return;
            }

            bool Authorized = myConnect.FindMatch(Username, Password);

            if (Authorized)
            {
                labelStatus.Text = "AUTHORIZED";
            }
            else
            {
                labelStatus.Text = "NOT AUTHORIZED";
            }
        }
    }

    class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        private void Initialize()
        {
            server = "12.12.12.12";
        //    server = "localhost";
        //    server = "192.168.2.101";
            database = "csharp";
            uid = "asd";
            password = "asd";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }

        private bool OpenConnection()
        {
            MessageBox.Show("Opening connection f-nc !");

            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;

                    case 1042:
                        MessageBox.Show("Couldn't resolve the hostname given !");
                        break;

                    default:
                        MessageBox.Show("Another failure ! " + ex.Number.ToString());
                        break;
                }
                return false;
            }
        }

        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool FindMatch(string user, string password)
        {
            string query = String.Format("SELECT user, password FROM tableinfo WHERE user='{0}' AND password='{1}'", user, password);
            bool _IsOK = false;

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                MySqlDataReader myReader;

                try   // This part was remade in order to get that exception. 
                {
                    myReader = cmd.ExecuteReader();  // This part causes the failure.
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString() + " exception");
                }

                int count = 0;
                
                try
                {
                  //  while (myReader.Read())
                    while(count < 2)
                    {
                        count++;
                    }
                }
                finally
                {
                    this.CloseConnection();
                }

                MessageBox.Show(count.ToString() + " entry found !");

                if (count > 0)
                {
                    _IsOK = true;
                }
                else
                {
                    _IsOK = false;
                }

            //    this.CloseConnection();

                return _IsOK;
            }
            else
            {
                _IsOK = false;
            }

            return _IsOK;
        }
    }
}


Basically what I'm trying to do in that function is retrieving the number of rows from the table which match the query. There is probably another way to do it.
Was This Post Helpful? 0
  • +
  • -

#7 RexGrammer   User is offline

  • Coding Dynamo
  • member icon

Reputation: 183
  • View blog
  • Posts: 785
  • Joined: 27-October 11

Re: MySQL connector, building a fully functional desktop app. VS2010.

Posted 03 January 2012 - 12:41 PM

Here's your problem:

034 bool Authorized = myConnect.FindMatch(Username, Password);

You're providing a key that isn't present in the dictionary. You're providing a bad username/password. You also need to return false in the part where you're catching the exception.

You could've found that this line originally causes the error with a simple trace back...

A few tutorials on debugging:
Debugging tips for C#
Debugging

This post has been edited by RexGrammer: 03 January 2012 - 12:44 PM

Was This Post Helpful? 1
  • +
  • -

#8 Alive7   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 02-January 12

Re: MySQL connector, building a fully functional desktop app. VS2010.

Posted 03 January 2012 - 01:53 PM

I didn't quite understand what you meant by "You're providing a key that isn't present in the dictionary".

However, I checked if both strings in the button click event contained valid values and they did, additionally I printed out the query string just to check out if it looked ok.

I believe I'm simply using the DBConnect object wrong in this case after it is created in public AuthBox(). Therefore I guess the best solution in this case would be rewriting MySQL part so that there wouldn't be that class but simply everything handled in the button click event function.
I will report back when I have done it.
Was This Post Helpful? 0
  • +
  • -

#9 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6246
  • View blog
  • Posts: 24,014
  • Joined: 23-August 08

Re: MySQL connector, building a fully functional desktop app. VS2010.

Posted 03 January 2012 - 03:16 PM

So you have fields in your tableinfo table in the connected database which contain the values you're passing for user and password in the user and password fields?

One thing I notice is you're storing your password in text in the database. That's a no-no. It may be OK if you're just learning, but if you're actually looking to release this onto the big old Internet, you're BEGGING for trouble. You need to salt and hash your passwords prior to saving them in the database. See this tutorial for more information on this.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1