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? :confused:

 

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

 

i've been working on this all day and sad to say, i can't find any solution for this problem. i hope that you can help me with regards to my problem :confused:

[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) isn't returning a valid SqlDataReader. If you step through the code are there any errors happening in the Query function?

[Nate Bross]

Generally, I'd 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 it's 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 :o wahaha! :D 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.