MySql, SqlDataSource, GridView, & VWD 2008 Express

Good afternoon, everyone.  I am new to the forums, though not to the site.  I *FINALLY* have a project that I can use to learn more about asp.net, albeit a home project.  I have created a MySQL database called "media" which will house all of my digital media (music, audiobooks, etc) as well as potentially my DVD & book collection.  The first table to have data is the "music" table, which contains the following columns:


DROP TABLE IF
EXISTS `media`.`music`;
CREATE TABLE  `media`.`music` (
  `music_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `music_filename` varchar(255) NOT NULL,
  `music_filesize` int(10) unsigned NOT NULL,
  `music_title` varchar(100) NOT NULL,
  `music_track` tinyint(3) unsigned NOT NULL,
  `music_artist` varchar(120) NOT NULL,
  `music_album` varchar(100) NOT NULL,
  `music_year` year(4) NOT NULL,
  `music_genre` varchar(20) NOT NULL,
  PRIMARY KEY (`music_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Digital music files'
So, I wanted to give my wife and I an interface in which to browse all the music in the database.  I also wanted it to be a "paged" display and allow for sorting.  "AH HA" said my brain, let's use a GridView control coupled with a SqlDataSource control to read the data in the MySQL table.  So, I got on my PC and fired VWD 2008 Express and got to work.  This is where it gets... interesting.

I spent 2 days trying to get the SqlDataSource to read the MySQL database with the MySQL driver.  I finally created an ODBC DSN on my machine and used that for the SqlDataSource and it worked fine.  However, in the interest of learning (and because it has REALLY ticked me off that I can't make this work) I would like to find out how to use the MySQL driver in conjunction with the SqlDataSource control and have it actually work.

After much searching, I came up with a way to get this done in code via my codebehind file, which was the first improvement.  Here is that code:



Imports System.Data
Imports System.Data.Odbc
Imports System.Web.UI.WebControls

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim sConString As String = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=media; User=root;Password=cany0n;Option=3;"
        Dim oConnection As OdbcConnection = New OdbcConnection(sConString)
        Dim sSQL As String = "SELECT music_id, music_title, music_track, music_artist, music_album, music_year, music_genre FROM music"
        Dim oDataAdapter As OdbcDataAdapter = New OdbcDataAdapter(sSQL, oConnection)
        Dim oDataSet As DataSet = New DataSet()
        oDataAdapter.Fill(oDataSet)
        GridView1.DataSource = oDataSet
        GridView1.DataBind()
    End Sub

    Sub PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
        GridView1.PageIndex = e.NewPageIndex
        GridView1.DataBind()
    End Sub
End Class
But, this did NOT give me all the cool features of using the SqlDataSource control with the GridView (had to manually code the paging code,
would have had to code the sorting code, etc). So this was NOT what I was looking for. But like I said, it was a start.
Next, I tried to add a connectionString to the web.config to define the MySQL driver for the SqlDataSource to read. But, that connectionString
did not appear in the dropdown under "Configure Data Source". When I tried to manually add it via choosing "ODBC", and "Connection String"
it puked with such familiar errors as "bad keyword: driver" and the like. So, what I would like to ask you awesome folks out there is how exactly would this work? I mean, it surely has to, correct? I followed about every
suggestion I found (add the reference to MySql.Data.MySqlClient, place the MySql.Data.dll into /bin, etc) and nothing works. Is this a shortcoming
of VWD 2008 Express? Is it a shortcoming of the MySQL Connector (v. 6.0.3)? I would appreciate any and all feedback on this issue. Thanks
everyone for your attention, and for your active assistance in these forums!

Jester1977
0
Jester1977
4/28/2009 7:47:19 PM
asp.net.mysql 1929 articles. 0 followers. Follow

0 Replies
518 Views

Similar Articles

[PageSpeed] 48

Reply:

Similar Artilces:

VWD 2008 Express & SQL Server 2008 Express & Hosting Options
Hey all, go easy on me, this is my first post On my development PC, I am using Visual Web Developer 2008 Express (the free one) along with SQL Server 2008 Express (the free one).  I am building a small scale database application (1 database) and would like to host this on the internet so that others can access it and report back on usability and bugs etc.  It doesn't need to be mega-fast at the moment. I am unsure of what type of hosting to buy, as there are many different options available.  I guess I have the following questions : Is there hosting specific to the...

MySQL as membership provider for VWD 2008 Express
I have been using VWD 2008 Express with the local SQL database to provide membership (users/roles) service.  The built-in user creation and password retrieval are great.  However, my Windows/.Net host does not support SQL.  Can I use a MySQL database to provide the membership service?  I understand that there is a .Net Connector from MySQL, but I am uncertain if it works to this level of functionality.  If it can provide membership service, what steps are needed to do so? If VWD 2008 Express lacks this compatibility, will upgrading to a Visual Studio Standard editio...

VS.NET 2005 V/s VWD 2008 & VS.NET 2008
Hi, I just download Visual Web Developer 2008 and little use it. Can anyone inform me what is the major difference b/w VS.NET 2005 V/s VWD 2008, I am asking only on the ASP.NET point of view. Can some one also guide me If I use Visual Studio 2008 so what major extra benefit I can take as compare to using VWD 2008. Please give me some practical example. Thanks in advance. Difference VS 2005 Target Framework 2.0 AJAX - To be installed separately by installing AJAX Extensions JavaScript Intellisense -limited JavaScript debugging - limited VWD 2008 Target Framework 2.0-3.5 ...

MySQL .NET Connector
I downloaded the MySQL .NET Connector and installed it sucessfully.  Some of the features were integrated into VWD Express, like for example, I can see the MySQL Memnership Provider in the virtual ASP.NET Configuration Wizard (the nice one, not web.config.)  However, it does not work because for some reason it cant make the connection to the database server.  My SQL Server does not appear in the lists of options for Data Sources.  Does anyone how to do this manually?  I tried the following connection string: connectionString="server=localhost;uid=root;pwd=xxxx...

VS.net 2003 & VWD express 2005 & SQL Express 2005 (RS)
i have installed sql express 2005 with reporting services, vs.net 2003 and visual web developer express 2005. How do i get the reporting services designer or create a new report ? The option 'Business Intelligent' is not in the project....

MySQL
Currently have a site coded in PHP but the customer wants to go to .NET. Can I use the existing MySQL DB or am I required to use MSSQL? Thanks, Ken Check out this forum which is specialized for this kind of questions: ASP.NET MySQL forumBart De Smet [MVP] Visit www.msdn.be, www.bartdesmet.net...

Mysql, VWD, and SqlDataSource
I'm trying to use a gridview and a SqlDataSource to connect to a MySql database. When I try to configure the SqlDataSource, I can enter a connection string, but when I click "next", I get an error saying the it could not retrieve the database schema, and I'm dead in the water, so to speak. When using a file that uses the MySqlXxxxxx syntax with a datagrid, it will work, at least it will deliver what the query asked for. I have the MySql.data.dll in the bin folder of the website. I'm using the Connector.Net driver from Mysql AB. This is an ado driver and not an odbc driver. My underst...

MYSQL express 2008...
Hi Everyone I have installed Visual Web Developer 2008 and notice that on the install it no longer gives the option for you to include integrated SQL in the download, as a result, when I go to 'Website' tab in VWD and then 'ASP.Net configuration', when clicking on the 'Security ' option, it makes it quite clear that it cannot find the mysql database. Presumably because its no longer part of the VWD 2008 install, so I install SQL express 2008 only to find: a) I cannot open it as a program, it just gives a 'configuration' option in my start menu and is not a...

VWD, SqlDataSource, and MySql
I'm trying to use a gridview and a SqlDataSource to connect to a MySql database. When I try to configure the SqlDataSource, I can enter a connection string, but when I click "next", I get an error saying the it could not retrieve the database schema, and I'm dead in the water, so to speak. When using a file that uses the MySqlXxxxxx syntax with a datagrid, it will work, at least it will deliver what the query asked for. I have the MySql.data.dll in the bin folder of the website. I'm using the Connector.Net driver from Mysql AB. This is an ado driver and not an odbc driver. My underst...

MySQL and VWD Express
Hi, 1. I'm sorry to have to post this, but I cannot find any info of how to actually WORK with VWD Express and MySQL. I have used MySQL Admin before, as well as MS Access, and there was a huge difference: the Access db could be changed via drag'n'drop on my local machine and then be uploaded to the web hotel. The MySQL db on the other hand was only editable via PHP MyAdmin on the web hotel, and I guess it would be impossible to use the DB explorer pane in VWD Express to use it. Am I correct? 2. Also, what are the negative side effects (if any) of using MySQL instaed of one of the MS S...

Mysql & gridview
here is my code, deleting works fine, but when i update i get a Exception Details: System.Data.Odbc.OdbcException: ERROR [HYT00] [MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-nt]Truncated incorrect DOUBLE value: 'value' here is my code for the gridview: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ikcConnectionString %>" ProviderName="<%$ ConnectionStrings:ikcConnectionString.ProviderName %>" SelectCommand="SELECT * FROM users" UpdateCommand="UPDATE users SET email = @email WHERE id = ?" DeleteCommand...

SQLdatasource + gridview + mysql
Hi,   I have a sqldatasource and a gridview. I work with a mysql 3.51 database. My problem is : When I try tu update a record through the update command of the gridview, it doesn't work.  Here is my code : <asp:SqlDataSource ID="SQL_Panier" runat=server ConnectionString="<%$ ConnectionStrings:Connection to NI %>" ProviderName="<%$ ConnectionStrings:Connection to NI.ProviderName %>" SelectCommand="selectcommand" UpdateCommand="Update Panier SET qtt=@qtt,commentaires=@commentaires WHERE email=?" DeleteCommand="delete from Panier where email=? and code=@code"&g...

VWD 2008 Express Error 42000 MySQL ODBC 5.1 Driver mysqld-5.0.22 error in SQL Syntax
This is driving me a bit mad now trying to get this fixed. Have downloaded and install the mysql connector 5.2.0, have managed to connect fine to the database using ODBC, Can connect ok to the database in Access 2008 and see data.In Visual Web Developer Express 2008 have managed to connect to the database ok, can see all the tables there. However when adding the SqlDataSource and configure, will connect ok, However in the configure the Select Statement it doesn't retreive any of the columns in the specify columns from a table or view. If I do a specify a customer SQL Statement ...

vwd express 2008 markup error in Master Page
Hi I was running the VWD 2008 Express version beta and everything worked as I needed it to..Apart from the registration process. I found on a forum it was an error with the program. (I downloaded it then burnt it to a disc) one of the two options. It turns out the way round it is to uninstall the whole package and do a download installation of the latest release. This has resolved the registration issue however every page I open now shows a "Page contains markup that is not valid when attached to master page" The only way round this is to click on the code make a slight change ie p...

Web resources about - MySql, SqlDataSource, GridView, & VWD 2008 Express - asp.net.mysql

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

Using The LINQDataSource
-*+There is a new datasource control available in Visual Studio that works in much the same way as the SQLDataSource and the ObjectDataSource. ...

moredotnet
M ore d otnet .NET Interview Questions, ASP.NET, C#, VB.NET, XML, SQL Check Out http://www.dotnetUncle.com √ .NET NOTES OOPS in .NET .NET ...

Architectural Overview of the Windows Presentation Foundation Beta 1 Release
Introduction Windows Presentation Foundation Beta 1 Windows Integration New Features WinFX SDK Exploring the New Features Introducing "Express" ...

About Mikesdotnetting
I started this site as somewhere to store answers to bits and pieces that I see asked frequently in the forums at www.asp.net . Rather than type ...

Using MiniProfiler with SqlDataSource ASP.NET WebForms Control
public class ProfiledSqlDataSource : SqlDataSource{ protected override DbProviderFactory GetDbProviderFactory() { // get the "base" DbProviderFactory ...

ASP.NET Pivot Table - RadPivotGrid for ASP.NET AJAX
... reports. RadPivotGrid allows easy customization of all data reports. Try now! Use any declarative data source control (AccessDataSource, SqlDataSource, ...

Sign on
Sign On • Join • Forums Home User Name * Password * Remember me next time ( I forgot my password ) SUBMIT YOUR WTF Content Random Article All ...

ComboBox Sample
ComboBox is an ASP.NET AJAX control that, like the AutoCompleteExtender , combines the flexibility of a TextBox with a list of options that users ...

Latest Updates
Latest Updates - Free source code and tutorials for Software developers and Architects.; Updated: 15 Feb 2013

Resources last updated: 12/1/2015 5:59:44 PM