Search

Wednesday, November 16, 2011

VB.NET MySQL (Insert Picture in Blob Field)

Following coding will help you to insert picture from VB.NET to MySql BLOB field. Coding is very simple.

First you need to install MyODBC for your windows. Installation is very simple. You can download MyODBC from following link:


Create table statement for MySQL.

CREATE TABLE `picture` (
  `ID` int(32) unsigned NOT NULL auto_increment,
  `Picture` longblob,
  `EmployeeID` varchar(10) default NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID`(`ID`),
  KEY `ID_2`(`ID`)
) TYPE=MyISAM;

VB.NET CODE:

Imports System.IO        'Write this coding before Class declaretion.
   
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

  Dim cmd As New Odbc.OdbcCommand
  Dim SQL As String
  Dim rawData() As Byte
  Dim fs As FileStream
  Dim sPath As String
 
  Dim connectionString As String = "Driver={MySQL};" & _
        "Server=server_name;" & _
        "Database=DSN_Name;" & _
        "Uid=root;" & _
        "Pwd=root;" & _
        "OPTION=16427"

  Dim conn As New Odbc.OdbcConnection(connectionString)

  sPath = txtPicturePath.Text

  fs = New FileStream(sPath, FileMode.Open, FileAccess.Read)
  rawData = New Byte(fs.Length) {}
  fs.Read(rawData, 0, fs.Length)
  fs.Close()
  conn.Open()
   
SQL = "INSERT INTO Picture(EmployeeID, Picture) VALUES('" & TxtID.Text & "', ?)"
 
  cmd.Connection = conn
  cmd.CommandText = SQL
  cmd.Parameters.Add("@bin_data", rawData)
  cmd.ExecuteNonQuery()

  conn.Close()
End Sub
If above code work please share the link.

2 comments:

Anonymous said...

is it no need to import mysql.data.mysqlclient?

Md. Zabed Al-Rezba said...

Sorry for very very late reply.

You just need to download and install myodbc

Nothing else you need.


Thanks