.net + MSAccess

Can anybody provide me a simple code to connect .net and MSAccess? also provide me the details of where to find ADODB control.

Hi!

A. If you are using .NET, you can set a reference to ADODB, but you will be using outdated technology. In .NET, to access data, you want to use ADO.net. It takes some getting used to, but it is well worth the time to learn. (I came from the world of vb/vba/Access myself). You do THAT by double-clicking on the "project" folder in the project explorer window. Then you can use ADODB from within .NET, but it is really not the best way to go.

B. Here is a simple example of connecting to an access Db. THis is a very simple Form class, with a single combo box on it. The Sub "LoadComboBox" is called during the Form's Load Event(at the bottom). This uses some basic .NET coding, and basic ADO.NET objects.

C. For better (and MUCH more complete) information on connecting to Data, See the FAQ's, and check out the links on jmchilhinney's signature.

D. Good Luck!

Code:

---


'Use these imports statements  at the VERY TOP of the code module.
'This will save some typing later:
Imports System.Data.OleDb
Imports System.Data

Public Class Form1

    'A local DataTable scoped at the Form's Module level.
    'Data Loaded here will be available until the DataTable
    'Is refreshed, or the form goes out of scope (is closed).
    Private dtComboSource As DataTable

    'A constant to hold the ConnectionString for an example database.
    'I would normally store the connection string in my Project Settings file:
    Private Const cxn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DataBase\CCCHousing_ACCESS\AACCCHousingDb_v1_be.mdb"


    'A Method used to load a ComboBox an A Form:
    Public Sub LoadComboBox()

        'A string variable set up with a simple SELECT statement:
        Dim strSELECT As String = "SELECT PropertyID, Property FROM tblProperty ORDER BY Property"

        'In .NET, the Using Keyword initializes a variable which goes out of
        'scope when the Using Block is finished executing, and the object disposes itself.
        'It is good practice to use a "Using" block with Database connections:

        'I defined my ConnectionString Parameter as a constant at the top of the Module.
        'Normally, I would place this in the Project settings file. Now I just reference
        'the name of the Constant as a parameter whien I initialize my connection object:
        Using cn As New OleDbConnection(cxn)

            'Now initialize an OleDb Command Object, and feed the String variable containing
            'the SELECT statement as the first parameter, and the connection object as the second:
            Using cmd As New OleDbCommand(strSELECT, cn)

                'The DataReader is one of the basic ways to READ
                'Data from your database. It is a Read-only, Forward-only
                'cursor, but it is fast:
                Dim dr As OleDbDataReader

                'Some basic .NET error Handling; the Try/Catch Block:
                Try
                    'Open the connection:
                    cn.Open()

                    'Use the ExecuteReader Method of the OleDBCommand object:
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                    'Initialize the DataTable Object declared at the top of
                    'the module. The dataTable will persist beyond the scope of this method:
                    dtComboSource = New DataTable

                    'Use the Load Method of the dataTable to load the
                    'dataTable with the contents of the DataReader:
                    dtComboSource.Load(dr)
                Catch ex As Exception
                    'Something went horribly wrong!
                    'Make sure the connection gets closed:
                    MsgBox(ex.Message)
                    If Not cn.State = ConnectionState.Closed Then
                        cn.Close()
                    End If
                End Try
            End Using
        End Using

        'Use the DataTable as the DataSource for a comboBox (Or something;
        'Your data is there now, and available for use:
        Me.ComboBox1.DataSource = dtComboSource
        ComboBox1.DisplayMember = "Property"
        ComboBox1.ValueMember = "PropertyID"

    End Sub


    'The LOAD Event Handler for the Form:
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.LoadComboBox()
    End Sub
End Class

---

Link: Database Development FAQs/Tutorials (at the top of the Database Development forum)

Quote:

---

Originally Posted by ttsdinesh

also provide me the details of where to find ADODB control.

---

There is no ADODB control at all, no matter what language you are working in.

There is the ADODC, but that is not a good idea even in older languages (the FAQs have an article explaining some of the reasons why).

For VB.Net, you should be following RunsWithScissors advice of using ADO.NET, which was designed for it.

Yeah, I assumed he was looking for some way to use COM ADO, and did not yet realize things had changed.

If you set a reference to the ADODB Interop Assebly in Project references, you CAN use ADODB, but things are still different (and you can't set the datasource of a ComboBox or any other bindable control to a ADODB.Recordset- you would have to use the .Recordset to populate a datatable or BindingList first).

By the time a guy went through the Machinations of trying to use ADODB within the .NET environment, I am betting they could have learned the basics of ADO.NET anyway, and would not be stuck then with the limitations of trying to use the old technology. ADO.NET SEEMS more complicated at first, because it is unfamiliar. But it is more powerful once you get used to it. I went through this same thing, but I surrendered.

Here is a (poorly put together, no exception-handling) example attempt to use ADODB. It works, but now my comboBox is not bound, and the only referenced I have to the Items listed in the COmboBox are either the string values loaded, or the indexes assigned in the order the items were added. No PK ID from my recordset.

If I had Manually added the records to the datatable used in the first example, everything would be hunky-dory, but why bother?

Code:

---


    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.GetADOComboBox()

    End Sub


    Public Sub GetADOComboBox()
        Dim rs As ADODB.Recordset = New ADODB.Recordset
        Dim cn As ADODB.Connection = New ADODB.Connection

        cn.ConnectionString = cxn
        cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

        cn.Open()
        With rs
            .Open("" & _
                "SELECT PropertyID, Property " & _
                "FROM tblProperty " & _
                "ORDER BY Property", cn, ADODB.CursorTypeEnum.adOpenStatic)
            If Not .EOF Then
                Do Until .EOF
                    ComboBox1.Items.Add(rs.Fields(1).Value)
                    .MoveNext()
                Loop
            End If

        End With
        cn.Close()

        cn = Nothing

    End Sub

---

Mr.Runwithscissors, thank u. i just now tried your tip and its working. i will be back if i need more clarifications.

U have mentioned the code to read the database. how to write data into database,then?

The short version is, to write to the Db you use an INSERT statement. What happens beyond that depends on what you are trying to do . . .

A. When you ask this question, is it because you don't know the SQL Statement needed to write to the Database? Or are your familiar with the basics (SELECT/INSERT/UPDATE/DELETE) and are having problems with the .NET Implementation? Or Both?

1. If you are unfamiliar with how to manipulate data with SQL, review the FAQ at the top of this forum, visit the Database development forum and do the same thing.

2. If you have at least the basics of SQL DML (Data Manipulation Language) down, then as I suggested in my previous post:

Quote:

---

C. For better (and MUCH more complete) information on connecting to Data, See the FAQ's, and check out the links on jmchilhinney's signature.

---

Mind you, I totally do not mind helping. But I need some idea of where you are starting. Using SQL to query your database is a fundamental without which you will find implementing ADODB OR ADO.NET difficult.


Here is the same code as previous, with a couple additions. Add a TextBox to the form ("TextBox1") and a Button ("Button1").

I have added a method called "AddProperty", and the eventHandler for the Button1_Click Event. This is crude, and has very little in validation, but you get the idea (Scroll down to find the added methods):

Code:

---


'Use these imports statements  at the VERY TOP of the code module.
'This will save some typing later:
Imports System.Data.OleDb
Imports System.Data

Public Class Form1

    'A local DataTable scoped at the Form's Module level.
    'Data Loaded here will be available until the DataTable
    'Is refreshed, or the form goes out of scope (is closed).
    Private dtComboSource As DataTable
    Private rsComboSource As ADODB.Recordset

    'A constant to hold the ConnectionString for an example database.
    'I would normally store the connection string in my Project Settings file:
    Private Const cxn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DataBase\CCCHousing_ACCESS\AACCCHousingDb_v1_be.mdb"


    'A Method used to load a ComboBox an A Form:
    Public Sub LoadComboBox()

        'A string variable set up with a simple SELECT statement:
        Dim strSELECT As String = "SELECT PropertyID, Property FROM tblProperty ORDER BY Property"

        'In .NET, the Using Keyword initializes a variable which goes out of
        'scope when the Using Block is finished executing, and the object disposes itself.
        'It is good practice to use a "Using" block with Database connections:

        'I defined my ConnectionString Parameter as a constant at the top of the Module.
        'Normally, I would place this in the Project settings file. Now I just reference
        'the name of the Constant as a parameter whien I initialize my connection object:
        Using cn As New OleDbConnection(cxn)

            'Now initialize an OleDb Command Object, and feed the String variable containing
            'the SELECT statement as the first parameter, and the connection object as the second:
            Using cmd As New OleDbCommand(strSELECT, cn)

                'The DataReader is one of the basic ways to READ
                'Data from your database. It is a Read-only, Forward-only
                'cursor, but it is fast:
                Dim dr As OleDbDataReader

                'Some basic .NET error Handling; the Try/Catch Block:
                Try
                    'Open the connection:
                    cn.Open()

                    'Use the ExecuteReader Method of the OleDBCommand object
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                    'Initialize the DataTable Object declared at the top of
                    'the module. The dataTable will persist beyond the scope of this method:
                    dtComboSource = New DataTable

                    'Use the Load Method of the dataTable to load the
                    'dataTable with the contents of the DataReader:
                    dtComboSource.Load(dr)
                Catch ex As Exception
                    'Something went horribly wrong!
                    'Make sure the connection gets closed:
                    MsgBox(ex.Message)
                    If Not cn.State = ConnectionState.Closed Then
                        cn.Close()
                    End If
                End Try
            End Using
        End Using

        'Use the DataTable as the DataSource for a comboBox (Or something;
        'Your data is there now, and available for use:
        Me.ComboBox1.DataSource = dtComboSource
        ComboBox1.DisplayMember = "Property"
        ComboBox1.ValueMember = "PropertyID"

    End Sub


    'The LOAD Event Handler for the Form:
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.LoadComboBox()

    End Sub


'The AddProperty Method:
    Public Sub AddProperty()

        Dim strPropertyName As String = Me.TextBox1.Text

        'A VERY Basic SQL INSERT Statement, which assumes that there will be a Field Named "Property"
        'in tblProperty. There would actually be more fields in most cases ("Address", "City", Etc . . .).
        'Note the "@Property" in the parens after VALUES; This is a PARAMETER. You don't HAVE to do it this
        'way, but it is a best practice for INSERTS and UPDATES. You COULD Concatenate the variable strPropertyName
        'In here instead, but this is not recommended (Look up "SQL Injection" to learn why" . . .):
        Dim strINSERT As String = "INSERT INTO tblProperty (Property) VALUES (@Property)"

        'Initialize an OleDb Connection:
        Using cn As New OleDb.OleDbConnection(cxn)

            'Initialize an OleDB Command:
            Using cmd As New OleDbCommand(strINSERT, cn)

                'The Command Object has a colleciton of PARAMETERS. In THIS case we
                'are only using one. However, most if the time there would be a PARAMETER
                'for each feild in the Db you intend to INSERT (or UPDATE, but that is a different matter . . .):

                'Add the Parameter "With value" as such:
                cmd.Parameters.AddWithValue("@Property", strPropertyName)

                Try
                    cn.Open()

                    'THIS time, we don't expect a return result set from MS Access, so we use
                    'the "Execute Non-Query" method of the OleDb Command Object:
                    cmd.ExecuteNonQuery()

                    MsgBox("A new record has been added to the Properties Table for " & strPropertyName)

                Catch ex As Exception
                    'Something went horribly wrong again!
                    MsgBox(ex.Message)
                    If Not cn.State = ConnectionState.Closed Then
                        cn.Close()
                    End If
                End Try
            End Using
        End Using

        'NOW: We have added an object to our back-end Db. We probably should refresh the ComboBox to show it:
        Me.LoadComboBox()

        'Pre-Select the newly added record:
        ComboBox1.SelectedText = strPropertyName

        'Then Clear the content of the TextBox for a new entry:
        Me.TextBox1.Clear()

    End Sub


'THe Button1_Click Event Handler:
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        If Len(TextBox1.Text) > 0 Then
            Me.AddProperty()
        Else

            'Some REALLY crude validation. You should make sure the user has entered some valid data before allowing
            'anything to be saved. I would normally do this by adding a fucntion to the Form's module
            'Called "CanSave" which tests for required data-entry conditions. Then, on each data-entry
            'Control's "Changed" event, Set the .Enabled Property of Button1 to the valule of the cansave function.

            'I didn't do that here, though:
            MsgBox("You must enter a Property Name Before you can save")
        End If

    End Sub

---