Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
706 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 80 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

636 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