p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Populate a Variable From Access Table (http://p2p.wrox.com/showthread.php?t=40085)

sirmilt March 20th, 2006 12:08 PM

Populate a Variable From Access Table
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:


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



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:


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;" & _
        cnn = New OleDb.OleDbConnection(connectionString)

        ' 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")

        ' 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")

    End Sub



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:


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")
    End Sub

End Module


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"];



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,


I am really new to this Access VBA stuff.
For starters, the code you posted:


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


for C#


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


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



All times are GMT -4. The time now is 01:32 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.