Question Creating grids using specific fields at run time

[klyxmaster]

I hope I am in the right area.. I am moving up to vb2008

I have mysql with millions of records. One of the tables I want to search but display only a few of the fields in a results grid. I know how to dump a record in it, but the table has over a 100+ fields. I just need a few.

  Try

            conn.Open()

            da = New MySqlDataAdapter(sqlQRY, conn)


            Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(da)


            da.Fill(ds, "super_big_table")

            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "super_big_table"

        Catch ex As Common.DbException
            MsgBox(ex.ToString)
        Finally

            conn.Close()
        End Try

this will fill the grid with a row of specified results from search. but I want to only show about 5 of the fields in the grid. How is this done? I am still used to vb6, but I cant seem to get mysql to work with it - thus the change to 2008

thanks!

 

[IanRyder]

Hi and welcome to the Forum.

What you are describing has nothing to do with VB6, VS2005 or VS2008 but has everything to do with the SQL statement that you are using to interrogate your Database. To limit the fields returned from your Database all you do is specify the field names you want in the Select clause of the SQL Statement. Have a look at this tutorial:-

SQL SELECT Statement

Hope that helps.

Cheers,

Ian

 

[klyxmaster]

Hi and welcome to the Forum.

What you are describing has nothing to do with VB6, VS2005 or VS2008 but has everything to do with the SQL statement that you are using to interrogate your Database. To limit the fields returned from your Database all you do is specify the field names you want in the Select clause of the SQL Statement. Have a look at this tutorial:-

SQL SELECT Statement

Hope that helps.

Cheers,

Ian

LOl
I believe you misread my post -
It has nothing to do with the sql queries (I am quite proficient in them ), I do not know how to setup the datagrid to accept the "RESULTS" provided by the quiery

example
I have a table with players info - something in the range of 125 fields
end user does a search for a player name
the results wanted are level, name, ip address and a few other fields - NOT ALL 125 fields are needed at this stage of the app.
- in php this is a breeze to do using tables - conduct the query, goes in results, the results (or w/e var) then create a table and assign each cell with the desired number of fields and their values

the last part of the above sentence is what I do not know how to do in vb. I can get the search results - but the datagrid I do not know how to set up at run time to accept those results. with just 5 of the 125 fields - in vb2008 I have the array setup for the values - im stuck at the datagrid.

hope that clears up the question

thanks for the welcome..

 

[IanRyder]

Hi,

Well you have confused me. Based on the code that you have already posted, this statement:-

I do not know how to setup the datagrid to accept the "RESULTS" provided by the quiery

Does not make sense because, even though the code is not strictly accurate, it is good enough to fill a DataGridView with the results of your SQL Query so long as you have correctly defined the "conn", "sqlQRY" and "ds" objects.

Here is a typical example I use to fill a DataGridView will specific columns from a Database:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
  Dim daEmployees As New SqlDataAdapter("Select LastName, FirstName From Employees", sqlConn)
  Dim DT As New DataTable
 
  daEmployees.Fill(DT)
  DataGridView1.DataSource = DT
End Sub

Hope that helps.

Cheers,

Ian

 

[klyxmaster]

Solved it (satisfactory)

 Try

            conn.Open()
            Dim mysql_query As MySqlCommand = New MySqlCommand(sqlQRY, conn)
            Dim Results As MySqlDataReader = mysql_query.ExecuteReader()

            While results.Read()
                Dim Playername, CharacterName As String
                Dim level,xp As Integer

                Title = results.GetString("Playername")
                Objectives = results.GetString("CharacterName")
                entry = Results.GetInt32("level")
                quest_level = Results.GetInt32("xp")

                Me.DataGridView1.Rows.Add(xp, level, Playername, CharacterName)
            End While


        Catch ex As Common.DbException
            MsgBox(ex.ToString)
        Finally

Right after the "try" is the stored results. What stumped me was the rows.add section. I did not know you could combine the variables in the datagrid. LIttle different than VB6. And I'm starting to like vb2008 already. I just found out with more queirs with larger results - you can SORT!! hitting the headers - now thats nice!!

any hoo. looks like one line held me up.

hope this will help anyone else tinkering with the datagrid.

 

[klyxmaster]

O I like that way better. I'll take a closer look at it

 

[IanRyder]

Hi,

Glad you got it working. Since I knocked out that last post pretty quickly and since this is new ground for you, this is how I should have really demonstrated using that code:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Using sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    Using daEmployees As New SqlDataAdapter("Select * From Employees", sqlConn)
      Using DT As New DataTable
        daEmployees.Fill(DT)
        DataGridView1.DataSource = DT
      End Using
    End Using
  End Using
End Sub

You will notice here that rather than using DIM statements I have declared the Database Objects with the Using Keyword. In VB.NET, any object type that Exposes the IDisposable interface should be Disposed after it has been used so that any resources can then be released back to the system. If you use a Using block to define an Object any used resources are then automatically released back to the system when its associated End Using statement is encountered.

Hope that helps.

Cheers,

Ian

 

[klyxmaster]

many thanks!!!