• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

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

   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
yanglin
Asked:
yanglin
  • 6
  • 4
1 Solution
 
yanglinAuthor Commented:
Suppose there is only one record in the table.
0
 
diegojserranoCommented:
you need to use a recordset
0
 
Michael_DCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
yanglinAuthor Commented:
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
 
Michael_DCommented:
You have to add this statement at very begining of your code module:

Imports System.Data.OleDb
0
 
yanglinAuthor Commented:
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
 
Michael_DCommented:
Here is the plase for import statement:

Imports System.Data.OleDb
Public Class Form1
....
0
 
yanglinAuthor Commented:
Michael,

It really works! Thanks many!
0
 
yanglinAuthor Commented:
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
 
Michael_DCommented:
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
 
yanglinAuthor Commented:
Michael, thank you!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now