Connection string to a live database asp.net C#

Question

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.

score 2 · Answer 1 · edited May 23 '17 at 10:26

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

edited May 23 '17 at 10:26

Community

1
1

answered Nov 16 '13 at 23:57

codingpirate

1,414
1
12
20

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=*******;");` – Beep Nov 17 '13 at 00:20
Are you using SqlConnection or MySql.Data.MySqlClient.MySqlConnection as your connection class, are you not using MYSQL as remote DB? – codingpirate Nov 17 '13 at 00:25
im using MYSQL as a remote DB. – Beep Nov 17 '13 at 00:44
1

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 – codingpirate Nov 17 '13 at 00:47

jmodrak · Answer 2 · 2013-11-17T01:22:40.193

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.

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 ? — Beep, Nov 17 '13 at 01:30
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. — jmodrak, Nov 17 '13 at 01:34

score 0 · Accepted Answer · answered Nov 17 '13 at 01:33

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();
    }

Connection string to a live database asp.net C#

3 Answers3