Data access and vb class file from .net novise

[Terje Flaten]

Hi!

Im trying to make a common data access file (class1.vb) for use in my .net
projects. To test this I make a simple webform based on some examples I
found at MSDN and asp.net.

My modell is like this:
webform3.aspx imports a class1.vb and calls subs and functions in this
class.
The Class1.vb imports the System.Data.OleDB. Class1 is supose to -open
connection, -execute a command, -close the connection and send back the data
as a resultset to the webform3.aspx

When I build and browse it runs fine the first time, but If I try to refresh
the browser I getting the error:

Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.
The error is in this line of the class1 file: objConn.ConnectionString =
connection_string
Under here is the code for the 2 files:

I will thank you all for any help
Regards Terje

-------------------------

webform3.aspx
--------------------
Imports myapp.net.Class1
Public Class WebForm3
Inherits System.Web.UI.Page
Protected WithEvents MyDataGrid As System.Web.UI.WebControls.DataGrid

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim dbread
'Get data from class1
Connect_database("Provider=Microsoft.Jet.OLEDB.4.0;data
source=somebase.mdb")
dbread = getSimpleData("Select * from Table1 where id=3")
MyDataGrid.DataSource = dbread
MyDataGrid.DataBind()
Disconnect_database()
End Sub

End Class

----------------
My class1 file :

-----------------
Imports System.Data.OleDb

Public Class Class1

Public Shared objConn As New System.Data.OleDb.OleDbConnection()
Public Shared objCmd As New System.Data.OleDb.OleDbCommand()

Shared Sub Connect_database(ByVal connection_string As String)
'sett hvilken connectionstring som skal brukes av objConn objektet.
objConn.ConnectionString = connection_string
Try
objConn.Open()
' hvilken connection skal kommandoen sendes/ settes til
objCmd.Connection = objConn
Catch
End Try
End Sub

Shared Sub Disconnect_database()
objConn.Close()
objConn = Nothing
objCmd = Nothing
End Sub


Shared Function getSimpleData(ByVal strSQL)
Dim objRst
If Trim(strSQL) <> "" Then
' Call remove_parameters()
objCmd.CommandType = CommandType.Text
objCmd.CommandText = strSQL
objRst = objCmd.ExecuteReader()
getSimpleData = objRst
End If
End Function
End Class

 

[yonggangwang]

I think you need to ceate an instance of the calss1
after Imports myapp.net.Class1
publick Test as new Class1
try again

 

[Norman Yuan]

It is bad design in the class1 where you intend to simply return a
datareader while still declare SqlConnection/SqlCommand as public. In your
case, you should only expose getSimpleData() as Shared and hide all others.
You open a connection inside the getSimpleData() method, execute
ExecuteReader() with CommandBehavior argument, which closes the connection
when DataReader is closed. The code should look like:

Shared Function getSimpleData(ByVal strSQL, ByVal connectionString)

Dim cn As New SqlConnection(connectionString)
Dim cmd As SqlCommand()
cmd.Connection=cn
cmd.CommandType=....
cmd.CommandText=....

Dim reader As SqlDataReader

''Connection will be closed when read.Close() is called
reader=cmd.ExecuteReader(CommandBehavior.CloseConnection)

getSimpleData=reader

End Function

Then in your WebForm1 Page_Load(), you simply:

Dim reader As SqlDataReader
reader=Class1.getSimpleData(queryString, connectionString)
DataGrid1.DataSource=reader
DataGrid1.DataBind()
reader.Close() ''Connection is closed also.

As your original code, if you have to open connection, set connectionstring,
close(disconnect) connection in webform1, then what is the point to have
Class1?