• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 903
  • Last Modified:

populate DataGridView using mysql database

Hello

Could someone please assist me in getting my information into a the dataGridView from my mysql Database
0
prowebinteractive
Asked:
prowebinteractive
  • 15
  • 8
  • 5
  • +1
1 Solution
 
Jeff CertainCommented:
Using VS2003 or VS2005?
0
 
SanclerCommented:
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
 
Jeff CertainCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
newyuppieCommented:
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
 
prowebinteractiveAuthor Commented:
I took alook at the post above,

the line  myAdapter.Fill(myTable)

(.fill) is not supported with mysql... this is my main problem
0
 
Jeff CertainCommented:
So there's no MySqlDataAdapter in the library you're using?
0
 
newyuppieCommented:
<<(.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
 
prowebinteractiveAuthor Commented:
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
 
newyuppieCommented:
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
 
prowebinteractiveAuthor Commented:
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
 
SanclerCommented:
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
 
prowebinteractiveAuthor Commented:
that line with the dataset do I need this line ?   is it correct ?
0
 
newyuppieCommented:
<<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
 
prowebinteractiveAuthor Commented:
to answer Chaosian Im using vb2005
0
 
prowebinteractiveAuthor Commented:
to answer Sancler

I think all I put was conn.fill(ds)
0
 
newyuppieCommented:
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
 
SanclerCommented:
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
 
prowebinteractiveAuthor Commented:
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
 
SanclerCommented:
Sorry, newyuppie, didn't see your post.  But we're agreed ;-)

Roger
0
 
SanclerCommented:
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
 
prowebinteractiveAuthor Commented:
everything compiles with alittle modifying of component names

however datagrid is not being filled

0
 
SanclerCommented:
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
 
prowebinteractiveAuthor Commented:
blank, no info at all
0
 
prowebinteractiveAuthor Commented:
I have the header only, 6 colomns, which I choose the text for the header
0
 
prowebinteractiveAuthor Commented:
I moved the code to the form2 onload and its giving me a mysql exception error while running the application
0
 
SanclerCommented:
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
 
prowebinteractiveAuthor Commented:
Catch ex As MySql.Data.MySqlClient.MySqlException   like this my catch line ?
0
 
prowebinteractiveAuthor Commented:
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
 
prowebinteractiveAuthor Commented:
as for the error I get for da.Fill(dt)   unable to connect to any of the mysql hosts
0
 
prowebinteractiveAuthor Commented:
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
 
SanclerCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 15
  • 8
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now