Populate a Variable From Access Table

sirmilt · March 20th, 2006, 12:08 PM

I am trying to populate a value in an Access database to a variable, rather then a control and can't figure out how.

This code works fine for text boxes:
txtAddress.DataBindings.Add("Text", myDv, "Address")

The text box is filled with the content of the field Address. The DataView is myDv.

This code doesn't work:

dblFacor.DataBindings.Add("Text", myDv, "Factor")

The DataBindings is not available to me. "Factor" is the field name and dblFactor is a variable declared with a double datatype.

Any suggestions as to how to handle this?

Bob Bedell · March 20th, 2006, 02:40 PM

Its because DataBindings is a public property of a .NET Control class that returns a ControlBindingsCollection that contains the Binding objects for the control.

A Double structure is simply a base data type. It doesn't have a DataBindings property.

The only variable you could add a Binding object to would be a control object variable for a control that inherits from the Control class, like System.Windows.Forms.TextBox:

e.g.,

Dim myTextBox As New System.Windows.Forms.TextBox
myTextBox.DataBindings.Add("Text", myDv, "Factor")

HTH,

Bob

Bob Bedell · March 20th, 2006, 05:12 PM

If you wanted to assign a field value from an Access table to a base data type variable (like Doulbe) stored in a DataView, you might to something like the following in VB.NET (see third to last line of code for variable assignment). This is just a console app:

Code:

Sub Main()

        ' Open a connection to Access.
        Dim cnn As OleDbConnection
        Dim connectionString As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\JetBackendDB.mdb;" & _
            "User ID=Admin;" & _
            "Password="
        cnn = New OleDb.OleDbConnection(connectionString)
        cnn.Open()

        ' Create DataTable
        Dim ds As New DataSet
        With ds.Tables.Add("tblRecords")
            .Columns.Add("ID", GetType(Integer))
            .Columns.Add("DoubleField", GetType(Double))
        End With

        ' Load DataTable
        Dim cmd As New OleDbCommand
        cmd = New OleDbCommand("SELECT * FROM tblRecords", cnn)

        Dim rdr As OleDbDataReader = cmd.ExecuteReader()
        Dim row As DataRow
        Do While rdr.Read()
            row = ds.Tables("tblRecords").NewRow()
            row("ID") = rdr("ID")
            row("DoubleField") = rdr("DoubleField")
            ds.Tables("tblRecords").Rows.Add(row)
        Loop
        rdr.Close()

        ' Create DataView
        Dim vue As New DataView(ds.Tables("tblRecords"))

        ' Assign value in a DataView row to a Double variable.
        Dim vueRow As DataRowView = vue(0)
        Dim dblDoubleField As Double = vueRow("DoubleField")
        Console.WriteLine(dblDoubleField)
        Console.Read()

    End Sub

HTH,

Bob

Bob Bedell · March 20th, 2006, 06:02 PM

Here's are better (more concise) version of the same, just 'cause I got way to much time on my hands:

VB.NET

Code:

Imports System.Data.OleDb

Module App

    Sub Main()
        Dim strCnn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\JetBackendDB.mdb;" & _
            "User ID=Admin;Password="
        Dim strSQL As String = "SELECT * FROM tblRecords"

        Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, strCnn)
        Dim ds = New DataSet
        da.Fill(ds, "tblRecords")

        Dim dv = New DataView(ds.Tables(0))

        ' Assign value in a DataView row to a Double variable.
        Dim dvRow As DataRowView = dv(0)
        Dim dblDoubleField As Double = dvRow("DoubleField")
        Console.WriteLine(dblDoubleField)
        Console.Read()
    End Sub

End Module

C#

Code:

using System;
using System.Data;
using System.Data.OleDb;

class App {
    static void Main() {

        // Open a connection to Access.
        String strCnn = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
            @"Data Source=C:\JetBackendDB.mdb;" +
            "User ID=Admin;Password=";
        String strSQL = "SELECT * FROM tblRecords";

        OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strCnn);
        DataSet ds = new DataSet();
        da.Fill(ds, "tblRecords");

        DataView dv = new DataView(ds.Tables[0]);

        // Assign value in a DataView row to a Double variable.
        DataRowView dvRow = dv[0];
        Double dblDoubleField = (Double)dvRow["DoubleField"];
        Console.WriteLine(dblDoubleField);
        Console.Read();
    }
}

HTH,

Bob

Lucy Longears · March 20th, 2006, 11:02 PM

Bob, I am new to this forum and need some big-time assistance. I am really new to this Access VBA stuff. I am creating a database that needs an inventory form to login items and logout items to 10 different sites. My problem is that I can't figure out how to remove the items that have been logged out from the available list. if you have some direction to code I might use. I would be very relieved.
YT,Lucille Longears

Bob Bedell · March 21st, 2006, 01:34 AM

Hi Lucy,

Quote:

quote:
I am really new to this Access VBA stuff.

For starters, the code you posted:

Code:

txtAddress.DataBindings.Add("Text", myDv, "Address")

is either VB.NET of C# (it would be identical in both), not VBA. So I assume you're writing a .NET app thats using ADO.NET to connect to Access.

In general, though, its pretty tough to field huge questions about application design in a forum like this without knowing anything about your app: what your UI looks like, your table structure, all that stuff. If you could provide some more detailed info, break things down a little, provide as much code as possible, etc. it might be helpful.

ADO.NET and .NET Windows Forms Databinding are just about never discussed on this forum, however - pretty much VBA, VB, ADO, DAO, the old closed COM universe.

You might try posting to one of the P2P .NET forums or:

for VB.NET

http://www.vbcity.com/forums

for C#

http://www.csharpfriends.com/

both real active .NET communities, or try Code Project, probably the best .NET downlaod site around:

http://www.codeproject.com/

though I'd be happy to help you as far as I can if you could provide more info and code.

HTH,

Bob