Viewing Data in Datagrid

[bajanpoet]

I've read up on attaching a dataset to a datagrid and followed all the code snippets I can, but I am still having problems viewing data.

Have a look at this code:

Private Sub ViewCustomers(ByVal vRegion As String)
        conn.Open()
        Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers('" & vRegion & "')", conn)
        Dim r As SqlDataReader
        r = SQLComm.ExecuteReader()
        dgCustomers.DataSource = r
        conn.Close()
    End Sub

The SqlCommand statement within the line Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers('" & vRegion & "')", conn) references a multistatement table function (ViewCustomers(vRegion)) that works when I run the SELECT statement within SQL itself.

I am trying to view the table returned from the function in the datagrid, but it is not updating. What am I missing? I even rewrote the Sub to try to run the code in a dataadapter rather than an SqlCommand, but no results.

Thanks for your help!

[rapture]

Do you bind the data to the datagrid somewhere? I'm newer but have successfully used the datagrid on a previous project. (Although I used a stored procedure to make the SQL call)

I do know I had to bind the info to the datagrid . . .
I actually think it was this thread that got me on the binding
http://www.daniweb.com/forums/thread10004.html

[bajanpoet]

Thanks Rapture....

I'm reading the suggested thread, so I'm researching data binding for VB.NET as apposed to C#. Keep you posted!

[bajanpoet]

I'm not able to ascertain how to bind this datagrid... I was trying dgCustomers.setdatabinding(ds, "region") but it says that "SetDatabinding' is not a member of 'System.Windows.Forms.DataGridView'... I'm a little lost

[rapture]

Did you happen to see this code in your searching?

Imports System.Data
Imports System.Data.OleDb
' some code here
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' create a connection string
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.mdb"
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
' create a data adapter
Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from Customers", myConnection)
' create a new dataset
Dim ds As DataSet = New DataSet
' fill dataset
da.Fill(ds, "Customers")
' write dataset contents to an xml file by calling WriteXml method
' Attach DataSet to DataGrid
DataGrid1.DataSource = ds.DefaultViewManager
End Sub
End Class

*taken from http://www.vbdotnetheaven.com/UploadFile/mahesh/DataGridSamp04232005050133AM/DataGridSamp.aspx

[bajanpoet]

Looks familiar, but I don't think I used this code on this occasion... it does look like I saw it while researching another day, though. The DataGrid1.DataSource = ds.DefaultViewManager didn't seem to work for me when I tried it last. Will try it again and see what happens...

[bajanpoet]

Tried to run the application and it compiled and started without a problem, but when I choose the radio buttons that would fire the required code, nothing shows up in the datagrid.

Here is a look at what I executed:

Private Sub ViewCustomers(ByVal vRegion As String)
        conn.Open()

        'Create Data adapter
        Dim da As New SqlDataAdapter("SELECT * FROM ViewCustomers('" & vRegion & "')", conn)

        'Create and fill dataset
        Dim ds As New DataSet
        da.Fill(ds, "Region")
        dgCustomers.DataSource = ds.DefaultViewManager
        conn.Close()
    End Sub

[rapture]

Again, I'm newer to programming so forgive me if I ask a dumb question. First of all, let me apologize as to not seeing that you were using sqlAdapter instead of oleDbDataAdapter. Now, you do have the database connection string formatted properly somewhere right?

(just in case, here is some sample code for the sqlAdapter)

Private Const SELECT_STRING As String = _
    "SELECT * FROM Contacts ORDER BY LastName, FirstName"
Private Const CONNECT_STRING As String = _
    "Data Source=Bender\NETSDK;Initial " & _
        "Catalog=Contacts;User Id=sa"

' The DataSet that holds the data.
Private m_DataSet As DataSet

' Load the data.
Private Sub Form1_Load(ByVal sender As Object, ByVal e As _
    System.EventArgs) Handles MyBase.Load
    Dim data_adapter As SqlDataAdapter

    ' Create the SqlDataAdapter.
    data_adapter = New SqlDataAdapter(SELECT_STRING, _
        CONNECT_STRING)

    ' Map Table to Contacts.
    data_adapter.TableMappings.Add("Table", "Contacts")

    ' Fill the DataSet.
    m_DataSet = New DataSet()
    data_adapter.Fill(m_DataSet)

    ' Bind the DataGrid control to the Contacts DataTable.
    dgContacts.SetDataBinding(m_DataSet, "Contacts")
End Sub

* from http://www.vb-helper.com/howto_net_datagrid.html

[rapture]

Here is the code I've worked with on page load

'import sql server connection namespace
Imports System.Data.SqlClient
Public Class WebForm1
    Inherits System.Web.UI.Page
    'inherit sql server client



    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here

        Dim conITRequest As SqlConnection
        Dim cmdSelect As SqlCommand
        Dim dtrRequests As SqlDataReader
        lblError.Text = ""
        Try
            'Create new connection object
            conITRequest = New SqlConnection
            'define connection string to connect to database
            conITRequest.ConnectionString = "Server=SERVERNAME;UID=ITRequestUser;PWD=PASSWORD;Database=ITRequest"
            'create command object to get data from database
            cmdSelect = New SqlCommand
            'populate command text for command to issue
            cmdSelect.CommandText = "select RequestID, RequestUser ,  RequestDate , RequestName , PriorityID , DateDue , StatusID , PercentComplete , AssignedUser, ClosedDate , TimeSpent from Request"
            'assign connection to Command Object
            cmdSelect.Connection = conITRequest
            'open database connection
            conITRequest.Open()
            'get data from database using command object into a datareader
            dtrRequests = cmdSelect.ExecuteReader()
            'set datasource on grid - this associates the datasource with the grid
            dgrdRequest.DataSource = dtrRequests
            'bind to datagrid - the databind function is what actually ties the data to the grid
            dgrdRequest.DataBind()
        Catch ex As Exception
            lblError.Text = ex.Message
        Finally
            conITRequest.Close()
        End Try

    End Sub

End Class

[bajanpoet]

Yeah the connection is built in a module, so that's why my code only has the conn.open() in this snippet. I call the module in the Form_Load of this form...

To verify that data is being returned, I changed the code to bring up MessageBoxes to show the data that has been read, like this:

conn.Open()

        'Create Data adapter
        'Dim da As New SqlDataAdapter("SELECT * FROM ViewCustomers('" & vRegion & "')", conn)
        Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers('" & vRegion & "')", conn)
        Dim r As SqlDataReader
        r = SQLComm.ExecuteReader()
        While r.Read()
           MessageBox.Show("ID: " & r(0).ToString & "   Name: " & r(1).ToString)
        End While
        'dgCustomers.DataSource = ds.DefaultViewManager
        conn.Close()

This shows message boxes with the relevant information in... so I know the function is returning values. It also tests my connection module. It just won't show up in the datagrid....

[rapture]

Try replacing:

dgCustomers.DataSource = ds.DefaultViewManager

with

dgCustomers.DataSource = r
dgCustomers.DataBind()

....

[rapture]

And I know that others are reading this, help us out here lol

[bajanpoet]

Sorry, couldn't get to this yesterday evening...

I tried the Databind() function, but VB says that Databind is not a member of the 'System.Windows.Forms.DataGridView'.

[4advanced]

Databind() is a function which needs to be called when binding data to a datagrid in ASP.NET !

Only use datasource when binding in Windows.Forms solutions!

yourGrid.DataSource = yourDataSource

If you had checked the posts on the first page you would have found out the solution....it has been mentioned a few times before!!!!

[bajanpoet]

It's not working. I've been doing everything people have requested from the time I asked for help. I know it's supposed to work, and I've already "checked the posts on the first page", thank you very much. I would appreciate that you do not insult my intelligence.

[bajanpoet]

This is the code that worked for me.

dgCustomers.DataSource = ds.Tables(0)

[rapture]

great!

[4advanced]

Euhh..... insulting your intelligence? Excuse me, that's not what i intended to mention.

Lets start over again. You are trying to view some table-data in a datagrid in a "windows application".
The data bound to the grid can be of different types. A dataset, a datatable, a dataview, a xml-file....you name it.....

If you use a dataset, you have to mention the datatable from the collection (dataset.Tables()) by number or name:
For example dataset.tables(0) or dataset.tables("Customers") if you ever attached a name to the table.

Try this example, its a form with 1 datagridview & 1 button called cmdDataView. If you load the form it will show 10 records and after clicking the button "cmdDataView" it will reload the datagrid with a descending sorted view of the data.

In the example I create also a _DataSet which isn't used at this particular moment.....

Imports System.Data

Public Class Form2

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Create a new dataset
        Dim _Dataset As DataSet = New DataSet
        'Create a new datatable called "Customers"
        Dim _DataTable As DataTable = New DataTable("Customers")

        'Create some columns in the datatable
        Dim _Column As DataColumn = New DataColumn
        _Column.DataType = Type.GetType("System.Int32")
        _Column.ColumnName = "ID"
        _Column.AutoIncrement = True
        _Column.AutoIncrementSeed = 1
        _Column.AutoIncrementStep = 1

        'add the column to the datatable
        _DataTable.Columns.Add(_Column)

        _Column = New DataColumn
        _Column.DataType = Type.GetType("System.String")
        _Column.ColumnName = "Name"

        'add the column to the datatable
        _DataTable.Columns.Add(_Column)


        'add some rows to the datatable
        For iCnt As Integer = 1 To 10
            Dim _DataRow As DataRow = _DataTable.NewRow
            _DataRow.Item("Name") = String.Format("Name {0}", iCnt.ToString)
            _DataTable.Rows.Add(_DataRow)
        Next

        Me.DataGridView1.DataSource = _DataTable


    End Sub

    Private Sub cmdDataView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDataView.Click
        Dim _DataTableTemp As New DataTable
        _DataTableTemp = Me.DataGridView1.DataSource
        Dim _DataView As DataView = _DataTableTemp.DefaultView
        _DataView.Sort = "ID desc"
        Me.DataGridView1.DataSource = _DataView
    End Sub

End Class

hmmm.... slightly too late :=)

[rapture]

4Advanced,

I struggle with this problem when working with international boards. I think what struck him was your use of exclamation points. It seemed in the thread like you were upset and/or dealing with someone stupid. What I have found mostly is that it's excitement, but not always negative especially from other countries than my own. It's hard to convey in a post if your feeling is such.

Thank you for your help, soon or late. It'll help the next person searching.

[4advanced]

Rapture,

Thank you for your clarifying words. What you describe is exactly what I was thinking about. Exclamation points are a real pain in the *** sometimes but they do give some expression to the typed text. In The Netherlands we have a phrase which declares these kind of interpretations " The soup is never been eaten thát hot as it is being served" (if translated well :=) )

Kind regards,
Richard
The Netherlands

[bajanpoet]

@Rapture - you hit the nail on the head... it did sound condescending a bit, and the exclamation marks didn't help.

@4advanced - your explanation did help clarify why what I did worked, so thank you for that. Sorry for getting upset, but it did feel like I was being talked down to. No hard feelings?

[4advanced]

Hehehe..... absolutely no hard feelings :=)
I am glad you figured out the databinding issue.

Binding can be done on tons of different ways, even runtime. You have to find the way which you are comfortable with.
My advice is, create a testproject and play with the datagridview, dataset, datatables, sorting of data etc. That will clarify the most. Try to build something like the management studio for sql-express.
Connect to a table, read the datacolumns into a gridview and so on...It's really not thát hard.... :=)

Regards,
Richard
The Netherlands

[bajanpoet]

Thanks! I'm about to send another post - so watch out! lol Need to view the entire content of the field Description without the user having to manually stretch the field title by dragging the right side. It's coming in a new post right now....