Solved

populate DataGridView using mysql database

Posted on 2006-11-28
31
880 Views
Last Modified: 2008-02-01
Hello

Could someone please assist me in getting my information into a the dataGridView from my mysql Database
0
Comment
Question by:prowebinteractive
  • 15
  • 8
  • 5
  • +1
31 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
Using VS2003 or VS2005?
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
Have a look at this

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_22048420.html#a17871205

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

Roger
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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.
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
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
0
 

Author Comment

by:prowebinteractive
Comment Utility
I took alook at the post above,

the line  myAdapter.Fill(myTable)

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

Expert Comment

by:Jeff Certain
Comment Utility
So there's no MySqlDataAdapter in the library you're using?
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
<<(.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?
0
 

Author Comment

by:prowebinteractive
Comment Utility
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)
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
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?
0
 

Author Comment

by:prowebinteractive
Comment Utility
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 !
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
0
 

Author Comment

by:prowebinteractive
Comment Utility
that line with the dataset do I need this line ?   is it correct ?
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
<<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
0
 

Author Comment

by:prowebinteractive
Comment Utility
to answer Chaosian Im using vb2005
0
 

Author Comment

by:prowebinteractive
Comment Utility
to answer Sancler

I think all I put was conn.fill(ds)
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
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
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
0
 

Author Comment

by:prowebinteractive
Comment Utility
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 ?
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
Sorry, newyuppie, didn't see your post.  But we're agreed ;-)

Roger
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
0
 

Author Comment

by:prowebinteractive
Comment Utility
everything compiles with alittle modifying of component names

however datagrid is not being filled

0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
0
 

Author Comment

by:prowebinteractive
Comment Utility
blank, no info at all
0
 

Author Comment

by:prowebinteractive
Comment Utility
I have the header only, 6 colomns, which I choose the text for the header
0
 

Author Comment

by:prowebinteractive
Comment Utility
I moved the code to the form2 onload and its giving me a mysql exception error while running the application
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
Comment Utility
I don't think we are seeing the full picture here.  

You say "I have the header only, 6 colomns, which I choose the text for the header".  But your sql statement is returning 10 fields.  So that yours is showing 6 columns suggests that you have already customised it to some extent.  The code that I offered assumed that there was just a plain datagrid (or datagridview - and they are different) on your form.  It doesn't really matter which it is, but if we are to help, we do need to know.  So, if you have customised the grid, can you please say how?

With respect to the mysql exception error you are now getting, can you please put the line

        da.Fill(dt)

in a Try Catch block with

       Debug.WriteLine (ex.ToString)

in the Catch so we can see what the details are?

Roger
0
 

Author Comment

by:prowebinteractive
Comment Utility
Catch ex As MySql.Data.MySqlClient.MySqlException   like this my catch line ?
0
 

Author Comment

by:prowebinteractive
Comment Utility
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
0
 

Author Comment

by:prowebinteractive
Comment Utility
as for the error I get for da.Fill(dt)   unable to connect to any of the mysql hosts
0
 

Author Comment

by:prowebinteractive
Comment Utility
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
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now