Problem with DataReader in object Class

[monkeynote]

hello guys!

i would like to share my problem with regards to converting my classic vb code to vb.net and one of this is my Query Function.

i have this code that passes SQLStatement and returns the recordset as datareader but im having an error in reader variable and it tells me that reader has no constructors! what does this error mean? what is a constructor? how can i prevent my code from having this error?

This is the code from frmUsers.vb file

Imports System.Data.SqlClient
Public Class frmUsers
    Private Sub frmUsers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim reader As New SqlDataReader
        Dim sqlStatement As String, err As String
        sqlStatement = "SELECT username, password, fullname, accesslevel FROM users"
        reader = obj.Query(sqlStatement, err)
        If reader.HasRows Then
            DataGrid.DataSource = reader
        End If
    End Sub
End Class

This is my Querystring Function in my SQL.vb class

Public Function Query(ByVal sqlStatement As String) As SqlDataReader
	Try
		Dim cm As New SqlCommand
        cm.Connection = cn                          Set a Connection
        cm.CommandText = sqlStatement               Execute SQL Statement
        cm.CommandType = CommandType.TableDirect    Refers that the command is SQL and not Stored Proc
        Query = cm.ExecuteReader(CommandBehavior.CloseConnection)
    Catch ex As Exception
		Query = Nothing
    End Try
End Function

ive been working on this all day and sad to say, i cant find any solution for this problem. i hope that you can help me with regards to my problem

 

[PlausiblyDamp]

You cannot create a DataReader directly - the framework itself will create one for you as part of the .ExecuteReader method. Try changing your original method to

  Private Sub frmUsers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim reader As SqlDataReader    No longer using the New keyword
        Dim sqlStatement As String, err As String
        sqlStatement = "SELECT username, password, fullname, accesslevel FROM users"
        reader = obj.Query(sqlStatement, err)
        If reader.HasRows Then
            DataGrid.DataSource = reader
        End If
    End Sub

 

[monkeynote]

thanks again for the reply

Dim reader As SqlDataReader No longer using the New keyword

i tried following your suggestion about removing "New" from sqldatareader but it gives me a new error

If reader.HasRows Then 	  Error: Use the "new" keyword to create an object instance
	DataGrid.DataSource = reader
End If

i also tried to pass the sqldatareader (as by reference in query parameter) but it also displays this message.

 

[PlausiblyDamp]

It looks as though obj.Query(sqlStatement, err) isnt returning a valid SqlDataReader. If you step through the code are there any errors happening in the Query function?

 

[Nate Bross]

Generally, Id put some type of logging (at least messagebox) in the catch section of your try...catch block.

Or, to keep it as is, change your call to look like this

reader = obj.Query(sqlStatement, err)
If reader is Nothing Then 
    MessageBox.Show("Exception caught in obj.Query")
Else
    If reader.HasRows Then
        DataGrid.DataSource = reader
    End If
End If

 

[monkeynote]

Thank you all guys for the reply

you are both right PlausiblyDamp and Nate Bross

i have an error in my Query Function
i followed Nate Bross code and testing whether if the reader returns values or not declared.

        Dim reader As SqlDataReader
        reader = obj.Query("SELECT * FROM users")
        If reader Is Nothing Then
            MessageBox.Show("Exception caught in obj.Query")
        Else
            If reader.HasRows Then
                Datagrid.DataSource = reader
            End If
        End If
        reader.Close()

i look at the error console and it gives me an error like this:

A first chance exception of type System.ArgumentOutOfRangeException occurred in System.Data.dll

i remove try...catch and i traced the error is in cm.CommandType = CommandType.TableDirect and Query = cm.ExecuteReader(CommandBehavior.CloseConnection) and i change it into this

Public Function Query(ByVal sqlStatement As String) As SqlDataReader
        Try
            Dim cm As New SqlCommand
            cm.Connection = cn                          Set a Connection
            cm.CommandText = sqlStatement               Execute SQL Statement
            cm.CommandType = CommandType.Text           Refers that the command is SQL and not Stored Proc
            Query = cm.ExecuteReader()
        Catch ex As Exception
            Query = Nothing
        End Try
    End Function

This is my button code. my problem is i cannot display my reader data in datagridview

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim reader As SqlDataReader
        reader = obj.Query("SELECT * FROM users")
        If reader Is Nothing Then
            MessageBox.Show("Exception caught in obj.Query")
        Else
            While reader.Read
                Debug.Print(reader("username")) this displays value in console
            End While
            If reader.HasRows Then
                Datagrid.DataSource = reader    data is not displayed in the datagrid            End If
        End If
        reader.Close()
    End Sub

why does Datagrid.DataSource = reader does not display any value?

Thank you so much for guiding me in my quest of learning .NET technology and i now appreciate the technology and flexibility that it gives to the developers. thank you so much guys!

 

[PlausiblyDamp]

The lines

While reader.Read
                Debug.Print(reader("username")) this displays value in console
            End While

are effectively using up the data reader. A DataReader can only be used once - after you have walked through its rows it has no more use. If you remove the debug loop it should be fine.

 

[monkeynote]

Problem Solved!

Thanks for the reply =)

Thanks for informing me that Datareader is forward-only query =)

I realize that datagrid needs dataset and dataset needs dataadapter. so i derived another function that will return the dataadapter for dataset to be used.

    Public Overloads Function Adapter(ByVal sqlStatement As String) As SqlDataAdapter
        Try
            Adapter = New SqlDataAdapter(sqlStatement, cn)
        Catch ex As Exception
            Adapter = Nothing
        End Try
    End Function

and ill be calling this like

    Dim da As SqlDataAdapter
    Dim ds As New DataSet
    
    Private Sub buttViewUsers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttViewUsers.Click
        da = obj.Adapter("Select * from users")
        da.Fill(ds, "users")
        Datagrid.DataSource = ds
        Datagrid.DataMember = "users"
    End Sub

I would like to thank you all for guiding me with creating SQL Class and i will continue my quest in .NET programming i will try to be resourceful so that i will not ask some stupid questions wahaha! thank you for the guidance guys! i hope that my post would help others in creating their own class... thanks guys!

 

[dan_appleyard]

Here is your original code of your DAL:
Public Function Query(ByVal sqlStatement As String) As SqlDataReader
Try
Dim cm As New SqlCommand
cm.Connection = cn Set a Connection
cm.CommandText = sqlStatement Execute SQL Statement
cm.CommandType = CommandType.TableDirect Refers that the command is SQL and not Stored Proc
Query = cm.ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
Query = Nothing
End Try
End Function

The issue is that you are not opening your connection. It breaks at the cm.ExecuteReader function and is caught by your catch ex as Exception. It then returns nothing. Open your connection right before the cm.ExecuteReader and all should be good.