Using SqlDataSource with MySQL in ASP.Net

kevinf

SOLVED CLOSED ACTIVE ANSWERED
User: kevinf
Posted: on Jun 07, 2022 11:11 PM
Forum: ASP.Net Web Forms
Answer: 1
Views: 4714

I am converting from SQL2014 Server databases to mysql I have changed the Web.config file to suit see below and changed all SqlConnection, SqlCommand and SqlDataReader to start with my. Replaced Imports System.Data.SqlClient with Imports MySql.Data.MySqlClient.

When I try and update the SqlDataSource for my Gridviews I get the following error The data Provider MySql.data.mysqlClient could not be found in the system configuration In the References I have MySql.data, MySqlBackup, MySqlConnector.

I have run NuGet Install-Package MySqlConnector -Version 2.1.10 which was recommended on the net what is required to connect the GridView and I will post another question if there is a problem with RDLC Reporting.

Dim conn As New MySqlConnection(connString)
Dim comm As New MySqlCommand("Select SchoolName from SchoolName order by Schoolname", conn)
Dim readerInfo As MySqlDataReader = comm.ExecuteReader

<add name="CareerConnection" connectionString="server=localhost;database=schoolcarr; User Id=root; Password=######; Integrated Security=yes;" providerName="MySql.Data.MySqlClient" />
<add name="NameConnection" connectionString="server=localhost;database=schoolname; User Id=root; Password=######; Integrated Security=yes;" providerName="MySql.Data.MySqlClient" />
<add name="DataConnection" connectionString="server=localhost;database=schooldata; User Id=root; Password=######; Integrated Security=yes;" providerName="MySql.Data.MySqlClient" />

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 10

Reply
ANSWER
User: dharmendr
Replied: on Jun 10, 2022 09:31 AM
Report

Hi kevinf,

Please refer the sample.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

Download and Install the MySQL Connector

You will need to download and install the MySQLConnector in order to connect to the MySQL database in ASP.Net.

Download MySQL Connector

After installation is complete you need to open Windows Explorer and look for the MySql installation in the Program Files folder of your Windows drive.

There you will find a folder for MySQL Connector and inside that you will find the MySql.Data.dll which you need to copy inside the BIN folder of your project.

MySql Connection String

You need to set the MySql Database connection string in the web.config.

<connectionStrings>
    <add name="constr" connectionString="Data Source=localhost;port=3306;Initial Catalog=AjaxSamples;User Id=mudassar;password=pass@123" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>

Following setting needs to be add in the web.config file.

<system.data>
  <DbProviderFactories>
    <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory,MySql.Data" />
  </DbProviderFactories>
</system.data>

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataSourceID="sdsCustomers">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="sdsCustomers" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>"
    ProviderName="<%$ ConnectionStrings:constr.providerName%>" SelectCommand="SELECT * FROM Customers"></asp:SqlDataSource>

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Using SqlDataSource with MySQL in ASP.Net

Company

Explore

Follow us

Contact Us