Monday, 18 February 2013

query mysql database in asp.net VB.NET


query mysql database in asp.net  VB.NET


Step 1) Add reference MySQL Library to asp.net application/Windows Forms/WPF

          MySql.Data  located in "C:\Program Files (x86)\MySQL\MySQL Connector Net 6.6.4\Assemblies\v4.0\MySql.Data.dll"
Step 2) If u don't find MySql.Data.dll
          pls download it from   MySQL Connector  Net 6.6.4 or higher.  from MYSQL NET CONNECTOR

Step 3)  Add GridView to asp.net page

 <div>
        <asp:GridView ID="GridView1" runat="server" ShowFooter="True"
             Caption="<h1 style='background-color:teal;color:white;'>MySQL connection in ASP.NET</h1>"
            BackColor="LightGoldenrodYellow" BorderColor="Tan"
            BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">
            <AlternatingRowStyle BackColor="PaleGoldenrod" />
            <FooterStyle BackColor="Tan" />
            <HeaderStyle BackColor="Tan" Font-Bold="True" />
            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
                HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
            <SortedAscendingCellStyle BackColor="#FAFAE7" />
            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
            <SortedDescendingCellStyle BackColor="#E1DB9C" />
            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
        </asp:GridView>
    </div>


Step 4)  Connect to MYSQL Database in asp.net C#

Add using Statements
Imports MySql.Data
Imports MySql.Data.MySqlClient
Imports System.Data
Connect to MYSQL Database using MySqlConnection
            //MySQL connection String
             MySql.Data.MySqlClient.MySqlConnection mysqlConn = New MySqlConnection(
            "server=localhost;database=HR;uid=root;pwd=password;port=3306")

Open the Connection

            mysqlConn.Open()
Add MySql data Adapter located in MySql.Data.MySqlClient.MySqlDataAdapter

             MySql.Data.MySqlClient.MySqlDataAdapter ad =
            New MySqlDataAdapter("select * from emp", mysqlConn)
            Dim tbl As DataTable =  New DataTable()
            ad.Fill(tbl)
Bind to GridView

             GridView1.DataSource = tbl
            GridView1.DataBind()

Step 6) RUN THE aspx page

OUTPUT:

MySQL connection in ASP.NET

idEMPenameSal
2shyam5500.56
3benegal5555.00





Complete Source Code

            MySql.Data.MySqlClient.MySqlConnection mysqlConn = New MySqlConnection(
            "server=localhost;database=HR;uid=root;pwd=password;port=3306")
            mysqlConn.Open()
            MySql.Data.MySqlClient.MySqlDataAdapter ad =
            New MySqlDataAdapter("select * from emp", mysqlConn)
            Dim tbl As DataTable =  New DataTable()
            ad.Fill(tbl)
            GridView1.DataSource = tbl
            GridView1.DataBind()

Note:
If u get following error
"#1045 - Access denied for user 'root'@'localhost' (using password: NO)"

 Pls follow this link

Tags:query mysql database in asp.net,MYSQL NET COnnector download,MYSQL.DATA.DLL,Connect to MYSQL Database in asp.net Vb.NET,,MySql connection String in asp.net,mysql connection string in web.config,mysql data adapter.