Solved

Get values from Access table without displaying them on form, calculate the values and display result on form.

Posted on 2004-04-23
11
684 Views
Last Modified: 2013-12-25
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim sales As Double
        Dim rate As Double
        Dim commission As Double

        sales = Me.OleDbDataAdapter1.SelectCommand.Parameters("sales").Value
        rate = Me.OleDbDataAdapter1.SelectCommand.Parameters("rate").Value

        commission = sales * rate

        Me.TextBox1.text = commission

    End Sub


The code is as above and it's not working. Basically it's very simple. I just want to get some numeric values from an Access table, calculate them, and display the result on a form.  Please note, when retrieving the values from table, I don't need to display them on form. So I don't need data binding, right?

Thanks many!
0
Comment
Question by:yanglin
  • 6
  • 4
11 Comments
 

Author Comment

by:yanglin
ID: 10903246
Suppose there is only one record in the table.
0
 
LVL 3

Expert Comment

by:diegojserrano
ID: 10905417
you need to use a recordset
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 10905738
You need several steps to do it:

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Create OleDBConnection Object and connect to Database
        Dim conn As OleDbConnection
        conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sales.mdb")
        ' Create OleDBCommand object
        Dim cmd As New OleDbCommand()
        cmd.CommandText = "SELECT sales , rate FROM Sales"
        cmd.Connection = conn
        conn.Open()
        'Create DataReader
        Dim Reader As OleDbDataReader
        Reader = cmd.ExecuteReader
        Reader.Read()
       
        TextBox1.Text = (Reader.GetValue(0) * Reader.GetValue(1)).ToString
        Reader.Close()
        conn.Close()

    End Sub

Another way is bring data from database directly:

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Create OleDBConnection Object and connect to Database
        Dim conn As OleDbConnection
        conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sales.mdb")
        ' Create OleDBCommand object
        Dim cmd As New OleDbCommand()
        cmd.CommandText = "SELECT (sales * rate)FROM Sales"
        cmd.Connection = conn
        conn.Open()
        'Create DataReader
        TextBox1.Text = cmd.ExecuteScalar().ToString

        conn.Close()

    End Sub

0
 

Author Comment

by:yanglin
ID: 10906147
Michael,

There is sth wrong with "Dim Reader As OleDbDataReader". There is a wave line under "OleDbDataReader". It seems OleDbDataReader class cannot be recognized. What's going on?

Thanks.

0
 
LVL 13

Expert Comment

by:Michael_D
ID: 10907430
You have to add this statement at very begining of your code module:

Imports System.Data.OleDb
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:yanglin
ID: 10908539
Public Class Form1
    Inherits System.Windows.Forms.Form



#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter

    Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbConnection1 As System.Data.OleDb.OleDbConnection
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents DataSet111 As WindowsApplication2.DataSet11
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter

        Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection
        Me.Label1 = New System.Windows.Forms.Label
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.DataSet111 = New WindowsApplication2.DataSet11
        CType(Me.DataSet111, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'OleDbDataAdapter1
        '
        Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
        Me.OleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "cots", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("cost", "cost"), New System.Data.Common.DataColumnMapping("price", "price")})})
        '
        'OleDbSelectCommand1
        '
        Me.OleDbSelectCommand1.CommandText = "SELECT cost, price FROM cots"
        Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
        '
        'OleDbConnection1
        '
        Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Data Source=""D:\VBProjects\research.mdb"";Jet OLEDB:Engine Type=5;P" & _
        "rovider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False" & _
        ";persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB" & _
        ":Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't C" & _
        "opy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User " & _
        "ID=Admin;Jet OLEDB:Global Bulk Transactions=1"
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(80, 64)
        Me.Label1.Name = "Label1"
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "Label1"
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(200, 64)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.ReadOnly = True
        Me.TextBox1.TabIndex = 1
        Me.TextBox1.Text = ""
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(152, 224)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 2
        Me.Button1.Text = "Button1"
        '
        'DataSet111
        '
        Me.DataSet111.DataSetName = "DataSet11"
        Me.DataSet111.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(448, 349)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.TextBox1)
        Me.Controls.Add(Me.Label1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataSet111, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

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


        OleDbConnection1.Open()

        'Create DataReader
        Dim Reader As OleDbDataReader
        Reader = Me.OleDbDataAdapter1.SelectCommand.ExecuteReader
        Reader.Read()

        TextBox1.Text = (Reader.GetValue(0) * Reader.GetValue(1)).ToString
        Reader.Close()

        OleDbConnection1.Close()

    End Sub


That's all my code. I added "Imports System.Data.OleDb" but there is a wave line under "Imports".

Thanks!


0
 
LVL 13

Accepted Solution

by:
Michael_D earned 20 total points
ID: 10908781
Here is the plase for import statement:

Imports System.Data.OleDb
Public Class Form1
....
0
 

Author Comment

by:yanglin
ID: 10909013
Michael,

It really works! Thanks many!
0
 

Author Comment

by:yanglin
ID: 10909724
Bye the wa, suppose there are more than one records in table. How to know the number of records? Is there a built-in method in OleDbDataReader to do this?
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 10910666
OleDBREader don't  have "build in" property to find out how many records in the table.
If you need to process all records use this construction

While objReader.read()

....
'Your Code going there
....

End while

objReader.Close()


Or you can use DataAdapter Control that allow you modify fields
0
 

Author Comment

by:yanglin
ID: 10911127
Michael, thank you!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to the message 13 63
VBA color chart bars 12 66
VBA filters 2 39
Modifying Conditional Format from VBA code 3 31
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now