1

I have set up a small test data base (local) using MS SQL Server, code bellow.

Code here 

SqlConnection cs = new SqlConnection(@"Data Source = .\SQLEXPRESS; Initial Catalog = OMS; Integrated Security = true");
        SqlDataAdapter da = new SqlDataAdapter ();
        da.InsertCommand = new SqlCommand("INSERT INTO Customer(FirstName,LastName) VALUES (@FirstName,@LastName)", cs);
        da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = firstname.Text;
        da.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar).Value = lastname.Text;

This is working well, now I have set up a MYSQL database hosted on a live server. I have installed the MYSQL connector I need for visual studios and have connected to the data base. what I now wish to know is how to alter my connection string to pace the data into the live database. I tried to put my host IP in eg:

SqlConnection cs = new SqlConnection(@"Data Source = 000.000.00.000; Initial Catalog = database name; Integrated Security = true");

However this is not working, any idea on what code I should use to connect to a live server? any help would be appreciated.

Improve this question

3 Answers 3

Reset to default
2

Please refer to the link on dev.mysql

http://dev.mysql.com/doc/refman/5.0/en/connector-net-programming-connecting-connection-string.html

and

C# with MySQL INSERT parameters

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

hmm thanks for the comment but it dose not seem to work if i lay it out like so SqlConnection cs = new SqlConnection(@"Data Source=173.254.28.113,3306;Network Library=DBMSSOCN;Initial Catalog=benoatsc_OMS; User ID=benoatsc_admin;Password=*******;");
Are you using SqlConnection or MySql.Data.MySqlClient.MySqlConnection as your connection class, are you not using MYSQL as remote DB?
im using MYSQL as a remote DB.
If you are using MySql you should use MySql.Data.MySqlClient.MySqlConnection class and not SqlConnection. That is the problem, hope it works out for you
1

The answer to your question is mostly related to the settings of the server and/or database. Make sure:

  • the ports on server are opened for remote communication.
  • the application runs (as service)
  • check the database configuration: mostly ports-wise, but you have to make sure there is SQL-login allowed
  • create an user account inside the database and grant that account permissions to your database instance and to the proper table and to actions you want him to perform, some databases require granting him remote login rights, too

When all this is checked, the connection string should be altered to something like:

"Data Source=123.456.789.012;Initial Catalog=testdb; password=password; user id=login;"

This works fine for me, to connect with MSSQL2012 remote server database, since you are mixing MySQL and MSSQL, there could occur some other MySQL specific options.

EDIT: from what I see you did in comments to other answer, CHECK TWICE, how you handle the port -> you are using a comma in the ip string, whereas i hope you should avoid using it or use colon.

Improve this answer

2 Comments

hey jmodrak thanks for your reply. i have got the connection working now and it inputs the data. what do you mean about the comma in the IP ?
Your first comment to kh_s' answer contains this "Data Source=173.254.28.113,3306"... I think the , may sometimes cause trouble, espetially, when the commpon sense tells me : is widely used to separate ip and ports. But I may be wrong, whereas I usually suffer hard, before getting everything on pace between my application and databases.
0

all working thanks to some great advice. working code for reference

{
        MySqlConnection cs = new MySqlConnection(@"Data Source = 000.000.00.000;username=*******;password=******; Initial Catalog = database; Integrated Security = true");
       MySqlDataAdapter da = new MySqlDataAdapter ();
        da.InsertCommand = new MySqlCommand("INSERT INTO Customer(FirstName,LastName) VALUES (@FirstName,@LastName)", cs);
        da.InsertCommand.Parameters.Add("@FirstName", MySqlDbType.VarChar).Value = firstname.Text;
        da.InsertCommand.Parameters.Add("@LastName", MySqlDbType.VarChar).Value = lastname.Text;

        cs.Open();
        da.InsertCommand.ExecuteNonQuery(); 
        cs.Close();
    }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.