Solved

populate DataGridView using mysql database

Posted on 2006-11-28
31
892 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 8
  • 5
  • +1
31 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18032114
Using VS2003 or VS2005?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18033209
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
ID: 18033698
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:newyuppie
ID: 18034785
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
ID: 18036681
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
ID: 18036710
So there's no MySqlDataAdapter in the library you're using?
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 18036727
<<(.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
ID: 18036775
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
ID: 18036851
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
ID: 18036880
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
ID: 18036887
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
ID: 18036888
that line with the dataset do I need this line ?   is it correct ?
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 18036902
<<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
ID: 18036919
to answer Chaosian Im using vb2005
0
 

Author Comment

by:prowebinteractive
ID: 18036956
to answer Sancler

I think all I put was conn.fill(ds)
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 18037006
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
ID: 18037073
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
ID: 18037092
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
ID: 18037105
Sorry, newyuppie, didn't see your post.  But we're agreed ;-)

Roger
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18037148
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
ID: 18037325
everything compiles with alittle modifying of component names

however datagrid is not being filled

0
 
LVL 34

Expert Comment

by:Sancler
ID: 18037394
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
ID: 18037424
blank, no info at all
0
 

Author Comment

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

Author Comment

by:prowebinteractive
ID: 18037661
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
ID: 18037815
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
ID: 18038016
Catch ex As MySql.Data.MySqlClient.MySqlException   like this my catch line ?
0
 

Author Comment

by:prowebinteractive
ID: 18038112
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
ID: 18038207
as for the error I get for da.Fill(dt)   unable to connect to any of the mysql hosts
0
 

Author Comment

by:prowebinteractive
ID: 18038314
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
ID: 18038574
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access/Visual Basic Question 3 42
Find date of 2nd Thursday of each month 3 33
vb.net sendkey 4 18
VB.Net Data Class 1 19
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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