Class Module VB.net Problem

[bornok15]

I've been working with a class module to make my connection codes abit shorter but when i removed my connection from the class and made it public it was not working anymore.

The way I use the class
lcon = New ADOcon
lds = New DataSet
Dim ltb As DataTable = New DataTable
lds = lcon.Opends("Select * from users where uname = '" & uname.ToString & "' and passw ='" & pass.ToString & "'")

can anyone help me solve this problem? here's my class module

Imports System.Data.OleDb
Imports System.Data

Public Class ADOcon

Private daobj As OleDbDataAdapter
Private drobj As OleDbDataReader
Private dsobj As DataSet
Private dtobj As DataTable

#Region "Constructors"
'Public Sub New()
' daobj = New OleDbDataAdapter
' dsobj = New DataSet
' dtobj = New DataTable
'End Sub

#End Region

#Region "Methods"
Public Function Opends(ByVal Query As String) As DataSet
Try
daobj = New OleDbDataAdapter
dsobj = New DataSet
MsgBox(cn.State)

daobj.SelectCommand = New OleDbCommand("Select * from master", cn)
daobj.Fill(dsobj)

MsgBox(dsobj.Tables.Count)
Return dsobj

Catch ex As Exception
Return dsobj
MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

Public Function Opendr(ByVal Query As String) As OleDbDataReader
Try

drobj = New OleDbCommand(Query, cn).ExecuteReader
'MsgBox(drobj.HasRows)
Return drobj

Catch ex As Exception
Return drobj
MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

Public Sub DatabaseCommand(ByVal CommandString As String)
Try
Dim cmdobj As New OleDbCommand

cmdobj.CommandText = CommandString
cmdobj.Connection = cn
cmdobj.Connection.Open()
cmdobj.ExecuteNonQuery()
'cn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
#End Region

End Class

And this is the connection:

Imports system.data
Imports System.Data.OleDb

Module Main
Public DBpath As String = "c:\ayn data\ayn.mdb"
Public cnstring As String = "Provider=microsoft.jet.oledb.4.0; data source='" & DBpath & "';jet oledb:database password=ayn"
Public cn As OleDbConnection

Sub main()
Dim frmlog As New frmlogin

cn = New OleDbConnection(cnstring)
cn.Open()

frmlog.ShowDialog()
End Sub
End Module

I hope you can help me solve this problem.

[SolTec]

I've been working with a class module to make my connection codes abit shorter but when i removed my connection from the class and made it public it was not working anymore.

The way I use the class
lcon = New ADOcon
lds = New DataSet
Dim ltb As DataTable = New DataTable
lds = lcon.Opends("Select * from users where uname = '" & uname.ToString & "' and passw ='" & pass.ToString & "'")

can anyone help me solve this problem? here's my class module

Imports System.Data.OleDb
Imports System.Data

Public Class ADOcon

Private daobj As OleDbDataAdapter
Private drobj As OleDbDataReader
Private dsobj As DataSet
Private dtobj As DataTable

#Region "Constructors"
'Public Sub New()
' daobj = New OleDbDataAdapter
' dsobj = New DataSet
' dtobj = New DataTable
'End Sub

#End Region

#Region "Methods"
Public Function Opends(ByVal Query As String) As DataSet
Try
daobj = New OleDbDataAdapter
dsobj = New DataSet
MsgBox(cn.State)

daobj.SelectCommand = New OleDbCommand("Select * from master", cn)
daobj.Fill(dsobj)

MsgBox(dsobj.Tables.Count)
Return dsobj

Catch ex As Exception
Return dsobj
MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

Public Function Opendr(ByVal Query As String) As OleDbDataReader
Try

drobj = New OleDbCommand(Query, cn).ExecuteReader
'MsgBox(drobj.HasRows)
Return drobj

Catch ex As Exception
Return drobj
MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

Public Sub DatabaseCommand(ByVal CommandString As String)
Try
Dim cmdobj As New OleDbCommand

cmdobj.CommandText = CommandString
cmdobj.Connection = cn
cmdobj.Connection.Open()
cmdobj.ExecuteNonQuery()
'cn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
#End Region

End Class

And this is the connection:

Imports system.data
Imports System.Data.OleDb

Module Main
Public DBpath As String = "c:\ayn data\ayn.mdb"
Public cnstring As String = "Provider=microsoft.jet.oledb.4.0; data source='" & DBpath & "';jet oledb:database password=ayn"
Public cn As OleDbConnection

Sub main()
Dim frmlog As New frmlogin

cn = New OleDbConnection(cnstring)
cn.Open()

frmlog.ShowDialog()
End Sub
End Module

I hope you can help me solve this problem.

I had a similar problem and tried to create a module for accessing my database, and found out that I could only do it locally on each form :(

If you find a solution, please let me know too :)

[Jugortha]

you have to use the DataAdapter.Update(DataSet) to commit changement in the database

[bornok15]

hmmmm.. im don't understand what you meant by that.. the error is like the class is not recognizing the public connection.. anyone who hows how do deal with this? thanks..

[JRSofty]

Hi,

When looking at your code I saw this function in your class

Public Function Opends(ByVal Query As String) As DataSet
Try
daobj = New OleDbDataAdapter
dsobj = New DataSet
MsgBox(cn.State)

daobj.SelectCommand = New OleDbCommand("Select * from master", cn)
daobj.Fill(dsobj)

MsgBox(dsobj.Tables.Count)
Return dsobj

Catch ex As Exception
Return dsobj
MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

When you created the new OleDbCommand object you specified a connection parameter cn. However, no where in your class do I see a OldDbConnection object declared and initialized. The only place where you have the cn created is in your sub Main routine which is local to the application but not necessarily to your class. If you want your class to use cn you will need to make a small change to the function OR you need to put the connection inside the class itself. If you want to create the connection in the Form and then pass it to the class you need to make this change to your function:

Public Function Opends(ByVal Query As String, ByVal cn as OldDbConnection) As DataSet
Try
daobj = New OleDbDataAdapter
dsobj = New DataSet
MsgBox(cn.State)

daobj.SelectCommand = New OleDbCommand("Select * from master", cn)
daobj.Fill(dsobj)

MsgBox(dsobj.Tables.Count)
Return dsobj

Catch ex As Exception
Return dsobj
MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

Usually I stay away from one single connection for all my database operations. In my data class I perform a new connection for each instance of the class. Whenever I need to access the database I create a new instance of the class inside the running routine/function and then close it and dispose of it at after the data has been gathered.

[bornok15]

Thanks! ill try it.. Which one is more faster and doesn't consume more memory? having only one active connection or having to open a connection everytime you open a class? hope you could reply to this.. tanx..

[JRSofty]

Actually I've never really tried checking which is faster. The reason I do it this way is because I like to use multiple threads and sometimes if you pass multiple queries over the same connection from different threads it can be a bit of pain and I've had some lock up problems.

Memory isn't really the problem as long as you remember to dispose and set to nothing everything that you are done using. In my data class I pass datasets which I dispose of once I'm done processing them. The connection happens as soon as the New class is called and just before I set the class instance to Nothing I have a method called Close that disposes and closes all the objects and sets them to nothing as well. This way I'm controlling my memory usage.

As for speed it seems ok for me. The biggest problem is the computer I'm working on currently is just a bit slow for the type of database I'm using (SQL Server 2005 Express on a Windows 2K machine without a lot of RAM). However, I'm sure on a modern computer the speed problems I'm having would not show.

[bornok15]

I see.. That's the reason why sometimes one of my programs in VB6 locks my access database or worst corrupts it. I think ill stick with the class connection instead of having only one.. i hope you could give some small sample programs on how you handle databases in vb.net.. pls email me at [e-mail].. Thank you..

[JRSofty]

I'll try to get something written up for you.

BTW Access Databases doesn't handle multithreading very well. We have an application at my company that used a Access Database and we always had trouble. Then we switched it to MSDE and now use MS SQL Server 2005 Express and it doesn't have the same problems.

[JRSofty]

So here's a bit of how I'm doing this so maybe it will help you:

One thing I'm using SQL Server 2005 Express. I don't know if this will work with Access.

Imports System.Data.SqlClient
Public Class clsData
    Private _con As SqlConnection
    Public Sub New()
        Dim constr As String = My.Settings.imgstoConnectionString
        _con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\imgsto.mdf";Initial Catalog=IMGSTO;Integrated Security=True;Connect Timeout=300;User Instance=False")
        _con.Open()
    End Sub

    'Use this function to return a dataset that you can iterate through SELECT queries
    Public Function returnQuery(ByVal qry As String) As DataSet
        Dim ret As New DataSet
        Dim da As New SqlDataAdapter()
        Try
            da.SelectCommand = New SqlCommand(qry, _con)
            da.Fill(ret)
            Return ret
        Catch ex As Exception
            Debug.WriteLine(ex.Message & " clsData.vb ")
            Return ret
        Finally
            da.SelectCommand.Dispose()
            da.Dispose()
            da = Nothing
        End Try
    End Function

    'Use this function for UPDATE and INSERT queries
    Public Function noReturnQuery(ByVal qry As String)
        Dim cmd As New SqlCommand(qry, _con)
        Try
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return ex
        Finally
            cmd.Dispose()
            cmd = Nothing
        End Try
    End Function

    'Call this method before destroying the instance.
    Public Sub Close()
        If Not _con Is Nothing Then
            If _con.State <> ConnectionState.Closed Then
                _con.Close()
            End If
            _con.Dispose()
            _con = Nothing
        End If
    End Sub
End Class

I will use this inside a method or function in my main application like this:

Private Function storageStatusDisplayData() As Boolean
        Dim cd As New clsData 'initialize new instance which opens a connection
        Dim qry = "SELECT sf.directory_id as dir_id, sd.directory_name as dir_name, COUNT(sf.directory_id) as fileCnt FROM storage_files as sf JOIN storage_directories as sd ON sf.directory_id = sd.directory_id GROUP BY sf.directory_id, sd.directory_name HAVING (COUNT(sf.directory_id) > 0) ORDER BY sf.directory_id;"
        Try
            Dim ds As DataSet = cd.returnQuery(qry) ' call the returnQuery function and return a dataset
            Dim dr As DataTableReader = ds.CreateDataReader ' convert the data set to a datatablereader
            If dr.HasRows Then ' the datatablereader has data
                While dr.Read ' you need to use the read method to advance through the data
                    'DO STUFF WITH DATA
                End While
            Else
                'DO STUFF IF THERE IS NO DATA
            End If
            ' Clean up your mess after you are done
            dr.Close()
            dr = Nothing
        
            ds.Dispose()
            ds = Nothing

            cd.Close()
            cd = Nothing
            Return True
        Catch ex As Exception
            Debug.WriteLine(ex.Message & " basMain.vb line " )
            Return False
        End Try

    End Function

Hope that helps. I'm sure it isn't perfect and I'm happy to hear from anyone who has improvements to this.

[bornok15]

Thanks for all your help.. I think ill be switching all of my vb6 to mysql.. With vb.net I think i'll be using the connection object within the class.. Last question..:)

With this function in my class:

Public Function Opends(ByVal Query As String, ByVal refv As String) As DataSet
Try
daobj = New OleDbDataAdapter
dsobj = New DataSet

daobj.SelectCommand = New OleDbCommand("Select * from master", cnobj)
daobj.Fill(dsobj, refv)

Return dsobj
cnobj.close
cnobj.dispose

Catch ex As Exception
Return dsobj
cnobj.close
cnobj.dispose

MessageBox.Show(ex.Message, "Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

Is it ok if i close the connection here? what will be the effect if i will? Because during the 1st week of study i've read that datasets are disconnected from the database..

[bornok15]

Ohh.. you already posted the answer to my question..:) That means i shouldn't close it yet till im done with the class. Can you teach me how to view rows of data? im using a binding manager now like this:

bm = Me.BindingContext(dt)
bm.Position = 0

loaddata()

Friend Sub loaddata()

With dt.Rows(bm.Position)
txteno.Text = .Item(0)
txtelname.Text = .Item(1)
txtefname.Text = .Item(2)
End With

End Sub

btw im just using a friends sub routine but dont know whats the difference of just having it a "Private" sub..:)

[JRSofty]

That is also in my example. The DataTableReader object will allow me to go through the rows one at a time.

dim ds as DataSet = cd.returnQuery("SELECT * FROM tbl WHERE id = 10")
dim dr as DataTableReader = ds.CreateTableReader()
If dr.HasRows Then
     While dr.Read()
          'Do something with the row data 
          ' dr("columnName").toString is the way to access the actual ordinals (there are other way but I prefer this one).
     End While
End If

You can also check out information on the DataTableReader object from MSDN online http://msdn2.microsoft.com/en-us/library/system.data.datatablereader.aspx

[bornok15]

Thanks again..How about having buttons to toggle the row position or maybe having the data grid index to display the fields in textboxes? is binding using currency manager and binding manger the only way or faster way of doing it?
And can you give me the equivalent of this vb6 code?:)

with rsrecs
txt1.text = !field1
txt2.text = !field2
end with

Im sorry if i've been asking a lot of questions on this..Im like addicted to vb.net now..:)

[bornok15]

I tried using a datareader to show 1 field record in a message box.. and i only got 2 records saved how come it shows 3? the third one is taken from my autonumber field.. how can i read only the 2 records? is there like a condition for it?

con = New ADOcon(cnstring)
dt = New DataTable

ds = con.Opends("Select * from master", "master")
dt = ds.Tables("master")

Dim dr As DataTableReader = ds.CreateDataReader

While dr.Read
MsgBox(dr(1))
End While

[JRSofty]

From what I've heard Bound Controls are Evil

http://www.vbrad.com/Articles/art_binding_evil.htm

Actually, I don't use bound controls. I usually want to display all the data retrieved from the database at the same time and then I can select the parts that I want to work with. So I have no practice in creating bound controls in either VB6 or VB.NET.

The best is what I've found at this site concerning data binding to controls for VB.NET

Sorry couldn't be more help with this one.

http://www.vbforums.com/showthread.php?t=490455&highlight=Bound+Controls+Evil

[JRSofty]

I tried using a datareader to show 1 field record in a message box.. and i only got 2 records saved how come it shows 3? the third one is taken from my autonumber field.. how can i read only the 2 records? is there like a condition for it?

con = New ADOcon(cnstring)
dt = New DataTable

ds = con.Opends("Select * from master", "master")
dt = ds.Tables("master")

Dim dr As DataTableReader = ds.CreateDataReader

While dr.Read
MsgBox(dr(1))
End While

Not quite sure about this one. You only have two records and it shows three? I've never seen this happen before.

[bornok15]

Im sorry i haven't looked carefully.. I include a message box for row count , i thought it was the last row of my auto number field..

[bornok15]

I think ill be suing this format since i guess this is not a total binding of controls.. right?

bm = Me.BindingContext(dt)
bm.Position = 0

loaddata()

Friend Sub loaddata()

With dt.Rows(bm.Position)
txteno.Text = .Item(0)
txtelname.Text = .Item(1)
txtefname.Text = .Item(2)
End With

[JRSofty]

That's how I would do it.

You could have a button that triggers the loaddata() routine so that the data is incremented each time or something like that.

[bornok15]

I think im finally getting to understand this.. Bindingcontext's(currency Manager and Biinding Manager Base) are way better than actually binding them.. Thank you so much... It's a good thing that there are people like you who help beginners like me.. Thanks! Probably my next problem will be how run my MSSQL within my VS2005.. i think its not functioning..

[JRSofty]

Yeah I know what you mean. I had a devil of a time getting mine to work.

Are you using MSSQL 2005 or MSSQL 2005 Express?

I know if you install MSSQL 2005 Express and you don't give the service the correct name (which must be computername\SQLEXPRESS where computername is the name of your computer) it will not work correctly with VS2005 Express (and quite possibly VS2005). I don't have the full versions of the software so I don't know how to get them to work together.

I would suggest making that a separate thread. Maybe someone can help you get it working.