Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

.NET get list of customers from mysql Database

Posted on 2012-08-14
4
Medium Priority
?
445 Views
Last Modified: 2012-08-14
Im guessing I need to use a DataGridView for this, below is my code for connecting to db and querying db, how do I fill the datagrid view... or is there a better way to do this.

Im trying to make a simple list of customers in the database, some icons on the left side, to select, or delete, or what ever

        Dim db As New databaseConnection()
        db.OpenConnection()

        Dim cmd As New MySqlCommand
        cmd.CommandText = "SELECT m.companyName, p.firstName, p.lastName FROM profiles As p LEFT JOIN merchants AS m ON p.merchantId=m.merchantId WHERE m.merchantId = '" & Form1.mMerchantId & "' AND p.profileId = '" & Form1.mUserId & "'"

        Dim dr As MySqlDataReader = db.DataReader(cmd)

        If dr.HasRows() Then
            dr.Read()
                Fill dataGridView here

        End If

        db.CloseConnection()

Open in new window

0
Comment
Question by:prowebinteractiveinc
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38292117
There are lots of ways to do this.  Here is one.  Datasource and DataBind are the key elements here:

    Dim dsDataSource As New SqlDataSource
    dsDataSource .ConnectionString = [i]whatever[/i]

    dsDataSource .SelectCommand = "SELECT m.companyName, p.firstName, p.lastName FROM profiles As p LEFT JOIN merchants AS m ON p.merchantId=m.merchantId WHERE m.merchantId = '" & Form1.mMerchantId & "' AND p.profileId = '" & Form1.mUserId & "'"

    gvGridView.DataSource = dsDataSource 
    gvGridView.DataBind()

Open in new window

0
 

Author Comment

by:prowebinteractiveinc
ID: 38292173
Im not really understanding your code, Im using mysqlDataReader, I need to fill the DataGridView in IF statement shown in code below. in the if Statement as you can see the db connection is already open and the query has already been executed

        Dim db As New databaseConnection()
        db.OpenConnection()

        Dim cmd As New MySqlCommand
        cmd.CommandText = "SELECT m.companyName, p.firstName, p.lastName FROM profiles As p LEFT JOIN merchants AS m ON p.merchantId=m.merchantId WHERE m.merchantId = '" & Form1.mMerchantId & "' AND p.profileId = '" & Form1.mUserId & "'"

        Dim dr As MySqlDataReader = db.DataReader(cmd)

        If dr.HasRows() Then
            'dr.Read()

        End If

Open in new window

0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 38292253
Datareader is for row by row access. You are showing all rows in grid so you need to grab all rows. Here is an approach using tableadapters

Dim dbadp as new MySqlDataAdapter("Select ...", "connection string")
Dim dTable As New DataTable
dbadp.Fill(dTable)
dbadp.Dispose

grid.DataSource = dTable
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38292535
As [CodeCruncher] notes a DataReader isn't the correct datasource for a GridView.  My psuedocode uses a DataSource, [CodeCruncher]'s uses a DataTable.  

In either case, the entire dataset is available for the GridView to use.  Then you just bind the DataSource to the GridView.
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.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
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 …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 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