populate DataGridView using mysql database

Using VS2003 or VS2005?

Have a look at this

https://www.experts-exchange.com/questions/22048420/dataadapter-datagridview.html?anchorAnswerId=17871205#a17871205

It's written in terms of OleDb objects, but is just as applicable to Sql.

Roger

MySql uses it's own library, I think. Should be the same code (roughly) with MySql replacing OleDb.

One minor note... in VB2005, you can use "Using" which disposes objects for you (and closes connections). Code is a little cleaner to write, since you're not worried about exception handling as much.

if you are using vb2005 and want to take advantage of typed datasets (as you can do form access or sql server) you will need a third party product to connect to the database, since VS does not offer yet this native functionality for MySQL.
if you decide to go down that road, i can recommend CoreLab MySQL Connector (www.corelab.com) which im using myself on my projects.

NY

I took alook at the post above,

the line  myAdapter.Fill(myTable)

(.fill) is not supported with mysql... this is my main problem

So there's no MySqlDataAdapter in the library you're using?

<<(.fill) is not supported with mysql... this is my main problem>>
i think thats highly unlikely...  the fill method is native to the dataadapter object no matter what type of database you are using. only thing that changes with different databases is the connection string.

have you downloaded the proper ODBC drivers from dev.mysql.com? and do you have a proper connection string?

my code:

        Dim conn As New MySql.Data.MySqlClient.MySqlConnection
        Dim myConnectionString As String

        myConnectionString = "server=127.0.0.1;" _
                    & "uid=user;" _
                    & "pwd=pass;" _
                    & "database=db;"

        Try
            conn.ConnectionString = myConnectionString
            conn.Open()

        Catch ex As MySql.Data.MySqlClient.MySqlException
            MessageBox.Show(ex.Message)
        End Try

        Dim ds As DataSet = New DataSet()
        Dim SqlString As String = "SELECT a.accountNumber, ST.statusName AS accountStatus, ST.statusColor, SU.fname, SU.lname, p.nom, p.prenom, p.telHome, d.dealerId, d.dealerName FROM accounts AS a INNER JOIN profiles AS p ON a.accountNumber=p.accountNumber INNER JOIN dealers AS d ON a.dealerId=d.dealerId INNER JOIN sysusers AS SU ON d.repId=SU.sysUserId LEFT JOIN accountStatus AS ST ON a.accountStatus=ST.statusId WHERE p.profile_type=1 and (a.accountStatus = 1 OR a.accountStatus = 8) AND a.deleted='false'"
        Dim Cmd As MySql.Data.MySqlClient.MySqlCommand = New MySql.Data.MySqlClient.MySqlCommand(SqlString)

there seems to be some records on your database that violate the non-null and uniqueness for the datacolumn you have specified as primary key. what is your primary key, and do have have any duplicates (on the database) for the column you set up as key in visual studio?

how would that prevent the .fill from working, its underlined in the program, meaning it shouldnt compile, which means the query is not even being executed !

Can you please show the code that follows on from your last post, putting that cmd into a dataadapter and calling the .Fill method on that dataadapter?

Roger

that line with the dataset do I need this line ?   is it correct ?

<<there seems to be some records on your database that violate the non-null and uniqueness for the datacolumn you have specified as primary key. what is your primary key, and do have have any duplicates (on the database) for the column you set up as key in visual studio?>>

sorry for this post was meant on another thread

to answer Chaosian Im using vb2005

to answer Sancler

I think all I put was conn.fill(ds)

conn.fill will never work as this is not a method of the connection object.

i dont see the line where you do this

Dim MyAdapter as New MySql.Data.MySqlClient.MySqlDataAdapter(cmd)
MyAdapter.Fill(ds)




The DataAdapter serves as a bridge between a DataSet and a data source for retrieving and saving data. The DataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondataadapterclasstopic.asp

Well, that wouldn't work.  .Fill is a method of a dataadapter, not a connection.

Try this

        Dim myConnectionString As String

        myConnectionString = "server=127.0.0.1;" _
                    & "uid=user;" _
                    & "pwd=pass;" _
                    & "database=db;"

        Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)

        Dim SqlString As String = "SELECT a.accountNumber, ST.statusName AS accountStatus, ST.statusColor, SU.fname, SU.lname, p.nom, p.prenom, p.telHome, d.dealerId, d.dealerName FROM accounts AS a INNER JOIN profiles AS p ON a.accountNumber=p.accountNumber INNER JOIN dealers AS d ON a.dealerId=d.dealerId INNER JOIN sysusers AS SU ON d.repId=SU.sysUserId LEFT JOIN accountStatus AS ST ON a.accountStatus=ST.statusId WHERE p.profile_type=1 and (a.accountStatus = 1 OR a.accountStatus = 8) AND a.deleted='false'"
        Dim da As MySql.Data.MySqlClient.MySqlDataAdapter = New MySql.Data.MySqlClient.MySqlDataAdapter(SqlString, conn)

        Dim dt As New DataTable

        da.Fill(dt)

        myDataGridView.DataSource = dt

That sets up a dataadapter.  The dataadapter opens and closes the connection as necessary.  No, you don't need a dataset - at least for the current purpose.  But you do need a datatable.

I don't have MySQL on this machine, but from recollection the above references are correct.

Roger

there has to be something wrong here:

Dim ds As DataSet = New DataSet()

bcause  MyAdapter.Fill(ds)  ds is underlined, and I have no info in my dataGrid

something tells me there are many steps missing to fill this datgrid view, haw does the datagrid know where to put the info I want ?

Sorry, newyuppie, didn't see your post.  But we're agreed ;-)

Roger

Lots of cross-posting here ;-(  

prowebinteractive.  Please try my code as it is - just substituting the name of your datagrid for myDataGridView.  If it doesn't work, please post the exact error message and the line on which it occurs.

Roger

everything compiles with alittle modifying of component names

however datagrid is not being filled

When you say "datagrid is not being filled" do you mean that it is remaining completely blank or is it, at the very least, getting names for the columns - just no records showing?

Roger

blank, no info at all

I have the header only, 6 colomns, which I choose the text for the header

I moved the code to the form2 onload and its giving me a mysql exception error while running the application

Catch ex As MySql.Data.MySqlClient.MySqlException   like this my catch line ?

the only customizing I did in my datagridview is rename my headers from column 1, column2, column 3 etc etc to Dealer, Rep, Telephone etc etc etc

as for the error I get for da.Fill(dt)   unable to connect to any of the mysql hosts

I got the database to connect

I see one row, in the table which is right

however in the header its showing the name of the column in the database, I want to customize that header text

With this set-up you will need to set the headertext in code after the grid has been bound to the datasource.  This is because, this way, the binding generates the columns.  The code you need will be on these lines, put after

        myDataGridView.DataSource = dt

If you want to change the header text

       myDataGridView.Columns("<field name>").HeaderText = "<new name>"

So, if you want to change d.dealerName from the datatable to Dealer it would be

       myDataGridView.Columns("d.dealerName").HeaderText = "Dealer"

or (I am not sure how MySql will return the qualified field name)

       myDataGridView.Columns("dealerName").HeaderText = "Dealer"

If you want to hide particular columns

       myDataGridView.Columns("<field name>").Visible = False

Roger