Connecting to remote MySQL database: cannot open connection - C# Discussion Boards

4-Nov-07 4:00

Hello
I do separate project i want to integrated in one how to do

Any idea is helpful

Thanks in advance

Assaf

Paul Conrad4-Nov-07 4:42

4-Nov-07 4:42

Assaf82 wrote:
I do separate project i want to integrated in one how to do

Huh? Rephrase your question. At the beginning you sound like you want to separate two projects, and then you want to integrate them?

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Anthony Mushrow4-Nov-07 4:58

Anthony Mushrow

4-Nov-07 4:58

I think they have two separate projects, and want to merge them togeather.

So, "I do separate project" becomes "I've done separate projects"

My current favourite word is: Waffle

Cheese is still good though.

Paul Conrad4-Nov-07 5:26

4-Nov-07 5:26

That could very well be the case, but who knows for sure.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Assaf824-Nov-07 6:13

4-Nov-07 6:13

Sorry for my mistakes
yes i've done two separate project and i want to merge them

Thanks in advance

Assaf

Paul Conrad4-Nov-07 6:21

Connecting to remote MySQL database: cannot open connection

4-Nov-07 6:21

Assaf82 wrote:
i want to merge them

I'd probably just create a third project and add the sources of the two others into the third. You then have the third one, being fresh, and the other two as original backups.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

mwholt4-Nov-07 2:48

Re: Connecting to remote MySQL database: cannot open connection

4-Nov-07 2:48

Hey - I'm trying to open a connection to a database hosted on my Unix server remotely:

private static void OpenSqlConnection()<br />
        {<br />
            MySqlConnection oMySqlConn = new MySqlConnection();<br />
<br />
            oMySqlConn.ConnectionString = "server=mydomain.com;database=uname_main;user id=uname_main;pwd=myPwd;pooling=false;port=3306;";<br />
<br />
<br />
<br />
            try<br />
            {<br />
<br />
                oMySqlConn.Open();<br />
<br />
                if (oMySqlConn.State == ConnectionState.Open) MessageBox.Show("Connection to MySQL opened through OLE DB Provider");<br />
<br />
                oMySqlConn.Close();<br />
<br />
            }<br />
            <br />
            catch (MySqlException ex)<br />
            {<br />
<br />
                MessageBox.Show(ex.Number + " - " + ex.Message, "DB-Connection");<br />
<br />
            } <br />
            <br />
<br />
        }

Everything works fine (as in, I'm using all required libraries, such as MySQL.Data/MySQL.Data.MySQLClient) and I get no errors until I run this method. After about 8 seconds, it constantly says:

1042 - Unable to connect to any of the specified MySQL hosts.

I'm positive that my domain name is correct and that all login credentials are correct. In CPanel on my server I've added %, %.%.%.%, and my IP address as access hosts and I still can't seem to open the connection.

I've been working on this for hours but to no avail, and online help has proved not useful so far.

Any suggestions? I'd gladly appreciate some!

-Matt

mwholt4-Nov-07 2:54

Re: Connecting to remote MySQL database: cannot open connection

4-Nov-07 2:54

Perhaps I should mention that actually I'm not 100% sure which port I should be using, but everyone else seems to be using 3306. Is that correct as well? What steps must I take to make this connection possible?

I need my application to work with this database on my remote server directly so that I can keep multiple users synced up with stored data, etc.

Thanks again for any help; I need it!

Guffa4-Nov-07 3:41

Guffa

4-Nov-07 3:41

From what I found on forums.mysql.com the default port is correct. You don't even have to specify it in the connection string.

According to connectionstrings.com you should use the key password instead of pwd.

Do you have a firewall/router between the server and the internet? Is there a port forward specified for the port?

Experience is the sum of all the mistakes you have done.

Re: Connecting to remote MySQL database: cannot open connection

mwholt4-Nov-07 8:28

Re: Connecting to remote MySQL database: cannot open connection

4-Nov-07 8:28

Hey thanks for the reply. I changed pwd to password but it still does not work. I never ordered a firewall with my hosting, but submitted a ticket to my host to see if there was any configuration things that need to be worked out anyway.

Also, what is port forwarding? Do I need to specify one?

mwholt4-Nov-07 8:43

Re: Connecting to remote MySQL database: cannot open connection

4-Nov-07 8:43

I just checked with my host and apparently in WHM you also have to add those access hosts. I did this but still get the error and synced it with the account at hand. Obviously something just isn't right...

Paul Conrad4-Nov-07 4:44

4-Nov-07 4:44

Your code looks right, assuming you did not change the default port. Check your firewall settings, and of course, check that the service is running for the database Big Grin | :-D

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

How to select the Server

Assaf824-Nov-07 1:26

4-Nov-07 1:26

Hello
How i can let the user select the server on my Pc instead of writting it?

any help will be greatly appreciated

Assaf

Koltz4-Nov-07 1:34

Koltz

4-Nov-07 1:34

What do you mean?
What server do you whant to select?

Assaf824-Nov-07 1:47

4-Nov-07 1:47

i do project where i want to run it on any computer
this project connect the database on that Pc
through dataconnection which contain the severName and the database
so i do a form let the user specify those value.
The user write the Server in a textBox,
and he chose the database from a combobox

my question is there any method to collect the name of my sever ?

Thanks a lot

Assaf

I Believe In GOD4-Nov-07 2:05

I Believe In GOD

4-Nov-07 2:05

Hi , Have a good Day

if SQL Server Installed on the same PC With your Application

(Not a network connection)

Simply :
Type Your Server Name "(local)" With out the Quotation , But included the Arc's
(local) (local) (local) Smile | :)

----
if you are planing to use your Application on the Other NetWork Pc's
please let me know , I have a Super class For Doing This Wink | ;-)

Kind regards

I know nothing , I know nothing

Assaf824-Nov-07 2:08

4-Nov-07 2:08

yes i plan to use my app. on other Pc

Thanks in advance Stark

Assaf

I Believe In GOD4-Nov-07 2:14

I Believe In GOD

4-Nov-07 2:14

using System;<br />
using System.Text;<br />
using System.Runtime.InteropServices;<br />
<br />
	public class SqlLocator<br />
	{<br />
		[DllImport("odbc32.dll")]<br />
		private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);<br />
		[DllImport("odbc32.dll")]<br />
		private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);<br />
		[DllImport("odbc32.dll")]<br />
		private static extern short SQLFreeHandle(short hType, IntPtr handle); <br />
		[DllImport("odbc32.dll",CharSet=CharSet.Ansi)]<br />
		private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder inString, <br />
			short inStringLength, StringBuilder outString, short outStringLength,<br />
			out short outLengthNeeded);<br />
<br />
		private const short SQL_HANDLE_ENV = 1;<br />
		private const short SQL_HANDLE_DBC = 2;<br />
		private const int SQL_ATTR_ODBC_VERSION = 200;<br />
		private const int SQL_OV_ODBC3 = 3;<br />
		private const short SQL_SUCCESS = 0;<br />
		<br />
		private const short SQL_NEED_DATA = 99;<br />
		private const short DEFAULT_RESULT_SIZE = 1024;<br />
		private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";<br />
	<br />
		//private SqlLocator(){}<br />
<br />
		public static string[] GetServers()<br />
		{<br />
			string[] retval = null;<br />
			string txt = string.Empty;<br />
			IntPtr henv = IntPtr.Zero;<br />
			IntPtr hconn = IntPtr.Zero;<br />
			StringBuilder inString = new StringBuilder(SQL_DRIVER_STR);<br />
			StringBuilder outString = new StringBuilder(DEFAULT_RESULT_SIZE);<br />
			short inStringLength = (short) inString.Length;<br />
			short lenNeeded = 0;<br />
<br />
			try<br />
			{<br />
				if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))<br />
				{<br />
					if (SQL_SUCCESS == SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(IntPtr)SQL_OV_ODBC3,0))<br />
					{<br />
						if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))<br />
						{<br />
							if (SQL_NEED_DATA ==  SQLBrowseConnect(hconn, inString, inStringLength, outString, <br />
								DEFAULT_RESULT_SIZE, out lenNeeded))<br />
							{<br />
								if (DEFAULT_RESULT_SIZE < lenNeeded)<br />
								{<br />
									outString.Capacity = lenNeeded;<br />
									if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString, <br />
										lenNeeded,out lenNeeded))<br />
									{<br />
										//throw new ApplicationException("Unable to aquire SQL Servers from ODBC driver.");<br />
									}	<br />
								}<br />
								txt = outString.ToString();<br />
								int start = txt.IndexOf("{") + 1;<br />
								int len = txt.IndexOf("}") - start;<br />
								if ((start > 0) && (len > 0))<br />
								{<br />
									txt = txt.Substring(start,len);<br />
								}<br />
								else<br />
								{<br />
									txt = string.Empty;<br />
								}<br />
							}						<br />
						}<br />
					}<br />
				}<br />
			}<br />
			catch //(Exception ex)<br />
			{<br />
				#if (DEBUG)<br />
				#endif <br />
				txt = string.Empty;<br />
			}<br />
			finally<br />
			{<br />
				if (hconn != IntPtr.Zero)<br />
				{<br />
					SQLFreeHandle(SQL_HANDLE_DBC,hconn);<br />
				}<br />
				if (henv != IntPtr.Zero)<br />
				{<br />
					SQLFreeHandle(SQL_HANDLE_ENV,hconn);<br />
				}<br />
			}<br />
	<br />
			if (txt.Length > 0)<br />
			{<br />
				retval = txt.Split(",".ToCharArray());<br />
			}<br />
<br />
			return retval;<br />
		}<br />
	}<br />

This Class return All the SQL Server , from the Other PC's on the network
by using this Function GetServers

(THIS CLASS RETURN ONLY THE PC'S That SQL SERVER INSTALLED ON IT)

P.S
it's return String Array Which is easily Can add to combo box

Kind regards ,

I know nothing , I know nothing

Jimmanuel4-Nov-07 4:52

Jimmanuel

4-Nov-07 4:52

Ever heard of SMO? All of that gets replaced with 5 lines of code:

DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
foreach (DataRow row in dtServers.Rows)
{
  string sqlServerName = row["Server"].ToString();
  if (row["Instance"] != null && row["Instance"].ToString().Length > 0)
      sqlServerName += @"\" + row["Instance"].ToString();
}

SMO Essentials[^]
MSDN[^]

Paul Conrad4-Nov-07 6:10

4-Nov-07 6:10

I like that approach better Big Grin | :-D

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Koltz4-Nov-07 6:26

Koltz

4-Nov-07 6:26

Realy nice solution, thanks!

I Believe In GOD5-Nov-07 4:42

I Believe In GOD

5-Nov-07 4:42

yeah I hear of it , thank for you

but I am still using VS 2003 . NET 1.1 Which didn't support the SMO

thank you so much for this information

kind regards

I know nothing , I know nothing

Jimmanuel5-Nov-07 4:56

Jimmanuel

5-Nov-07 4:56

you could try SQL DMO instead - it's the predecessor to SMO which has most of the same functionality, in this case:

static public string[] GetSQLServers()
{
	List<string> retval = new List<string>();

	SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
	SQLDMO.NameList serverList = (SQLDMO.NameList)sqlApp.ListAvailableSQLServers();
	foreach (string server in serverList)
	{
		retval.Add(server);
	}

	return retval.ToArray();
}

Assaf824-Nov-07 2:25