Solved

Function returns filled Dataset but wont display as intended in Datagrid

Posted on 2006-10-19
32
506 Views
Last Modified: 2008-02-01
Goodaye all

basic .net datagrid, im building an application for windows. I use vs 2003.

This is the function I am using to grab the data I need for my datagrid and other items on my window form. I return a dataset from a custom SQL class.
It will display the correct column names and even the correct amount of rows that I would expect to be in the dataset result. The problem is for each row and column the actual data is not shown as intended, but it shows '(null)' for each of the cells.
Would I need to further declare something in a collection of sorts for the grid? Is it to do with a seperate class and defining table styles? I tried just appending without the table style and get the exact same display error except the column names are then naturally the database field names.
Further down I have no problem accessing an individual column etc to display it in a combo or list box, I just cant append it like I have previously. I never had a problem before until now and the difference is im getting the dataset from an outside class, not within the same function.

Option Strict is ON


Private Sub setupData()
   
    ' create a new DataSet and DataRow interface
    Dim myDataSet As DataSet = New DataSet
    Dim myDataRow As DataRow

    myDataSet = sqlCommand.executeArraySQL("SELECT User,loggedIn,list,previousList" & _
        " FROM tm_User ORDER BY User", "Error setting up User option grid", CType(Me.ParentForm, main).User.ToString())

        ' assign it
    UserGrid.DataSource = myDataSet.Tables(0)

    ' set the datagrid properties to bind it to our data
    '-------------------------------------------------------------------
    ' set up User grid window columns etc as a style
    Dim ts As DataGridTableStyle = New DataGridTableStyle
    ts.MappingName = myDataSet.Tables(0).ToString()
    UserGrid.TableStyles.Add(ts)

    UserGrid.TableStyles(0).GridColumnStyles.Item(0).Width = 85
    UserGrid.TableStyles(0).GridColumnStyles.Item(0).HeaderText = "User"
    UserGrid.TableStyles(0).GridColumnStyles.Item(1).Width = 75
    UserGrid.TableStyles(0).GridColumnStyles.Item(1).HeaderText = "Logged in?"
    UserGrid.TableStyles(0).GridColumnStyles.Item(2).Width = 140
    UserGrid.TableStyles(0).GridColumnStyles.Item(2).HeaderText = "Assigned List"
    UserGrid.TableStyles(0).GridColumnStyles.Item(3).Width = 50
    UserGrid.TableStyles(0).GridColumnStyles.Item(3).HeaderText = "Previous List"

    ' now setup the other smaller lists
    ' clear the lists first
    removeUserList.Items.Clear()
    UsersListBox.Items.Clear()

    For Each myDataRow In myDataSet.Tables(0).Rows
      ' now add to the different User lists
      UsersListBox.Items.Add(myDataRow("User").ToString())
      removeUserList.Items.Add(myDataRow("User").ToString())
    Next

    ' deselect values!
    addUserNameText.ResetText()
    removeUserList.ResetText()

    ' clear the lists first
    systemListsListBox.Items.Clear()

    ' run the query
    sqlCommand.executeArraySQL("SELECT nameOfList FROM tm_listInformation ORDER BY nameOfList", _
        "Error getting the List Names", CType(Me.ParentForm, main).User.ToString())

    For Each myDataRow In myDataSet.Tables(0).Rows
      ' now add to the different User lists
      systemListsListBox.Items.Add(myDataRow("nameOfList").ToString())
    Next

    ' deselect values!
    systemListsListBox.ResetText()
  End Sub
0
Comment
Question by:bedind
  • 14
  • 9
  • 8
  • +1
32 Comments
 
LVL 9

Expert Comment

by:DjDezmond
ID: 17772083
Is it all tables that are not filling properly? Or just one?

Do you actually receive any errors? or does it just not work as expected?
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17772309
Hi

In your code there are no filling method for the dataset ?

if you already have filled your dataset ! then your initiliazing a new dataset with this statement

Dim myDataSet As DataSet = New DataSet

means that you now have a new dataset object with no data inside it!
else use
Dim myDataSet As DataSet

since you are working with different classes to retrive data you might have a scope problem. i cant tell for sure but check the scope for the dataset

your table style is OK

and to get some particular collum data you would loop trough your dataset and assign a specific collum to you cbo.box

eg.
                                                            fieldname
myDataSet.Tables("User").Rows(0).Item("Surname")


                                                        col.no ------> of the fieldname
myDataSet.Tables("User").Rows(0).Item(3)











0
 
LVL 3

Author Comment

by:bedind
ID: 17772430
DJ and vbturbo:

there is on this particular window a datagrid, a combo box and 2 listboxes. Everything except the datagrid shows the data as expected.
The datagrid just shows nulls instead of the values I expect to see (eg 10*4 columns of (null)'s )

I might check the scope of the dataset. Its definately getting populated in the class of mine, I can access any cell that I know would exist in my returned dataset and msgbox it, and as I said, I can get it to display the user names and another set of data in the combo and list boxes.

its just when dealing with the datagrid. Perhaps I cannot do this and need to display it different, its very frustrating.

I dont want to have to manually write each row to the datagrid.....uhhh...
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17772548
Try alter

 UserGrid.DataSource = myDataSet.Tables("User")

but the data in the dataset is ok and inside scope since your list's and cbo's are populated from the same dataset! in the same event

do you have multiple tables in the same dataset?




0
 
LVL 34

Expert Comment

by:Sancler
ID: 17774025
Try changing this

    ts.MappingName = myDataSet.Tables(0).ToString()

to this

    ts.MappingName = myDataSet.Tables(0).TableName

Roger
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17775278
Despite my earlier suggestion, I don't now think this is anything to do with the formatting/mapping of the tablestyles/datagrid.  I was just conscious that datatable.ToString can sometimes return other than just the TableName.  But, if that were to happen, the results would - while still being "wrong" - I think be different.

My guess now is that the problem is intrinsic to your sqlCommand.executeArraySQL method.  You call that method twice.  The first time in the line

    myDataSet = sqlCommand.executeArraySQL(etc)

and the second time in the line

    sqlCommand.executeArraySQL(etc)

After the first call you use the contents of myDataSet.Tables(0) - which I must assume the method fills - to bind the grid and automatically put the columnstyles (which you then adjust) in its tablestyle.  You also use it to fill removeUserList and UsersListBox.

You then call it again - although this time without setting myDataSet as its return value - and then go on to use myDataSet.Tables(0) again, but this time to fill systemListsListBox.  The row item that you refer to this time (that is, "nameOfList") does not appear to have been present the first time you used myDataSet.Tables(0).

So I reckon that what is happening is that the second call of your method is changing/refilling myDataSet.Tables(0) after you have used it to bind your grid, and set your tablestyles/columnstyles with it.  This would - as you report - leave removeUserList, UsersListBox and  systemListsListBox showing the "right" values.  But, by the end of the sub, the table to which the grid was bound would no longer contain the data relevant to that binding.

Roger
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17777341
Very impressive Roger

As i see it too.
He's filling in a second table in the dataset but assigning/reference to Tables(0) once more. (so to say)

would be interresting if he could set a break point after he added the.......... UserGrid.TableStyles.Add(ts)
and then visualized the dataset in debug mode to see if the data in the dataset are as expected.

and then did it once more after running
                                                           the second time in the line
                                                           sqlCommand.executeArraySQL(etc)

this would confirm your assumption/prediction

thumbs up.....:)
0
 
LVL 3

Author Comment

by:bedind
ID: 17778405
Sorry guys, I should have posted an update of the code in the last day, I did notice this yesterday and fixed it, what that did was append to the datagrid the colum I tried to get with my second call.

Updated code:

' system callback lists
        ' --------------------------------------
        ' clear the lists first
        systemListsListBox.Items.Clear()
        myDataSet = New DataSet

        ' run the query
        myDataSet = sqlCommand.executeArraySQL("SELECT nameOfList FROM tm_listInformation ORDER BY nameOfList", _
                "Error getting the List Names", CType(Me.ParentForm, main).login.ToString())

        For Each myDataRow In myDataSet.Tables(0).Rows
            ' now add to the different telemarketer lists
            systemListsListBox.Items.Add(myDataRow("nameOfList").ToString())
        Next

        ' deselect values!
        systemListsListBox.ResetText()
        ' --------------------------------------

This way I am definately getting a new dataset.

Its still populating my grid incorrectly with nulls. Ill try the debug...tried the table name change (.TableName) on the ts.mappingName but to no avail :(





0
 
LVL 3

Author Comment

by:bedind
ID: 17778411
now both my listboxes and combo box, fill correctly as you can see in the code, but the datagrid remains a table of nulls.

trying debug
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17779310
I think we need to see the code for your method sqlCommand.executeArraySQL()

Roger
0
 
LVL 3

Author Comment

by:bedind
ID: 17782565
goodaye fellas, thanks for the persistance:
------------------------------------------------
Option Strict On

Imports System.data
Imports System.data.Odbc
Imports System.DirectoryServices


Public Class sqlAccess
    ' for connecting to SQL and donor management
    Private sqlConnection, dmConnection As OdbcConnection

    ' the data adpater, read up on how this works
    Private dbDataAdapter As New OdbcDataAdapter

    ' for reading our lines FROM sql or donor management, one record at a time
    Private dataReader As OdbcDataReader

    ' the data set for storing what comes from the data adapter
    Private dbSQLSet As DataSet = New DataSet

    ' stores our connection string to use elsewhere
    Private sqlConnectionString, dmConnectionString As String

    Public Function executeArraySQL(ByVal statement As String, ByVal errorType As String, ByVal login As String) As DataSet

        dbDataAdapter.SelectCommand = New OdbcCommand
        dbDataAdapter.SelectCommand.Connection = sqlConnection
        dbDataAdapter.SelectCommand.CommandText = statement
        dbDataAdapter.SelectCommand.CommandType = CommandType.Text

        Try
            ' open the database connection
            sqlConnection.Open()

            ' fill the dataset with data (first must clear it to kill existing data)....
            dbSQLSet.Clear()
            dbDataAdapter.Fill(dbSQLSet, "resultData")
            ' close the database
            sqlConnection.Close()

        Catch ex As Exception
            sqlConnection.Close()
            MsgBox(errorType & ControlChars.CrLf & ControlChars.CrLf & "statement: " & statement _
                & ControlChars.CrLf & ex.Message, MsgBoxStyle.Exclamation, "Whoops!")
            ' and send error to IT
            errorEmail(errorType & ControlChars.CrLf & ControlChars.CrLf & "statement: " & statement _
                    & ControlChars.CrLf & ex.Message, login)
        End Try

        ' return the dataset
        Return dbSQLSet

    End Function
    Public Sub errorEmail(ByVal errorMsg As String, ByVal login As String)

        Dim oMsg As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage

        oMsg.From = "fundraising@bedfordindustries.com.au"
        oMsg.To = "mbutt@bedfordindustries.com.au"
        oMsg.Subject = "Telemarketing Error"
        oMsg.Body = "login: " & login & ControlChars.CrLf & _
                    "Telemarketing Error : " & errorMsg

        Try
            System.Web.Mail.SmtpMail.SmtpServer = "srvemail.<business name>.com.au"
            System.Web.Mail.SmtpMail.Send(oMsg)
        Catch ex As Exception
            MsgBox("The following exception occurred: " + ex.ToString(), MsgBoxStyle.Exclamation, "Whoops!")
            Exit Sub
        End Try
    End Sub

    Public Sub New()
        ' now lets connect to the fundraising tables
        sqlConnectionString = "DSN=telemarketing;"
        sqlConnection = New OdbcConnection(sqlConnectionString)

        ' Donor Management connection
        dmConnectionString = "DSN=DONMAN;DATABASE=DONMAN;UID=pfxuser"
        dmConnection = New OdbcConnection(dmConnectionString)
    End Sub
End Class

------------------------------


thats my entire SQL class I took out some functions to show on here since they dont pertain to the code for the user grid.
As you can see, upon declaration of the instance I grab the DSN's and connect, then I make the calls.
I cant see anything here that would make the nulls show instead of my intended data.

Thanks! Try it yourselves! see if you can get the same error

0
 
LVL 34

Expert Comment

by:Sancler
ID: 17783410
Here's your answer.  The comment says it all.

            ' fill the dataset with data (first must clear it to kill existing data)....
            dbSQLSet.Clear()
            dbDataAdapter.Fill(dbSQLSet, "resultData")

As I guessed, having filled the table with the data for the datagrid (and two of the listboxes) on the first call to this method, the second call clears that data out and puts different data in.  So the data that the datagrid's binding is expecting is no longer there.  Which is why it is showing all nulls so far as that data is concerned.

Roger
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17783510
Hi


I modified your code a bit. instead running the second query i used the first query to fill the actual tables that you are working with

    Public Function executeArraySQL(ByVal myDataSet As DataSet) As DataSet

        dbDataAdapter.SelectCommand = New OdbcCommand
        dbDataAdapter.SelectCommand.Connection = sqlConnection
        dbDataAdapter.SelectCommand.CommandText = statement
        dbDataAdapter.SelectCommand.CommandType = CommandType.Text

        Try
            ' open the database connection
            sqlConnection.Open()

            ' fill the dataset with data (first must clear it to kill existing data)....
            ' fill 3 tables in dataset
            myDataSet.Clear()
            dbDataAdapter.Fill(myDataSet, "resultData")
            dbDataAdapter.Fill(myDataSet, "tm_User") ' assigns to the datagrid
            dbDataAdapter.Fill(myDataSet, "tm_listInformation") ' assigns to the listboxes and combo box
            ' close the database
            sqlConnection.Close()

        Catch ex As Exception
            sqlConnection.Close()
            MsgBox(errorType & ControlChars.CrLf & ControlChars.CrLf & "statement: " & statement _
                & ControlChars.CrLf & ex.Message, MsgBoxStyle.Exclamation, "Whoops!")
            ' and send error to IT
            errorEmail(errorType & ControlChars.CrLf & ControlChars.CrLf & "statement: " & statement _
                    & ControlChars.CrLf & ex.Message, login)
        End Try

        ' return the dataset
        Return myDataSet

    End Function


Private Sub setupData()
   
    ' create a new DataSet and DataRow interface
    Dim myDataSet As DataSet = New DataSet
    Dim myDataRow As DataRow

     executeArraySQL(myDataSet)

        ' assign it
    UserGrid.DataSource = myDataSet.Tables("tm_User")

    ' set the datagrid properties to bind it to our data
    '-------------------------------------------------------------------
    ' set up User grid window columns etc as a style
    Dim ts As DataGridTableStyle = New DataGridTableStyle
    ts.MappingName = myDataSet.Tables("tm_User").TableName
    UserGrid.TableStyles.Add(ts)

    UserGrid.TableStyles(0).GridColumnStyles.Item(0).Width = 85
    UserGrid.TableStyles(0).GridColumnStyles.Item(0).HeaderText = "User"
    UserGrid.TableStyles(0).GridColumnStyles.Item(1).Width = 75
    UserGrid.TableStyles(0).GridColumnStyles.Item(1).HeaderText = "Logged in?"
    UserGrid.TableStyles(0).GridColumnStyles.Item(2).Width = 140
    UserGrid.TableStyles(0).GridColumnStyles.Item(2).HeaderText = "Assigned List"
    UserGrid.TableStyles(0).GridColumnStyles.Item(3).Width = 50
    UserGrid.TableStyles(0).GridColumnStyles.Item(3).HeaderText = "Previous List"

    ' now setup the other smaller lists
    ' clear the lists first
    removeUserList.Items.Clear()
    UsersListBox.Items.Clear()
   dim i as integer=0
    For Each myDataRow In myDataSet.Tables("tm_listInformation").Rows
      ' now add to the different User lists
      UsersListBox.Items.Add(myDataRow.Rows(i).Item("User").ToString())
      removeUserList.Items.Add(myDataRow.Rows(i).Item("User").ToString())
     i+=1
    Next

    ' deselect values!
    addUserNameText.ResetText()
    removeUserList.ResetText()

    ' clear the lists first
    systemListsListBox.Items.Clear()

   i=0
    For Each myDataRow In myDataSet.Tables("tm_listInformation").Rows
      ' now add to the different User lists
      systemListsListBox.Items.Add(myDataRow.Rows(i).Item("nameOfList").ToString())
     i+=1
    Next

    ' deselect values!
    systemListsListBox.ResetText()
  End Sub


hope this helps
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17784157
vbturbo

I don't think that will work as it stands.  (a) where does the single dataadapter in executeArraySQL get its select statement/s from? and (b) how does it change from

SELECT User,loggedIn,list,previousList FROM tm_User ORDER BY User

to

SELECT nameOfList FROM tm_listInformation ORDER BY nameOfList

between its various .Fill commands?

I think the simplest answer, using the code more or less as it stands, is just to re-order the code in the original sub so that

        ' clear the lists first
        systemListsListBox.Items.Clear()
        myDataSet = New DataSet

        ' run the query
        myDataSet = sqlCommand.executeArraySQL("SELECT nameOfList FROM tm_listInformation ORDER BY nameOfList", _
                "Error getting the List Names", CType(Me.ParentForm, main).login.ToString())

        For Each myDataRow In myDataSet.Tables(0).Rows
            ' now add to the different telemarketer lists
            systemListsListBox.Items.Add(myDataRow("nameOfList").ToString())
        Next

        ' deselect values!
        systemListsListBox.ResetText()

comes before

    myDataSet = sqlCommand.executeArraySQL("SELECT User,loggedIn,list,previousList" & _
        " FROM tm_User ORDER BY User", "Error setting up User option grid", CType(Me.ParentForm, main).User.ToString())

        ' assign it
    UserGrid.DataSource = myDataSet.Tables(0)

    ' set the datagrid properties to bind it to our data
    '-------------------------------------------------------------------
    ' set up User grid window columns etc as a style
    Dim ts As DataGridTableStyle = New DataGridTableStyle
    ts.MappingName = myDataSet.Tables(0).ToString()
    UserGrid.TableStyles.Add(ts)

    UserGrid.TableStyles(0).GridColumnStyles.Item(0).Width = 85
    UserGrid.TableStyles(0).GridColumnStyles.Item(0).HeaderText = "User"
    UserGrid.TableStyles(0).GridColumnStyles.Item(1).Width = 75
    UserGrid.TableStyles(0).GridColumnStyles.Item(1).HeaderText = "Logged in?"
    UserGrid.TableStyles(0).GridColumnStyles.Item(2).Width = 140
    UserGrid.TableStyles(0).GridColumnStyles.Item(2).HeaderText = "Assigned List"
    UserGrid.TableStyles(0).GridColumnStyles.Item(3).Width = 50
    UserGrid.TableStyles(0).GridColumnStyles.Item(3).HeaderText = "Previous List"

    ' now setup the other smaller lists
    ' clear the lists first
    removeUserList.Items.Clear()
    UsersListBox.Items.Clear()

    For Each myDataRow In myDataSet.Tables(0).Rows
      ' now add to the different User lists
      UsersListBox.Items.Add(myDataRow("User").ToString())
      removeUserList.Items.Add(myDataRow("User").ToString())
    Next

That way, the data which ends up in myDataSet.Tables(0) is that required for the datagrid's binding.

But I didn't suggest that because, although it might solve the immediate problem, I've no idea when - elsewhere in the app - this method might be being called again, resulting in things going "wrong" again then.  So I really think a more radical change in approach might be necessary.  

I was hoping that bedind might, once the seat of the problem had been identified, be able to devise a solution appropriate to the app as a whole.  

Asking supplementary questions if necessary ;-)

Roger
0
 
LVL 3

Author Comment

by:bedind
ID: 17785549
Previously, the same function (executeArraySQL) was fine when it was on the same window form, its now that I call a seperate class function where im having the problem. For vbturbo's idea, I need it to be as slimeline as possible as many many windows will use this sql class, hence one result data and each window with its own dataset.

My app window form currently in this discussion has one loader function - the one I originally pasted, and an update one, which is exactly the same but does not have the tabestyle definitions. Both give the same result, so I know its irrelevant whether the table style exists.

Im unsure why reordering the data will have an effect because I get the expected results in my code after the datagrid function where I populate a listbox and combobox from the same dataset (myDataSet) that was populated via the executeArraySQL function, before I repopulate myDataSet with data from my SQL table 'tm_listInformation'

I have a few things I want to try today to see if it can fix it.

Thanks for the persistance!
0
 
LVL 3

Author Comment

by:bedind
ID: 17785560
"...But I didn't suggest that because, although it might solve the immediate problem, I've no idea when - elsewhere in the app - this method might be being called again, resulting in things going "wrong" again then.  So I really think a more radical change in approach might be necessary..."

Exactly, I need to solve it in this form before I hastily build the others and have exactly the same problem. Each app window is a child in a parent, each child then using an instance of the class, which of course calls the functions and populates a dataset instance belonging to each child.

Cheers guys
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:vbturbo
ID: 17785594
Hi Roger

Yeah i now see i was a bit to quick on the sumit button.
as you mention the SQL statements are missing when the adapter is going to fill the dataset...
Must say that you have hawk-eyes  ;-)
Its always nice to get a opinion/input from someone skillfull as you.

Jens

0
 
LVL 3

Author Comment

by:bedind
ID: 17785621
Roger,

            ' fill the dataset with data (first must clear it to kill existing data)....
            dbSQLSet.Clear()
            dbDataAdapter.Fill(dbSQLSet, "resultData")

Im not sure why you see this as a problem, I can access the data by using msgboxes to show random cells I know would exist and show the expected data, and after the datagrid populate function I access the same dataset to populate the listbox and combobox and get the expected result. If I dont use dataset.Clear() I then get bizarre results. Im not clearing the dataset after I fill it but before to ensure I return a fresh dataset of data.

Cheers fellas
0
 
LVL 18

Accepted Solution

by:
vbturbo earned 200 total points
ID: 17785961
Hi bedind

Here it comes
You might have to modify/ add a bit but i have now tested the fill and functionality on a Access database

Private Sub setupData()
        'set the particular connection string (dmConnectionString or sqlConnectionString)
        ConnectionStringName = sqlConnectionString

        sTablename = "tm_User"
        sSql = "SELECT User,loggedIn,list,previousList  FROM tm_User ORDER BY User"

        SqlDataAccess.MyVarDs(sSql, sTablename, ConnectionStringName)
        UserGrid.DataSource = myDataSet.Tables(sTablename)

        ' set the datagrid properties to bind it to our data
        '-------------------------------------------------------------------
        ' set up User grid window columns etc as a style
        Dim ts As DataGridTableStyle = New DataGridTableStyle
        ts.MappingName = myDataSet.Tables(0).ToString()
        UserGrid.TableStyles.Add(ts)

        UserGrid.TableStyles(0).GridColumnStyles.Item(0).Width = 85
        UserGrid.TableStyles(0).GridColumnStyles.Item(0).HeaderText = "User"
        UserGrid.TableStyles(0).GridColumnStyles.Item(1).Width = 75
        UserGrid.TableStyles(0).GridColumnStyles.Item(1).HeaderText = "Logged in?"
        UserGrid.TableStyles(0).GridColumnStyles.Item(2).Width = 140
        UserGrid.TableStyles(0).GridColumnStyles.Item(2).HeaderText = "Assigned List"
        UserGrid.TableStyles(0).GridColumnStyles.Item(3).Width = 50
        UserGrid.TableStyles(0).GridColumnStyles.Item(3).HeaderText = "Previous List"

        ' now setup the other smaller lists
        ' clear the lists first
        removeUserList.Items.Clear()
        UsersListBox.Items.Clear()

        For Each myDataRow In myDataSet.Tables(sTablename).Rows
            ' now add to the different User lists
            UsersListBox.Items.Add(myDataRow("User").ToString())
            removeUserList.Items.Add(myDataRow("User").ToString())
        Next

        sTablename = "tm_listInformation"
        sSql = "SELECT nameOfList FROM tm_listInformation ORDER BY nameOfList"
        SqlDataAccess.MyVarDs(sSql, sTablename, ConnectionStringName)


        For Each myDataRow In myDataSet.Tables(sTablename).Rows
            ' now add to the different User lists
            systemListsListBox.Items.Add(myDataRow("nameOfList").ToString())
        Next
  End Sub

------------------add a module and paste this------------------------------------------


Imports System.Data.Odbc

Module constant
    'define the conection strings
    Public dmConnectionString = "DSN=DONMAN;DATABASE=DONMAN;UID=pfxuser"
    Public sqlConnectionString As String = "DSN=telemarketing;"

    ' declare the sqlAccess class
    Public SqlDataAccess As New sqlAccess

    Public ConnectionStringName As String
    Public m_cn As New OdbcConnection
    Public da As New OdbcDataAdapter
    Public sTablename As String
    Public sDsName As New DataSet
    Public myDataRow As DataRow
    Public myDataSet As New DataSet

End Module


-------------------------and the sqlAccess------------------------------------------

Imports System.Data.Odbc

Public Class sqlAccess

    Public Function MyVarDs(ByVal sSql As String, ByVal sTablename As String, ByVal ConnectionStringName As String) As DataSet
        Dim m_cn As New OdbcConnection(ConnectionStringName)
        '// PURPOSE: This function will return a filled dataadapter object for a given
        '// SQL expression.
        If m_cn.State <> ConnectionState.Open Then m_cn.Open()
        Try
            Dim da As New OdbcDataAdapter(sSql, m_cn)
            da.Fill(myDataSet, sTablename)
            m_cn.Close()
            Return myDataSet
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            m_cn.Close()
        End Try
        Return myDataSet
    End Function
End Class

hope this is better

vbturbo
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17785968
hi

sorry but change this


        ts.MappingName = myDataSet.Tables(0).ToString()
        UserGrid.TableStyles.Add(ts)

to

        ts.MappingName = myDataSet.Tables(sTablename).TableName
        UserGrid.TableStyles.Add(ts)

0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17786065
sorry again

but it happens sometimes when you out comment
change
Public dmConnectionString = "DSN=DONMAN;DATABASE=DONMAN;UID=pfxuser"
to
Public dmConnectionString As String = "DSN=DONMAN;DATABASE=DONMAN;UID=pfxuser"
0
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 300 total points
ID: 17787112
I think there may be some misunderstanding here of two points.

1) How dataadapter.Fill works.

When you call

            dbDataAdapter.Fill(dbSQLSet, "resultData")

the instructions implicit in that are

a) if no datatable called "resultData" already exists in dbSQLSet then create one
b) for each field/column in the database table referred to in the dataadapter's select command, if no column already exists in the datatable then create one
c) for each row in the database table referred to bring over the data from the field/column specified and put it in the appropriate column in the datatable.

So, when you first call that line with the select command

     SELECT User,loggedIn,list,previousList FROM tm_User

the answer to (a) is that there is no datatable called "resultData" so one is created.  The answer under (b) is that there are no columns called "User", "loggedIn", "list" and "previousList" in the datatable "resultData", so they are created: it has four columns.  And then - under (c) - each of the four columns is filled with the data from the database.

When you call that line for the second time, with the select command

     SELECT nameOfList FROM tm_listInformation

the answer under (a) is now that there is a datatable called "resultData" so that is re-used.  The answer under (b) is that there is no column called "nameOfList" in that datatable, so one is created: now it has five columns.  Under (c), although there are now five columns, the only data that is specified to be brought over is "nameOfList", so that is the only column in "resultData" that gets filled.  The other four columns have no values put in them and so are null.

2) How databinding works.

The line

    UserGrid.DataSource = myDataSet.Tables(0)

creates a link between the datagrid and the datatable.  Whenever the data in the datatable alters, that is reflected in the datagrid.  Any appearance that the datagrid actually CONTAINS the data it is showing is misleading.  It is only DISPLAYING data as it exists, from time to time, in the datatable to which it is bound.  This is different from how (in this case anyway) the lists work.  What is happening with those, with

    For Each myDataRow In myDataSet.Tables(0).Rows
      ' now add to the different User lists
      UsersListBox.Items.Add(myDataRow("User").ToString())
      removeUserList.Items.Add(myDataRow("User").ToString())
    Next

and the like, is that the code is copying the data that exists in the datatable AT THE TIME THE CODE IS CALLED into the listbox.  So the listboxes do CONTAIN the data that they are displaying.  Any subsequent change to the datatable has no effect on them.  This is why, although the lists and the datagrid are initially filled from the same source, the subsequent alteration to the datatable is not reflected in the lists but is reflected in the datagrid.

For the above reasons, I think you are going to have to revise your approach.  That revision may well be on the lines that vbturbo has set out.  But because you are using your class in a number of settings - and only you know the details of all that - you will need to make sure that in solving one problem you don't create others.  To do that, you will need to understand what is going on in the background.

Hence, with apologies, the lecture.

Roger
0
 
LVL 3

Author Comment

by:bedind
ID: 17792343
Roger thanks heaps. I can see where its going wrong now. I removed the code after the datagrid and as you implied, it now fills correctly. This is tricky now. I figured I could just return a dataset, and if I declared an instance of one, it would copy it, im presuming its linking to that dataset when I do :

Dim myDataSet and New dataSet

Not copying as I expected. Its behaving like c pointers.

Is it possible to perhaps return a dataset and not have it point to the table, rather copy the dataset contents?
Another way I could try is using a dataset for any grid functions and single lists perhaps return an array of data instead of the dataset.
Ill share out the points because you both helped a lot and made me smarten the code a touch.

If you can submit any further ideas, it would be helpful.

Thanks

0
 
LVL 3

Author Comment

by:bedind
ID: 17792499
A question I have, if I create another window in my app with another datagrid, does this mean by creating a new instance of my sql class int hat window form and using the same dataset return functiion, Im going to get null issues again? Or will it be ok because its a seperate class instance.
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17793908
Hi

In this case where myDataset is declared globally(so to speak..which is not very oop) you have access to all tables that are queryed / loaded in into that dataset once.(Take care that you dont query/load it twice !  in case of makeing changes to a table that is not yet updated in the datasource)

Meaning that you at anytime can access these tabledata from any form..etc  as you like in that dataset.

By running a new instance of my sql class  the dataset still contains the earlier queryed data"tables"(they persist) as long you seperate the tablenames and dont clear the dataset eg.    (myDataset.clear).
Thats why its a god idea to avoid myDataSet.Tables(0).Rows and instead use myDataSet.Tables(tablename).Rows when useing multiple tables in one dataset

Means that you can make any change as you like without it is reflecting on others dynamic bound controls (as Roger mentioned) unless it is intended (like "crud" changes that you want to reprensent.

Module constant

    Public myDataSet As New DataSet

End Module

vbturbo
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17793971
Forgot to mention that you can add on more tables into that dataset also ! also from other instance's
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17794275
I have to disagree with vbturbo on one point.  If you use different instances of your class - e.g. one on each form - each instance will have its own dataset.  With the present design of your class, every time you do anything with a single instance of your class it will affect, in some way, that instance's dataset.  But doing something with one instance of your class will not affect the dataset in any other instance of your class.

But I do agree with vbturbo that what you really need to do is change the emphasis in your approach from dataset/s to datatable/s and that you would do best to give your datatables names and refer to them with those

    myDataSet.Tables("myName")

rather than by index - myDataSet.Tables(0).  At its simplest, all this would require would be to amend the signature of your method by adding a TableName variable.  Like this

    Public Function executeArraySQL(ByVal tablename As String, ByVal statement As String, ByVal errorType As String, ByVal login As String) As DataSet

and then replacing this

            ' fill the dataset with data (first must clear it to kill existing data)....
            dbSQLSet.Clear()
            dbDataAdapter.Fill(dbSQLSet, "resultData")
            ' close the database
            sqlConnection.Close()


with this

            dbDataAdapter.Fill(dbSQLSet, tablename)

Then, in your SetUpData sub, these lines

    myDataSet = sqlCommand.executeArraySQL("SELECT User,loggedIn,list,previousList" & _
        " FROM tm_User ORDER BY User", "Error setting up User option grid", CType(Me.ParentForm, main).User.ToString())

        ' assign it
    UserGrid.DataSource = myDataSet.Tables(0)

    ' set the datagrid properties to bind it to our data
    '-------------------------------------------------------------------
    ' set up User grid window columns etc as a style
    Dim ts As DataGridTableStyle = New DataGridTableStyle
    ts.MappingName = myDataSet.Tables(0).ToString()
    '[...]
    For Each myDataRow In myDataSet.Tables(0).Rows

could become

    myDataSet = sqlCommand.executeArraySQL("userTable", "SELECT User,loggedIn,list,previousList" & _
        " FROM tm_User ORDER BY User", "Error setting up User option grid", CType(Me.ParentForm, main).User.ToString())

        ' assign it
    UserGrid.DataSource = myDataSet.Tables("userTable")

    ' set the datagrid properties to bind it to our data
    '-------------------------------------------------------------------
    ' set up User grid window columns etc as a style
    Dim ts As DataGridTableStyle = New DataGridTableStyle
    ts.MappingName = "gridTable"
    '[...]
    For Each myDataRow In myDataSet.Tables("userTable").Rows

and this

    ' run the query
    sqlCommand.executeArraySQL("SELECT nameOfList FROM tm_listInformation ORDER BY nameOfList", _
        "Error getting the List Names", CType(Me.ParentForm, main).User.ToString())

    For Each myDataRow In myDataSet.Tables(0).Rows
      ' now add to the different User lists
      systemListsListBox.Items.Add(myDataRow("nameOfList").ToString())
    Next

could become

    ' run the query
    sqlCommand.executeArraySQL("listTable", "SELECT nameOfList FROM tm_listInformation ORDER BY nameOfList", _
        "Error getting the List Names", CType(Me.ParentForm, main).User.ToString())

    For Each myDataRow In myDataSet.Tables("listTable").Rows
      ' now add to the different User lists
      systemListsListBox.Items.Add(myDataRow("nameOfList").ToString())
    Next

Let me stress that I am not saying that the above is the "right" approach.  As I keep saying, what is right will depend on details about your app in general which only you know.  But it would overcome the immediate problem with a minimum of amendment to your current code.

Roger
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17794285
Lets say you open another form


example

you just past these lines in

'define the table you want to fetch

sTablename = "tm_User"

        For Each myDataRow In myDataSet.Tables(sTablename).Rows
            ' now add to the different User lists
            systemListsListBox.Items.Add(myDataRow("another_field_in_the_table").ToString())
        Next

or

sTablename = "pick_a_table_that_is_present_in_the_dataset"

AnotherGridname.DataSource = myDataSet.Tables(sTablename)


0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17794306
sorry Roger i didnt see your post untill after i submitted where i got the updated page
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17794765
But Roger, as i see it

this particular dataset "myDataSet" is now (shared) throughout the whole project/solution and any changes made to it, is addressed (has a pointer)i the heap.

therefore it would be the same dataset in the different instance's in different classes

please correct me if i am wrong

i am just a humble self teached entutiast as you who wants to become a little more experience'd

Jens
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17795223
Jens

The CLASS is sqlAccess.  The INSTANCE of that class which is used in the sub setupData is sqlCommand.  Certainly if that was to be the only instance of that class, declared somewhere "globally" and used by all forms, I'd go along with you.  And we are not actually shown the code by which it is declared, so I suppose that is a possibility.  But bedind says (EMPHASIS added) "Each app window is a child in a parent, each child then using an INSTANCE of the class, which of course calls the functions and populates a dataset instance belonging to each child."  So my understanding is that there will be a number of separate instances of the class.  And in that case each instance's dataset will, as it is declared as follows

    ' the data set for storing what comes from the data adapter
    Private dbSQLSet As DataSet = New DataSet

at the start of the class definition, be a different object from each other instance's dataset.

Here's a little demo to illustrate.  Just copy and paste the whole thing into a new project and set the start form as myForm.

Public Class myForm
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Button2 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.Button2 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(8, 8)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(104, 20)
        Me.TextBox1.TabIndex = 0
        Me.TextBox1.Text = "TextBox1"
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(120, 8)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(56, 24)
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Button1"
        '
        'Button2
        '
        Me.Button2.Location = New System.Drawing.Point(184, 8)
        Me.Button2.Name = "Button2"
        Me.Button2.Size = New System.Drawing.Size(56, 24)
        Me.Button2.TabIndex = 2
        Me.Button2.Text = "Button2"
        '
        'myForm
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(248, 46)
        Me.Controls.Add(Me.Button2)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.TextBox1)
        Me.Name = "myForm"
        Me.Text = "myForm"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private _myName As String

    Public Sub SetMyName(ByVal name As String)
        _myName = name
        Me.Text = _myName
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim frm As New myForm
        frm.SetMyName(TextBox1.Text)
        frm.Show()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        MsgBox(_myName)
    End Sub
End Class

In the class, the declaration of _myName is equivalent to the declaration of the dataset.  Its sub is equivalent to filling that dataset (and, for this demo's purposes, sticks the name in the caption so we know where we're at).  The button1 code creates a new instance of the class and sets its name to whatever is entered in the textbox.  Click that a few times with different text entered in the textbox.  Now, if _myName was global, it should mean that its value was now the same in all the forms.  But the button2 code demonstrates that it is not.  It depends on the instance.

Roger
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17795843
Thanks Roger for the demostration and sharing out of your experience.

I now see your point. -;)


Jens
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now