Real Newbie to DB

[phreaky]

Im a real newbie to use DB, I mean, using them in coding, I know everything about its theories, but I need to know how to use them in VB, I already did the connection so I discovered how to make the realtionship between VB-ACCESS, but, what I need to know is how to work with the (accesing them, erase records, insert records, select records) without having to code every step of it, I have read lots of DB tutorials, and I know that using ADOs I dont have to do almost anything about those topics of inserting, deleting, etc., but, what I really need is an explanation of how to set ADOs, and how to use them correctly (combinating them with MSACCESS DBs), a good tutorial could help, I havent found the info that I really need, the tutorials I have found doesnt work at all for my needs, they explain for SQL server or they dont explain as well as I need (I havent worked with DB in VB), so, any help?

 

[a_jam_sandwich]

Using ADO the connection is the major bit about the type of database your connecting to the Record Set is the same for both

You create a Record Set by ...

[VB]

Create your connection
Dim MyConn as ADODB.Connection
Create your Recordset
Dim MyRs as ADODB.Recordset

MyConn = New ADODB.Connection
MyRs = New ADODB.recordset

Open the Connection
MyConn.Open(Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\" & DatabaseName & ";Persist Security Info=False)

Open The Recordset based on the SELECT Query
MyRs.Open("SELECT * FROM MyTABLE", MyConn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

While Not MyRs.EOF
Code to look at the returned recordset
e.g. console.writeline(MyRs.Fields("LASTNAME").Value)
Wend

Close all
MyRs.Close
MyConn.Close

[/VB]

Hope this helps

 

[Robby]

Shouldnt we move away from using COM?

here a simple function that Updates a record in Access,
You can alter the function to Insert Into or Delete records.

    Private Function SetTableValue(ByVal nID As Integer, ByVal sField As String, ByVal nValue As Integer, ByVal sTable As String) As Integer
        Dim SqlCMD As OleDbCommand
        Dim SqlCN As New OleDbConnection(Conn)
        Dim recordsAffected As Integer, strSql As String
        Try
            strSql = "UPDATE " & sTable & " SET " & sField & " = " & nValue & " WHERE ID = " & nID
            If SqlCN.State = ConnectionState.Closed Then SqlCN.Open()
            SqlCMD = New OleDbCommand(strSql, SqlCN)
            recordsAffected = SqlCMD.ExecuteNonQuery()
        Catch
            recordsAffected = -1
        Finally
            If SqlCN.State = ConnectionState.Open Then SqlCN.Close()
            If Not SqlCMD Is Nothing Then SqlCMD.Dispose()
        End Try
        Return recordsAffected
    End Function

 

[Robby]

by the way, you need to "Imports System.Data.OleDB"


Also, most of the samples you find may use SQL Server, you can usually just change SqlCommand to OleDbCommand or SqlConnection to OleDbConnection, etc...

 

[a_jam_sandwich]

Thax for the information as regards not to using COM object ive only start VB.NET 3 days ago still learing the fundimentals