Search

Tuesday, November 15, 2011

VB.NET & MySql Connection (ADODB)


Some time when we search for VB.NET MYSQL Connection in internet we found lots of complicated solutions. I also face same problem. That’s why I would like to share my experience with you.  I hope it will also help for you.

You need to install MyODBC 2.5 or later version need to install. Check the MyODBC version in following link:

Or You can just extract the MyODBC zip file and copy the MYODBC.DLL into System32 folder.

http://84.113.22.230:8980/mysql/downloads_html/api-myodbc-2.50.html

Add References of ADODB (Project Add References).

Coding:
  Dim con As New ADODB.Connection
        cst = "Driver={MySQL};" & _
        "Server=server_name;" & _
        "Database=Database_Name;" & _
        "Uid=userid;" & _
        "Pwd=password;" & _
        "OPTION=16427"

        con.Open(cst)
     con.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Sample data retrieve in Grid:
Dim iRow As Integer
Dim rs As New ADODB.Recordset
Dim qrystr As String

qrystr = "Select EmployeeID, Name From Employee Order by EmployeeID"

rs.Open(qrystr, con, ADODB.CursorTypeEnum.adOpenStatic,
                  ADODB.LockTypeEnum.adLockReadOnly)

If rs.RecordCount > 0 Then
   EmployeeGrid.RowCount = rs.RecordCount
   For iRow = 0 To rs.RecordCount - 1
EmployeeGrid.Item(0, iRow).Value = rs.Fields("EmployeeID").Value EmployeeGrid.Item(1, iRow).Value = rs.Fields("Name").Value
      rs.MoveNext()
   Next
 Else
      EmployeeGrid.RowCount = 1
      EmployeeGrid.Item(0, 0).Value = ""
      EmployeeGrid.Item(1, 0).Value = ""
 End If

 rs.Close()
 con.Close()

1 comment:

Anonymous said...

Hi Rezba vai

Thanks for the coding. This coding is really very simple.

Can you please upload the coding of Picture upload using VB.NET & MySql.

Thanks
Asad