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
700 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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