asked on

.NET get list of customers from mysql Database

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

Paul MacDonald

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

prowebinteractiveinc

ASKER

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

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paul MacDonald

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.