• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

Function to Return DataSet

I want to write a function to return the results of a SQL query as a dataset. I'm calling it like this:

Dim dsResults As Data.DataSet = New Data.DataSet
dsResults = databaseFunctions.ExecuteQuery("SELECT * FROM tUsers")

But I keep getting this error on the second line:
System.NullReferenceException: Object reference not set to an instance of an object.


Function ExecuteQuery(ByVal txtQuery As String) As Data.DataSet
        OpenDB()
        cmd = New SqlClient.SqlCommand
        adItem = New SqlClient.SqlDataAdapter
        dsResults = New Data.DataSet
        cmd = New SqlClient.SqlCommand(txtQuery, cn)
        adItem.SelectCommand = cmd
        adItem.Fill(dsResults)
        ExecuteQuery = dsResults
        CloseDB()
End Function



0
CodeManiac
Asked:
CodeManiac
2 Solutions
 
TorrwinCommented:
Usually that error means that a variable has not been properly initialized.  It isn't always true, but may want to double check.

Here's what I use:

Public Function SelectCommand(ByVal sSQL As String) As DataSet
        Try
            Dim daDataAdapter As New SqlDataAdapter
            Dim dsDataSet As New DataSet
            daDataAdapter.SelectCommand = New SqlCommand(sSQL, mobjConn)
            daDataAdapter.Fill(dsDataSet)
            Return dsDataSet
        Catch err As Exception
            MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Function
0
 
rodmjayCommented:
is option explicit turned off?
0
 
CodeManiacAuthor Commented:
Thanks Torrwin!

Can you give an example of how you call your function?

Do you declare a dataSet variable like I tried above?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HavaganCommented:
Dim ds as DataSet = SelectCommand("SELECT * FROM authors")

Paul
0
 
TorrwinCommented:
Paul is right, that's how I essentialy do it:

Dim ds as New Dataset
Dim sSQL as String
sSQL = "SELECT * FROM AUTHORS"
ds = SelectCommand(sSQL)
0
 
CodeManiacAuthor Commented:

I'm getting an "Object reference not set to an instance of an object." error when I call the function like this...


Dim ds As Data.DataSet = New Data.DataSet
        ds = databaseFunctions.SelectCommand("SELECT * FROM tUsers")
        With ds.Tables(0)
            For i = 0 To .Rows.Count - 1
                Response.Write("test" & i)
            Next
        End With

Any ideas?

Post your answer at the following URL to get another 500 points...
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21393393.html

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now