Connect SQL Express 2012 vb.net

Question

I have been working on this for a few days now. I am trying to make vb.net forms app for my database. Right now I am working on a login form and a main form. I have researched many different websites and played with this string forever, but i can not get a connection to my db. I get different errors saying the machine refused it, then ill get a timeout error, then it will go back to refusal.

Dim conn As New MySqlConnection
    If conn.State = ConnectionState.Closed Then
        conn.ConnectionString = ("Server=192.168.0.2;Database=Sunshinetix;User=sa;Password=sunshine;")
    End If

Can someone please tell me what I am doing wrong? I am a beginner in vb.net. And this is a remote server, but LAN.

Thanks!

PS: Is is because I am using SQL Express?

Enable remote connections for SQL Server Express 2012

I've had that article open for a few days and it has helped me a lot to this point.

Create a file on your desktop named test.udl Double click it, you should get a data link properties window. Step through the process and make sure test is ok. When it is, close the data link window and right click -> open in notepad. Your connection string will be shown for you. — George Mastros, Jan 24 '14 at 17:55
So is it SQL Server Express or MySQL? Why are you using MySqlConnection? — Yuriy Galanter, Jan 24 '14 at 17:55
I did not know there was a differnece Yuriy Galanter. I am using SQL Express — user3232927, Jan 24 '14 at 17:58
G Mastors, this is what it returned sucsessfully,Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Sunshinetix;Initial Catalog=Sunshinetix;Data Source=SUNSHINETIX — user3232927, Jan 24 '14 at 18:01
In your connection string that you posted, you had user=sa. In this one, you have User ID=Sunshinetix. Make that change in your connection string. Hopefully it will work. — George Mastros, Jan 24 '14 at 18:03
thank you for all your help. I figured out the idiot I am for using the wrong code. Thanks again! — user3232927, Jan 24 '14 at 18:21
The only "idiot" part about this is that you worked on it for a few days before asking for help. It's important that people research whatever problem they are having, but "a few days" is too long to wait before asking for help. — George Mastros, Jan 24 '14 at 18:36

score 3 · Accepted Answer · edited May 23 '17 at 10:29

If you are using Sql Server express, you need to use a SqlConnection, not a MySqlConnection. That one is for use with MySQL, which is a different implementation of SQL than Microsoft SQL Server

Of course, Microsoft also offers the OleDbConnection as kind of a "catch-all" (see more here: Difference between Sql Connection and OLEDB Connection). Still, if I know I'm going to stick with a particular SQL provider, then I generally use the specialized version.

Also, if you're ever interested, here is a small list of common SQL providers:

Microsoft SQL Server - proprietary SQL provider developed and supported by Microsoft
MySQL - Free, open source relational database system developed by Oracle. Very popular with web developers.
PostgreSQL - another open-source RDBMS. Gaining popularity due to its flexibility and adherence to standards.
SQLite - a small, SQL provider with an emphasis on portability. Unlike the others, it uses local database files rather than a remote server. This has made it pretty much the default choice when developing mobile applications that require local storage.

this did create my connection. thank you. still learning. now have to find out why the rest didnt work lol — user3232927, Jan 24 '14 at 18:10
which provider would you use? I just want one where I know im not going to be sitting on connection errors to get my DB to work — user3232927, Jan 24 '14 at 18:17
I typically use MS SqlServer with `SqlConnection`. That'll definitely be fine for learning. I mostly wanted to include a list so you could get an idea of what all is out there (and why different sql connection classes exist) — valverij, Jan 24 '14 at 18:30

Marek · Answer 2 · 2014-01-24T18:11:31.123

1

Try this:

Dim SQLConn As SqlConnection = New SqlConnection
SQLConn.ConnectionString = "Data Source=servername;" & _
"Initial Catalog=databasename;" & _
"User ID=username;" & _
"Password=userpassword;"

The reason why you can't connect is that you are using MySQLConnection that is connectiong to connecting to MySQL DB which is different from MS SQL so you need to use SqlConnection

You might also check this:

http://support.microsoft.com/kb/308656

Also check this site:

http://www.connectionstrings.com/sql-server/

To know preferences of your SqlConnection string according to SQL version.

Try this to test your connection string:

    Dim connectString as String = ""
Try
     Dim objConn As SqlConnection = New SqlConnection(Server=192.168.0.2;Database=Sunshinetix;User=sa;Password=sunshine;)
     objConn.Open()
     objConn.Close()
     Msgbox("Successfully connected to database!")
Catch ex As Exception
     Msgbox("Cannot connect, Error:" & ex.Message)
End Try

edited Jan 24 '14 at 18:11

answered Jan 24 '14 at 17:58

Marek

3,555
17
74
123

thank you for that article. it helped me create my connection. All help is appreciated greatly!!! – user3232927 Jan 24 '14 at 18:11
i got my db to connect, but their is an exception thrown at this line. im not sure if i wrote it wrong or not, since i converted from MYsql to SQL Express. I have - Dim data As SqlDataReader and the exception is - data = command.ExecuteReader – user3232927 Jan 24 '14 at 18:20
@user3232927 better would be create new question, as this is not related to the topic, then add here a comment with url of the new question. – Marek Jan 24 '14 at 18:22

Connect SQL Express 2012 vb.net

2 Answers2