DAO in dot net PLEASE.

[UKnod]

I have writen a few DB programms in ADO.net now, and I hate it. It is riddle with confusion and problems, and because of the ridiculous idea of using all the data locally, it makes networking neigh on impossible and incredibly slow, so please please someone show me how to use DAO on dot net. All I want to know is:
how to make a connection
how to add a record
how to delete a record
and how to edit a record.
Just like in the good ol VB6 days.

[Reverend Jim]

You are not going to like this answer but don't use DAO. ADO is not confusing or riddled with problems. ADO is actually pretty straightforward. The only confusing part can be getting the connection string right and we can help you with that as long as we know what type of database you are connecting to. For example, to connect to my local MS SQL database as a trusted user I would do

Dim con As New ADODB.Connection
con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=yes;")

To retrieve records I would do something like

rec.Open("SELECT au_lname,au_fname,zip FROM authors WHERE au_lname like 'S%'", con, CursorTypeEnum.adOpenStatic)

which retrieves all records for authors whose last name starts with S. To delete records do

con.Execute("DELETE FROM authors WHERE au_lname = 'ASIMOV'")

and to edit a record you can do an UPDATE as in

con.Execute("UPDATE authors SET au_fname = 'Dave' WHERE au_lname = 'Barry'")

then when you are done

con.Close()

and if you opened a recordset do (before closing con)

rec.Close()

Just because it was old doesn't mean it was good. Of course, just because it's new doesn't mean it is better.

[UKnod]

OK Thanks for this, but I am using Access MDB.

Your way looks way beter but I am a little confused with the syntax.
It seems that in fact I have been using OLEDB not ADO.
Here is a typical lookup that I would use:

        Public con As New OleDb.OleDbConnection
                dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source =" & app_path & "\AddressBook.mdb;"
        password = "Jet OLEDB:Database Password=12345678;"

        con.ConnectionString = dbProvider & dbSource & password

 con.Open()

        sql0 = "SELECT * FROM tblContacts order by contactID"
        da0 = New OleDb.OleDbDataAdapter(sql0, con)
        da0.Fill(ds0, "AddressBook")

 con.Close()

The trouble is, this is stored locally, the fill command take forever to run as it loads the complete table, and any changes dont go to the network until the programme closes or you invoke a comandbuilder, all of which takes ages, when most of the time I just want to change one record.

So your example looks like it does exactly what I am wanting but I am not sure how to convert it to something similar to above.

[Reverend Jim]

OK Thanks for this, but I am using Access MDB.

All you have to change is the connection string. For access you might use

Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\test\mydb.mdb;Uid=USERID;Pwd=PASSWORD;

If you don't require a userid or password just make them blank. Do you want to allow the user to input certain fields for the edit? In other words, will the user in effect build the query to fetch a record? If that is the case stick with OleDB so you can use parameterized queries (to prevent SQL injection attacks). If you can be more specific I could show you the code or you can have a look at a sample here

[UKnod]

Hi
Thanks for the info. I understand now how to read data with the required SQL string, this is great. However how do I now write data, and make new records using the same technique, update records etc.

[Reverend Jim]

Using ADO you can insert a new record by

Dim query As String = "INSERT INTO myTable (ID, LastName,FirstName) " _
                    & "  VALUES(123,'Lightyear','Buzz')"

con.Execute(query)

You should put these statements into a Try/Catch block so that you can detect failures. For example, if ID is the primary key and you try to insert a duplicate you will get an error. Also, if you have a type in the table name or one of the field names you will get an error.

For UPDATE and DELETE see my first response.

[UKnod]

Hi
Thanks for the info, I now understand how it works, but I am having trouble converting to mdb.

Here is what I have.
for setup

Imports System.Data.OleDb
Imports System.Data.SqlClient

But I think that the second one should not be SQL.
And I think I have to add the ADO reference

for connection

     Dim con As New ADODB.Connection
     con.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\addressbook.mdb;Uid=admin;Pwd=12345678;")

To open a sql string

rec.Open("SELECT au_lname FROM authors WHERE au_lname like 'S%'", con, CursorTypeEnum.adOpenStatic)

but what is rec ?

On VB6 is would be a recordset, but there isn;t one of those on .net

And even if it worked what do I do with the SQL string now I have it open?

So I could try:

 Dim cmd As New SqlCommand("", con)
 cmd.CommandText = "SELECT * FROM tblContacts WHERE ContactID =" 1234 "" ORDER BY ID"

and then to populate a variable I need a reader:

Say there was just one record in the above then

 con.Open()
 Dim rdr As SqlDataReader = cmd.ExecuteReader
 rdr.Read()
 x={rdr("au_lname")}


 rdr.Close()
 con.Close()

but again what is SWLCommand in MDB?

And to write back:

    con.open()
    con.Execute("UPDATE authors SET au_fname = 'Dave' WHERE ID = '1234'")
    con.close()

This would change one field au_fname to Dave in record ID 1234

Sorry to be thick, but as you see I am a little confused.

[Reverend Jim]

rec is a recordset. If you are using ADO then it doesn't matter if you are using VB6, VB.net, VBScript or whatever. But you should be consistent with what type of interface you are using. As far as I know, SQLClient and OLEDB are both built on top of ADO. SQLClient is for working with SQL databases only and is optimized for that. OLEDB can be used to connect to just about any data source. Here are examples for populating a ListView using all three interfaces. I can't provide you with a working example using an Access database because I do not have Access installed, however, the only difference would be in the connection string (and possibly the wildcard characters used in the queries).

Imports System.Text.RegularExpressions
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports ADODB

Public Class Form1

    Private Sub btnSQL_Click(sender As System.Object, e As System.EventArgs) Handles btnSQL.Click

        'SqlClient is optimized for SQL Server and supports named parameters

        ListView1.Items.Clear()

        Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=yes;")
        Dim cmd As New SqlCommand("", con)

        cmd.CommandText = "SELECT au_lname,au_fname,zip FROM authors WHERE au_lname like @pattern"
        cmd.Parameters.AddWithValue("@pattern", "%")

        con.Open()

        Dim rdr As SqlDataReader = cmd.ExecuteReader

        Do While rdr.Read()
            ListView1.Items.Add(New ListViewItem({rdr("au_lname"), rdr("au_fname"), rdr("zip")}))
        Loop

        rdr.Close()
        con.Close()

    End Sub

    Private Sub btnADO_Click(sender As System.Object, e As System.EventArgs) Handles btnADO.Click

        'ADO is the underlying layer for both SqlClient and OLEDB

        ListView1.Items.Clear()

        Dim con As New ADODB.Connection
        Dim rec As New ADODB.Recordset

        con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=yes;")
        rec.Open("SELECT au_lname,au_fname,zip FROM authors WHERE au_lname like 'S%'", con, CursorTypeEnum.adOpenStatic)

        Do Until rec.EOF
            ListView1.Items.Add(New ListViewItem({rec("au_lname").Value, rec("au_fname").Value, rec("zip").Value}))
            rec.MoveNext()
        Loop

        rec.Close()
        con.Close()

    End Sub

    Private Sub btnOleDB_Click(sender As System.Object, e As System.EventArgs) Handles btnOleDB.Click

        'OLEDB is more generic (can be used for different data sources) and supports
        'parameters but they are unnamed and positional

        ListView1.Items.Clear()

        Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=Yes;Connect Timeout=15;")
        Dim cmd As New OleDbCommand("", con)

        cmd.CommandText = "SELECT au_lname,au_fname,zip FROM authors WHERE au_lname like ?"
        cmd.Parameters.AddWithValue("@pan", "D%")

        con.Open()
        Dim rdr As OleDbDataReader = cmd.ExecuteReader()

        Do While rdr.Read
            ListView1.Items.Add(New ListViewItem({rdr.GetString(0), rdr.GetString(1), rdr.GetString(2)}))
        Loop

        rdr.Close()
        con.Close()

    End Sub

    Private Sub Button1_Click_1(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
        Dim cmd As New SqlCommand("", con)

        cmd.CommandText = "INSERT INTO sys_log (UserID,EventTime,Machine,EventType,Details) " _
                        & "VALUES(@UserID,GETDATE(),@Machine,@EventType,@Details)"

        cmd.Parameters.AddWithValue("@UserID   ", UserID)
        cmd.Parameters.AddWithValue("@Machine  ", Machine)
        cmd.Parameters.AddWithValue("@EventType", EventType)
        cmd.Parameters.AddWithValue("@Details  ", Details)

        con.Open()
        cmd.ExecuteNonQuery()

    End Sub

End Class

I've never used DataTables or DataAdapters, and I have never used controls that bind to a data source so I'm afraid I cannot help you with that but there are plenty of examples in this forum if you do a search. In case you don't already know this, in order to use ADO you must add a reference to adodb in your project properties. You'll find it on the .NETY tab. If you click on the Component Name column header twice then adodb will appear at the top.

[UKnod]

Ok, now this looks different than before.

Heres what I have got in my simplistic view.

    Dim con As New ADODB.Connection
    Dim rec As New ADODB.Recordset

    con.ConnectionString = ("Driver={Microsoft Access Driver (*.mdb)};Dbq=addressbook.mdb;Uid=admin;Pwd=12345678;")
    Dim x1(100), x2(100), x3(100)
    Dim contactID = 1

        'this is how to change a record
        con.Open()
        Dim sql
        sql = "UPDATE tblContacts SET Contact1 = 'Fred' WHERE ContactID = " & contactID
        con.Execute(sql)
        con.Close()

        'this is how to step through records and populate x1,x2,and x3
        con.Open()

        sql = "SELECT Contact1,Contact2,Contact3 FROM tblContacts WHERE Contact1 like 'S%'"
        rec.Open(sql, con, CursorTypeEnum.adOpenStatic)
        Dim n
        Do Until rec.EOF
            n = n + 1
            x1(n) = rec("Contact1").Value
            x2(n) = rec("Contact2").Value
            x3(n) = rec("Contact3").Value
            rec.MoveNext()
        Loop
        rec.Close()
        con.Close()

        'This is how to delete a record

        con.Open()
        Dim sql
        sql = "DELETE FROM tblContacts WHERE ContactID = '1234'"
        con.Execute(sql)
        con.Close()

So if this will work there is only the how to insert a record that I am missing.
This is just using ADO, What do you think.

[Reverend Jim]

It looks fine to me. I showed you the format of the INSERT in an earlier post. The thing to remember is that you should include both the field names and the field values. As long as you specify both then you don't have to worry about the actual order of the fields in the table. For example, if you have a table

ID         number
LastName   varchar(50)
FirstNamr  varchar(50)

The both of the following inserts will work

sql = "INSERT INTO myTable (ID,LastName,FirstName) VALUES(123,'Jetson','George')"
sql = "INSERT INTO myTable (ID,FirstName,LastName) VALUES(123,'George','Jetson')"

con.Execute(sql)

You may want to browse this SQL Tutorial

[UKnod]

Excelent, working now, thanks for your help.