How to connect VB.net on a ONLINE DATABASE.

[altjen]

Hi everyone.

Im a bit new on visual basic programming language and I need your help connection visual basic with an ONLINE DATABASE.
Im making a software for my school (is happening a small city competition about programming). I set everything up, just need to fix database online.

anyone help me?

[Begginnerdev]

Will the server that you are connecting to have a static IP?

Also, what database type are you connecting to?

SQL Server, Oracle, MySQL, Access, ect...

[altjen]

Im creating a student softwarem to register students(from the software) and upload them on a online database(http://cpanel.hostinger.co.uk) using MySQL.

I need to upload student information from the textboxes to the database online.

[Begginnerdev]

The foremost issue to resolve would be to find what the IP will be for the connection string. The rest will be syntax.

For your correct connection string see this.

Now, for example:

   Protected Friend Function Insert(sFirstName As String, sLastName As String, sMiddleName As String) As Integer
        'Replace home address with IP for hosting server
        Dim con As OleDb.OleDbConnection("Provider=127.0.0.1;Data Source=mydb;" & _ 
                                         "User Id=myUsername;Password=myPassword;")
        Try
            con.Open()
            Dim cmd As New OleDb.OleDbCommand("INSERT INTO Students(LastName,FirstName,MiddleName) OUTPUT INSERTED.StudentID" & _
                                                                   " VALUES(?,?,?)", con)

            cmd.Parameters.AddWithValue("@LastName", IIf(sLastName = String.Empty, DBNull.Value, sLastName))
            cmd.Parameters.AddWithValue("@FirstName", IIf(sFirstName = String.Empty, DBNull.Value, sFirstName))
            cmd.Parameters.AddWithValue("@MiddleName", IIf(sMiddleName = String.Empty, DBNull.Value, sMiddleName))

            'Returns the inserted student's ID
            Return CInt(cmd.ExecuteScalar)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Student.Insert")
            Return -1
        Finally
            con.Close()
        End Try
    End Function

Now you can insert a student like so:

If Insert("John","Doe","Lotta") > 0 Then
    'Inserted
Else
    'Did not insert
End If

[altjen]

thank you for that sir, but Im kinda new on visual basic, would you help me by showing me any video?
I dont understand the code very well :( so...

but thank you anyway :D

[Begginnerdev]

I currently do not have any screen capture software, but I will try to explain it for you.

 Dim con As New OleDb.OleDbConnection("Provider=127.0.0.1;Data Source=mydb;" & _
 "User Id=myUsername;Password=myPassword;")

What this line does is create an object that will connect to the database. The parameters above are: Provider, Data Source, User ID, and Password.

These Paramters break down as follows:

Provider - The Server that is hosting the database (If a local use 127.0.0.1)
Data Source - The name of the database that you are targeting
User ID - A user that has access to that database (Set up through MySQL)
Password - The user's password (Set up through MySQL)

con.Open()

This line is opening the connection to the database.

Dim cmd As New OleDb.OleDbCommand("INSERT INTO Students(LastName,FirstName,MiddleName) OUTPUT INSERTED.StudentID VALUES(?,?,?)", con)

This line is setting the command that you are issuing to the database.

The command is telling the database engine to insert a new record into the table named Students. The command is also telling the database engine that you are supplying the LastName, FirstName, and MiddleName column values. These columns are set up in the database when you create the Students Table.

The "OUTPUT" Clause tells the command that you wish to retreive the ID of the student that you just inserted.

The Values clause tells the database engine that the column values that you have specified before are about to follow.

Please read this for more information on SQL sytax.

The "?"'s that you see are called Parameters. These are used as place holder that will be replaced by the data when you call the Parameters.AddWithValue command.

For more information on parameterized queries please read this.

Please be aware that not all libraries will use "?". Some will use "@ColumnName" for the parameter name.

Once the cmd.Parameters.AddWithValue() function is called, the "?" will be replaced by the value that is stored in the string variables.

cmd.Parameters.AddWithValue("@LastName", _
IIf(sLastName = String.Empty, DBNull.Value, sLastName))

cmd.Parameters.AddWithValue("@FirstName", _
IIf(sFirstName = String.Empty, DBNull.Value, sFirstName))

cmd.Parameters.AddWithValue("@MiddleName", _
IIf(sMiddleName = String.Empty, DBNull.Value, sMiddleName))

For example, if the sLastName variable holds the value of "Doe", the query will say "INSERT INTO Students(LastName,FirstName,MiddleName) OUTPUT INSERTED.StudentID VALUES ('Doe',?,?)

This will continue the same with the following parameters.

The cmd.ExecuteScalar function will return an integer value representing either 1/0 for pass fail or an integer value that is returned from the database. (In this example, if the next StudentID is set to be 102, the ExecuteScalar command will return 102 because of the OUTPUT Inserted.StudentID command)

For more information on OleDbCommand functions, please see this.

I hope that this clears some things up for you. If not, please feel free to ask.

[altjen]

looks like I cant do this without a video :(

I tried this many times, but nothing work....

[Begginnerdev]

What error are you receiving? Also, can you possibly post the code that is throwing the error?

Also with MySQL you may also need to make sure that the connector is installed.

Here is the connector.

[altjen]

I want a code like this, to connect with my database and upload information

I found this code there http://stackoverflow.com/questions/2269237/connecting-to-a-online-mysql-database-using-vb-net

but want to make it to upload data online not to read them online

[Santanu.Das]

What do you mean by

but want to make it to upload data online not to read them online

I mean, you want to save data to the database, which resides at server, through TCP Port.
Begginnerdev, tried to see how you could save data to your database.

But, using MySql, a small chage is needed in codes

Firstly load MySql Connector for vb in your system.
Now, call it from your project useing Import keyword.

Imports MySql.Data.MySqlClient

To access your database declare the connection string.
The structure of connection String by spacifying TCP Port is

  "Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"

The port 3306 is the default MySql port.

And to declare the objects use MySqlClient keyword in lieu of OleDb.