CODEWITHSUNDEEP
vb.netms-access
George Phillipson
George Phillipson

Reputation: 860

Retrieving data from Access 2010 view with VB.NET 2008 throws error

I'm trying to learn VB.NET and I'm looking to retrieve data from an Access 2010 view, using Vb.NET 2008, problem is I'm getting the following error. System.ArgumentNullException was unhandled Message=Value cannot be null

I cannot work out how to solve the error, so hopefully someone with more experience can help.

Public Function GetMyoleDataAdapterStudentQuestionRepeat(ByRef mydataSet As DataSet, ByVal topicId As String, ByVal groupId As String) As OleDbDataAdapter
            Try
                Dim strAccessConn As String = _appConfigDbConn
                Dim cn As OleDbConnection = New OleDbConnection(strAccessConn)
                Dim ds As New DataSet
                Dim da As New OleDbDataAdapter("qryStudentNameRandomBasedOnScore", cn)
                da.SelectCommand.CommandType = CommandType.StoredProcedure
                da.SelectCommand.Parameters.AddWithValue("@GroupID", groupId)
                da.Fill(ds, "Student")
                Return da
            Catch ex As Exception
                Throw New ApplicationException(ex.InnerException.Message.ToString())
            End Try
        End Function

Within button click

Dim myoleDataAdapter As OleDbDataAdapter = GroupData.GetMyoleDataAdapterStudentQuestionRepeat(mydataSet, topicId, groupId)
            myoleDataAdapter.Fill(mydataSet)
            txtStudentName.DataBindings.Add("Text", mydataSet.Tables(0), "studentname")

This line throws error: myoleDataAdapter.Fill(mydataSet)

And in case it helps, my view is 

SELECT TOP 1 tblStudentNameAndScore.studentname
FROM tblStudentNameAndScore
WHERE (((tblStudentNameAndScore.[QuizCount]) Between 2 And 10)) AND  tblStudentNameAndScore.GroupID = ?
ORDER BY Rnd(QuizCount);

Thanks for any hep

Upvotes: 1

Views: 672

Answers (2)

Steve
Steve

Reputation: 216333

In this line

myoleDataAdapter.Fill(mydataSet)

you try to fill the dataset but this dataset is never initialized.

Following your actual pattern, you should change your code that initialize the dataadapter in this way

Public Function GetMyoleDataAdapterStudentQuestionRepeat(ByVal topicId As String, ByVal groupId As String) As OleDbDataAdapter
    Try
        Dim strAccessConn As String = _appConfigDbConn
        Dim cn As OleDbConnection = New OleDbConnection(strAccessConn)
        Dim ds As New DataSet
        Dim da As New OleDbDataAdapter("qryStudentNameRandomBasedOnScore", cn)
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        da.SelectCommand.Parameters.AddWithValue("@GroupID", groupId)
        Return da
    Catch ex As Exception
        Throw New ApplicationException(ex.InnerException.Message.ToString())
    End Try
        End Function

and then in the caller code write

 Dim myoleDataAdapter As OleDbDataAdapter = GroupData.GetMyoleDataAdapterStudentQuestionRepeat( topicId, groupId)
 mydataSet = new DataSet()
 myoleDataAdapter.Fill(mydataSet)
 ....

In the method that returns the adapter you don't need to pass the dataset because you don't use it in any way and there is no need to create and fill another dataset. Just return the adapter and work on the caller code

Of course you could also change the method and return a dataset filled with your data instead of the DataAdapter. Probably this solution could be considered more encapsulated 

Public Function GetStudentQuestionRepeat(ByVal topicId As String, ByVal groupId As String) _
                As Dataset
    ....
    Dim da As New OleDbDataAdapter("qryStudentNameRandomBasedOnScore", cn)
    da.SelectCommand.CommandType = CommandType.StoredProcedure
    da.SelectCommand.Parameters.AddWithValue("@GroupID", groupId)
    Dim ds = new Dataset()
    da.Fill(ds, "Student")
    Return ds
    ....        
End Function

By the way, what is the purpose of topidID variable passed and never used?

Upvotes: 2

Edper
Edper

Reputation: 9322

Why not return a Dataset instead of DataAdapter, like:

    Public Function GetMyoleDataAdapterStudentQuestionRepeat(ByVal topicId As String, ByVal groupId As String) As DataSet
        Try
            Dim strAccessConn As String = _appConfigDbConn
            Dim cn As OleDbConnection = New OleDbConnection(strAccessConn)
            Dim ds As New DataSet
            Dim da As New OleDbDataAdapter("qryStudentNameRandomBasedOnScore", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@GroupID", groupId)
            da.Fill(ds, "Student")
            Return ds
        Catch ex As Exception
            Throw New ApplicationException(ex.InnerException.Message.ToString())
        End Try
    End Function

And then when you call it would be shorter:

  Dim myDataSet As DataSet = GroupData.GetMyoleDataAdapterStudentQuestionRepeat(topicId, groupId)

  txtStudentName.DataBindings.Add("Text", myDataSet.Tables(0), "studentname")

Upvotes: 0

Related Questions