Question Trying to make an MsAccess connection function

[manny_gt]

Hello,
Just registered here so hi to all!

I want to (re)create a connection function for MsAccess.
I'm a ASP programmer too and this is my old function:

function Connettore_NG(mdb,SQL)

    dim NG_Connessione, stringa_connessione, NG_rs, file_fisico
    file_fisico = server.MapPath(cartella_mdb & mdb & ".mdb")
    Set NG_Connessione = Server.CreateObject("ADODB.Connection") 
    stringa_connessione ="driver={Microsoft Access Driver (*.mdb)}; DBQ=" & file_fisico
    NG_Connessione.Open stringa_connessione
    Set NG_rs = Server.CreateObject("ADODB.Recordset")
    NG_rs.Open SQL, NG_Connessione ,3,3
    set Connettore_NG = NG_RS
    set NG_Connessione = Nothing

end function

With that function, in ASP, I can use correctly the addnew(), delete() and update() correctly.
Here an example:

set rs = connettore_NG("fileaccess","select * from table1")

rs.addnew
rs("field1") = "hi folks"
rs("field2") = "regards"
rs.update

I've tried to modify my function for vb.net

Public Function Connettore_NG(mdb, SQL)

        Dim NG_Connessione, stringa_connessione, NG_rs

        NG_Connessione = CreateObject("ADODB.Connection")

        stringa_connessione = "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & mdb
        NG_Connessione.Open(stringa_connessione)
        NG_rs = CreateObject("ADODB.Recordset")
        NG_rs.Open(SQL, NG_Connessione, 3, 3)
        Connettore_NG = NG_rs
        NG_Connessione = Nothing
End Function

With that function for vb.net I can write records correctly, but if I want to reads records, I got an error for ArgumentException.

Please can you help me?

Thank you very much.

 

[jmcilhinney]

Bad, bad, bad. You're using VB.NET now so you should be using ADO.NET. If you want to execute query in a function and return the result set it should look like this:

Private Function GetDataTable(dataFilePath As String, sqlQuery As String) As DataTable
    Dim builder As New OleDbConnectionStringBuilder("default connection string here")

    builder.DataSource = dataFilePath

    Using connection As New OleDbConnection(builder.ConnectionString),
          command As New OleDbCommand(sqlQuery, connection)
        connection.Open()

        Using reader = command.ExecuteReader()
            Dim table As New DataTable

            table.Load(reader)

            Return table
        End Using
    End Using
End Function

There are still numerous improvements you could make to that but anything it lacks is also lacking in your original.

 

[manny_gt]

Thanks for you reply.
So with that function, can I use the old addnew(), delete() and update()?

Thanks again!

 

[jmcilhinney]

You can't use any members of a Recordset because you don't have a Recordset. There are analogous members. To create a new row you call the NewRow method of the DataTable. To add that row to the table you call the Rows.Add method. To edit a row you simply set the appropriate field by name or ordinal. To delete a row you call its Delete method.

The most important difference is that ADO.NET works in disconnected mode. A Recordset has a live connection to the database so any changes you make in the Recordset are immediately reflected in the database. With ADO.NET, you open a connection, retrieve a copy of the data and then close the connection. Any changes you make to that local copy affect it only. To save the changes to the database you use a data adapter to save them in a batch. It's possible to make changes one by one but that, if you're retrieving multiple records in the first place, that is generally not the preferred approach.

You might like to check out my own ADO.NET code examples, which cover the most common scenarios.

Retrieving and Saving Data in Databases

You can then look for more specific information elsewhere as you require it. I'd suggest at least reading the documentation for each of the types used in those examples of mine.

 

[manny_gt]

Well... I don't want to use ADO.NET for now (and if it possible).
Can you explain me why the addnew, rs("field") = "hello", update works and retrieving var = rs("field") doesn't?

Thank you.

 

[manny_gt]

Hello? Any news for why retrieving fields doesn't works?