Question Help With Data Classes & Concepts

dukeofawesome

Member
Joined
Mar 22, 2006
Messages
23
Location
Australia
Programming Experience
Beginner
Hi,

After about 2 days of working on using a Data Class to return values from a database I'm finally getting somewhere. I can return data and read data without issue. What I'm having a major issue with is editing and writing data back to the database. My class looks like so:

VB.NET:
Imports System.Data.OleDb 

Public Class clsDatabase 
Dim cnn As New OleDbConnection 
Dim dbProvider As String 
Dim dbSource As String 

Dim da As OleDb.OleDbDataAdapter 
Dim ds As New DataSet 

Sub ConnectDB() 
dbProvider = My.Settings.DatabaseCon 
cnn.ConnectionString = dbProvider 
cnn.Open() 
End Sub 
Sub CloseDB() 
cnn.Close() 
End Sub 

Function sqlSelect(ByVal strSQL As String) 
da = New OleDbDataAdapter(strSQL, cnn) 
da.Fill(ds, "Results") 
Return ds 

End Function 

End Class

I tried to add the following, but can't make it work:

VB.NET:
Sub AddNew() 
Dim cb As New OleDb.OleDbCommandBuilder(da) 

da.Update(ds, "Results") 
End Sub

I'm assuming there is a better way of doing this, but this is what I've managed to put together so far so hopefully someone can point me to a good tutorial becuase this sort of stuff is very hard to find on Google. Any guidance will be most appreciated.

Thanks
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
This line:
VB.NET:
da.Update(ds, "Results")
is how you save changes so you've got that bit right. Maybe you could explain what actually happens. I'm sure that you wouldn't expect a doctor to diagnose you without explaining the symptoms. There are only really three possibilities when that line is executed:

1. The call fails and an exception is thrown.
2. The call succeeds and returns zero.
3. The call succeeds and returns a non-zero value.

Which is it in your case?
 

dukeofawesome

Member
Joined
Mar 22, 2006
Messages
23
Location
Australia
Programming Experience
Beginner
I've managed to sort it out, thank you. For future reference if someone else need to get this working, here is my Class:

VB.NET:
Imports System.Data.OleDb

Public Class clsDatabase
    Dim cnn As New OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String

    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    Sub ConnectDB()
        dbProvider = My.Settings.DatabaseCon '"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\cadata.accdb;" 
        'MsgBox(My.Settings.DatabaseCon)
        cnn.ConnectionString = dbProvider
        cnn.Open()
    End Sub
    Sub CloseDB()
        cnn.Close()
    End Sub

    Function sqlSelect(ByVal strSQL As String)
        da = New OleDbDataAdapter(strSQL, cnn)
        da.Fill(ds, "Results")
        Return ds

    End Function
    Sub AddNew(ByVal strSQL As String)
        Dim cmd As New OleDbCommand(strSQL, cnn)

        cmd.ExecuteNonQuery()

    End Sub
    Sub Update(ByVal strSQL As String)
        Dim cmd As New OleDbCommand(strSQL, cnn)

        cmd.ExecuteNonQuery()

    End Sub
    Sub Delete(ByVal strSQL As String)
        Dim cmd As New OleDbCommand(strSQL, cnn)

        cmd.ExecuteNonQuery()

    End Sub

End Class
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Have you noticed how your AddNew, Update and Delete methods are exactly the same? There is nothing at all in any of them that is specific to the operation implied by the method name. That class needs a lot more thought. For one thing, there's no provision for using ADO.NET parameters so, besides any other problem that that may pose, it absolutely precludes you from saving binary data to the database.
 

dukeofawesome

Member
Joined
Mar 22, 2006
Messages
23
Location
Australia
Programming Experience
Beginner
The info regarding creating a Data Class that I have found over the past week is so cryptic, it's like it belongs to a secret club, and unless you're a member there is no way of finding out how to do it.

I know the AddNew, Update and Delete methods are exactly the same, but I just pass a SQL Statement to it and it works, so it does what I need for the moment.

If you have a link to a good guide on how to create one on these or alternatively, can make some suggestion on how best to modify mine to get the best out of it, I'm all ears.

I have been working on this stuff for about 4 days, and it has been hard work let me tell you...
 
Top Bottom