Albert Enstein

Science without Religion Is Lame, Religion without Science Is Blind!

Ali bin Abi Thalib

Tiada kekayaan lebih utama daripada akal. Tiada kepapaan lebih menyedihkan daripada kebodohan. Tiada warisan yang lebih baik daripada pendidikan.

Qur’an Al mujadalah 11

"Niscaya Allah akan meninggikan beberapa derajat orang-orang yang beriman diantaramu dan orang-orang yang diberi ilmu pengetahuan beberapa derajat."

Hadits - Sahih Ibnu Majah

"Siapa yang Allah kehendaki menjadi baik maka Allah akan memberikannya pemahaman (ilmu) terhadap Agama.".

ahm.united

Hidup adalah proses didalamnya ada pembelajaran tiada henti untuk sabar dan syukur... untuk menyempurnakan tempat di keabadian...

Showing posts with label Programming. Show all posts
Showing posts with label Programming. Show all posts

Tuesday, May 21, 2013

VB.Net Connection With MySQL

Before we begin, to use MySQL with VB.NET you will need to download the MySQL Connector/NET which is available from
http://dev.mysql.com.../connector/net/. At the time of writing, the newest version is 6.1. The installer will install
the connector on your system ready for use.

Once the connector is installed, start a new project in VB.NET (Im using 2008) and you will then need to add a reference to the connector.

Choose "Add Reference" from the Project menu, then select "Browse" and browse to the installation folder where the connector was installed,
choose "MySQL.Data.dll" .

Resized to 65% (was 781 x 390) - Click image to enlargeAttached Image


You may also have to add a reference to "System.data.dll". Now inport the Connector/NET to use its Namespace.
1Imports MySql.Data.MySqlClient


Save the project.

Now lets design the form,
Place 3 labels, 3 text boxes and 2 buttons onto your form. Change the labels to the following "Server", "Username" and "Password".
Name the textboxes to the following "txtServer", "txtLogin", "txtPassword". Change the buttons to "Login" and "Cancel".

It should look like the picture below.
Attached Image

Double click the Cancel button and add the following code into the Sub Function. This will close the application when the Cancel button us pressed.
1Application.Exit()


Now we need to add a MySQLConnection object, to do this - add the following to the "Public Class" of the form. Click the Login button and add the following.
1Dim MysqlConn as MySQLConnection


Next, add the following to instanciate the MySQLConnection object.
1MysqlConn = New MySqlConnection()


Now to set the connection string which will be used - this is similar to say - connecting to an MS Access database.
Here is an example of a connection string. This is a reflection on my test database I have set up, hence there is no password.
1server=localhost; user id=root; password=; database=test


Instead of having a hard coded connection (which of course you can for security), we will be using the textboxes that are on the form like so.
1MysqlConn.ConnectionString = "server=" & txtServer.Text & ";" _
2    & "user id=" & txtUsername.Text & ";" _
3    & "password=" & txtPassword.Text & ";" _
4    & "database=test"


Now we want to actually Open the connection, so we add this to the Login button precedure.
1MysqlConn.Open()


Lets add a message box to show if the connection has been open successfully.
1MessageBox.Show("Connection to Database has been opened.")


Lastly, now that we have opened the connection, and because we wont be using any tables in this tutorial, we will close the connection and then
free the resources used.
1MysqlConn.Close()
2MysqlConn.Dispose()


We can if we wanted to and it is good programming, is to catch any errors - when the connection cannot be opened, you will be notified by a message box,
so we will use the Try, Catch and Finally method.
1Try
2   Mysql.conn.Open()
3   MessageBox.Show("Connection to Database has been opened.")
4   Mysqlconn.Close()
5 Catch myerror As MySqlException
6   MessageBox.Show("Cannot connect to database: " & myerror.Message)
7 Finally
8   Mysql.conn.Dispose()
9 End Try



And this is what all your code should look like,
01Imports MySql.Data.MySqlClient
02Public Class frmLogin
03 
04    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
05        Application.Exit()
06    End Sub
07    Dim MysqlConn As MySqlConnection
08    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
09 
10 
11        MysqlConn = New MySqlConnection()
12 
13        MysqlConn.ConnectionString = "server=" & txtServer.Text & ";" _
14        & "user id=" & txtUsername.Text & ";" _
15        & "password=" & txtPassword.Text & ";" _
16        & "database=test"
17 
18        Try
19            MysqlConn.Open()
20            MessageBox.Show("Connection to Database has been opened.")
21            MysqlConn.Close()
22        Catch myerror As MySqlException
23            MessageBox.Show("Cannot connect to database: " & myerror.Message)
24        Finally
25            MysqlConn.Dispose()
26        End Try
27 
28    End Sub
29End Class


Now lets save the application, and then run it and try it.

Connection Successful.
Resized to 96% (was 524 x 234) - Click image to enlargeAttached Image


Connection UnSuccessful - using a password for a non-password protected connection.
Resized to 67% (was 752 x 233) - Click image to enlargeAttached Image


Attached Image

Monday, May 20, 2013

Auto Suggestion with PHP, jQuery, MySQL and XML

I will tell you about implementation autocomplete for your sites. Data can be located in different sources – directly in the JS code, in the database, and even in the XML file.

Step 1. HTML

Here are HTML layout for our autocomplete example page:

index.html

01<!DOCTYPE html>
02<html lang="en" >
03    <head>
04        <meta charset="utf-8" />
05        <title>Autocomplete with PHP, jQuery, MySQL and XML | Script Tutorials</title>
06
07        <link href="css/jquery.autocomplete.css" rel="stylesheet" type="text/css" />
08        <link href="css/main.css" rel="stylesheet" type="text/css" />
09        <script type="text/javascript" src="js/jquery-1.5.2.min.js"></script>
10        <script type="text/javascript" src="js/jquery.autocomplete.pack.js"></script>
11        <script type="text/javascript" src="js/script.js"></script>
12    </head>
13    <body>
14        <div class="container">
15            <form action="#">
16                <p><label>Your month:</label> <input id="month" type="text" autocomplete="off"></p>
17                <p><label>Your year:</label> <input id="year" type="text" autocomplete="off"></p>
18                <p><label>Your country:</label> <input id="country" type="text" autocomplete="off"></p>
19            </form>
20        </div>
21        <footer>
22            <h2>Autocomplete with PHP, jQuery, MySQL and XML</h2>
23            <a href="http://www.script-tutorials.com/autocomplete-with-php-jquery-mysql-and-xml/" class="stuts">Back to original tutorial on <span>Script Tutorials</span></a>
24        </footer>
25    </body>
26</html>

Step 2. CSS

Now, lets define all used styles:

css/main.css

001*{
002    margin:0;
003    padding:0;
004}
005
006body {
007    background-repeat:no-repeat;
008    background-color:#bababa;
009    background-image: -webkit-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
010    background-image: -moz-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
011    background-image: -o-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
012    background-image: radial-gradient(600px 200px, circle, #eee, #bababa 40%);
013    color:#fff;
014    font:14px/1.3 Arial,sans-serif;
015    min-height:600px;
016}
017
018footer {
019    background-color:#212121;
020    bottom:0;
021    box-shadow: 0 -1px 2px #111111;
022    display:block;
023    height:70px;
024    left:0;
025    position:fixed;
026    width:100%;
027    z-index:100;
028}
029
030footer h2{
031    font-size:22px;
032    font-weight:normal;
033    left:50%;
034    margin-left:-400px;
035    padding:22px 0;
036    position:absolute;
037    width:540px;
038}
039
040footer a.stuts,a.stuts:visited{
041    border:none;
042    text-decoration:none;
043    color:#fcfcfc;
044    font-size:14px;
045    left:50%;
046    line-height:31px;
047    margin:23px 0 0 110px;
048    position:absolute;
049    top:0;
050}
051
052footer .stuts span {
053    font-size:22px;
054    font-weight:bold;
055    margin-left:5px;
056}
057
058.container {
059    border:3px #111 solid;
060    color:#000;
061    margin:20px auto;
062    padding:20px;
063    position:relative;
064    text-align:center;
065    width:300px;
066
067    border-radius:15px;
068    -moz-border-radius:15px;
069    -webkit-border-radius:15px;
070}
071
072.ac_results  {
073    border: solid 1px #E5E5E5;
074    color:#000;
075
076    border-radius:0 0 5px 5px;
077    -moz-border-radius:0 0 5px 5px;
078    -webkit-border-radius:0 0 5px 5px;
079}
080.ac_over {
081    background-color:#444;
082}
083
084form p {
085    margin-bottom:5px;
086    text-align:right;
087}
088
089form input {
090    background-color: #FFFFFF;
091    background: -moz-linear-gradient(top, #FFFFFF, #EEEEEE 1px, #FFFFFF 25px);
092    background: -webkit-gradient(linear, left top, left 25, from(#FFFFFF), color-stop(4%, #EEEEEE), to(#FFFFFF));
093    border: solid 1px #E5E5E5;
094    font-size:14px;
095    outline: 0;
096    padding: 9px;
097    width: 180px;
098
099    border-radius:5px;
100    -moz-border-radius:5px;
101    -webkit-border-radius:5px;
102
103    box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
104    -moz-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
105    -webkit-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
106}
107
108form  input:hover, form  input:focus {
109    border-color: #C9C9C9;
110
111    box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
112    -moz-box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
113    -webkit-box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
114}
In our package you can find few more files:

css/jquery.autocomplete.css + css/indicator.gif

Both files I got from autocomplete jquery package (this is default files – don`t need to re-publish it in our article)

Step 3. Javascript

Its time to prepare JS:

js/script.js

01$(function(){
02
03    $('#month').autocomplete(['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'], {
04        width: 200,
05        max: 3
06    });
07
08    $('#year').autocomplete('data.php?mode=xml', {
09        width: 200,
10        max: 5
11    });
12
13    $('#country').autocomplete('data.php?mode=sql', {
14        width: 200,
15        max: 5
16    });
17
18});
As you can see – very easy syntax of using Autocomplete. In first case I hardcoded possible values directly in JS code. Second and third cases – through PHP file (using different way of obtaining data – XML and SQL). In package you can find two another JS files:

js/jquery-1.5.2.min.js + js/jquery.autocomplete.pack.js

This is jQuery library itself plus Autocomplete plugin

Step 4. SQL

Now, lets prepare our database – lets add 1 new table:
01CREATE TABLE `s85_countries` (
02  `country_code` varchar(2) NOT NULL,
03  `country_name` varchar(255) NOT NULL,
04  PRIMARY KEY  (`country_code`)
05) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
06
07INSERT INTO `s85_countries` (`country_code`, `country_name`) VALUES
08('AR', 'Argentina'),
09('AU', 'Australia'),
10('BR', 'Brazil'),
11('CA', 'Canada'),
12('CN', 'China'),
13('IN', 'India'),
14('KZ', 'Kazakhstan'),
15('RU', 'Russia'),
16('SD', 'Sudan'),
17('US', 'United States');
This small table contain several records – list of countries. I took that SQL code from one of our old tutorials.

Step 5. PHP

This step most important – now you will see how we returning data for Autocomplete:

data.php

01<?php
02
03if (version_compare(phpversion(), "5.3.0", ">=")  == 1)
04  error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
05else
06  error_reporting(E_ALL & ~E_NOTICE);
07
08require_once('classes/CMySQL.php');
09
10$sParam = $GLOBALS['MySQL']->escape($_GET['q']); // escaping external data
11if (! $sParam) exit;
12
13switch ($_GET['mode']) {
14    case 'xml': // using XML file as source of data
15        $aValues = $aIndexes = array();
16        $sFileData = file_get_contents('data.xml'); // reading file content
17        $oXmlParser = xml_parser_create('UTF-8');
18        xml_parse_into_struct($oXmlParser, $sFileData, $aValues, $aIndexes);
19        xml_parser_free( $oXmlParser );
20
21        $aTagIndexes = $aIndexes['ITEM'];
22        if (count($aTagIndexes) <= 0) exit;
23        foreach($aTagIndexes as $iTagIndex) {
24            $sValue = $aValues[$iTagIndex]['value'];
25            if (strpos($sValue, $sParam) !== false) {
26                echo $sValue . "\n";
27            }
28        }
29        break;
30    case 'sql': // using database as source of data
31        $sRequest = "SELECT `country_name` FROM `s85_countries` WHERE `country_name` LIKE '%{$sParam}%' ORDER BY `country_code`";
32        $aItemInfo = $GLOBALS['MySQL']->getAll($sRequest);
33        foreach ($aItemInfo as $aValues) {
34            echo $aValues['country_name'] . "\n";
35        }
36        break;
37}
We filter the resulting data by incoming parameter $_GET['q'] from the active text field (where we started typing something). The result – the script gives all matching records. Another one file which we using (as always):

classes/CMySQL.php

This is our usual class file to work with database (pretty comfortable). In its constructor you will able to set your own database configuration:
1$this->sDbName = '_DATABASE_NAME_';
2$this->sDbUser = '_DATABASE_USERNAME_';
3$this->sDbPass = '_DATABASE_USERPASS_';

Step 6. XML

Here are content of our XML data file:

data.xml

01<!--?xml version="1.0"?-->
02<items>
03    <item>1991</item>
04    <item>1990</item>
05    <item>1991</item>
06    <item>1992</item>
07    <item>1993</item>
08    <item>1994</item>
09    <item>1995</item>
10    <item>1996</item>
11    <item>1997</item>
12    <item>1998</item>
13    <item>1999</item>
14    <item>2000</item>
15    <item>2001</item>
16    <item>2002</item>
17    <item>2003</item>
18    <item>2004</item>
19    <item>2005</item>
20    <item>2006</item>
21    <item>2007</item>
22    <item>2008</item>
23    <item>2009</item>
24    <item>2010</item>
25    <item>2011</item>
26    <item>2012</item>
27    <item>2013</item>
28    <item>2014</item>
29    <item>2015</item>
30</items>

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More