[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 682
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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