accessing/mofigying a local database in VB.net? *tears out hair*

this is the one thing i never even looked into in VB6, i've done some database work in PHP using MySQL but that's it
anyway, im trying to stumble through; i've found out how to create a DSN-less connection to the database (i think) with the following code:

Dim objDs As DataSet
Dim ObjDa As OleDbDataAdapter
Dim sSQL As String
sSQL = "SELECT * FROM Accounts"
objDs = New DataSet()
ObjDa = New OleDbDataAdapter(sSQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Environment.CurrentDirectory & "\db\database.mdb")


im just trying to figure out how to retreive the result of the SQL statement,

thanks for any assistance,

bump

This ADO.NET is very confusing at first, but after you get it, its smooth sailing from then on.

Try this

'This fills the dataset with the info from the DB
ObjDa.Fill(ObjDs)

You will have to declare a datatable to get the tables from the database, and you also need to declare a datarow to get the row information.

This is some code I wrote in C#, it should give you an idea what you need to do.

Code:

---

                          CdDataAdapter.Fill(CdDataSet);
                        int index = CdDataSet.CD_Collection.Count;
                        CDCollection []CdColl = new CDCollection[index];

                        DataTable CdDataTable = CdDataSet.Tables[0];
                       
                        foreach(DataRow dr in CdDataTable.Rows)
                        {
                                for(int i = 0; i < index; i++)
                                {
                                        CdColl[i] = new CDCollection();

                                        CdColl[i].CdArtist = dr["RecordingArtistID"].ToString();
                                        CdColl[i].CdLabel = dr["RecordingLabel"].ToString();
                                        CdColl[i].CdNumberOfTracks = dr["NumberofTracks"].ToString();
                                        CdColl[i].CdTitle = dr["RecordingTitle"].ToString();
                                        CdColl[i].CdYear = dr["YearReleased"].ToString();
                                        CdColl[i].CdGenre = dr["MusicCategoryID"].ToString();
                                }

---

Here are two examples of retrieving the results of a sql select using the datareader and dataset approaches:

VB Code:

Private Function MSAccess_DataReaderTest()
    Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                "C:\Program Files\Microsoft Visual Studio\VB98\BIBLIO2002.mdb"
    Dim cnBiblio As New OleDbConnection(sConnString)
    Dim drTitles As OleDbDataReader
    Dim cmdTitles As New OleDbCommand("Select Top 10 * From Titles", cnBiblio)
    cnBiblio.Open()
    drTitles = cmdTitles.ExecuteReader()
    While drTitles.Read()
        Debug.WriteLine(drTitles.Item("title"))
    End While
    drTitles.Close()
    cnBiblio.Close()
End Function
 
Private Function MSAccess_DataSetTest()
    Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                    "C:\Program Files\Microsoft Visual Studio\VB98\BIBLIO2002.mdb"
    Dim cnBiblio As New OleDbConnection(sConnString)
    Dim dsTitles As New DataSet()
    Dim cmdSelect As New OleDbCommand("Select Top 10 * From Titles", cnBiblio)
    Dim da As New OleDbDataAdapter(cmdSelect)
    Dim drowTitle As DataRow
 
    cnBiblio.Open()
    da.Fill(dsTitles, "Titles")
    For Each drowTitle In dsTitles.Tables.Item("Titles").Rows
        Debug.WriteLine(drowTitle.Item("title"))
    Next
    cnBiblio.Close()
End Function