如何在vb中进行查询 [英] How do I make query in vb

问题描述

我的MS ACCESS数据库客户端，产品和销售中有三个表。表客户的字段是clientid，名称，地址，phoneno

产品是productid，name，unitprice
销售额为salesid，fkclientid，fkproductid，salesdate，quantity。 FK表示外键。



此表连接到visual basic。



现在我想要一张销售表格来记录销售交易。

a组合框在销售表单中显示产品列表及其unitprice

和计算字段小计，其中unitprice * quantity

解决方案

 Imports System.Data.OleDb 
 Public Class Form2 
 
 
 Public oCon As New System.Data.OleDb.OleDbConnection（provider = microsoft.jet.OLEDB.4.0; data source =& Application.StartupPath&\ Data\mydata.mdb）
 Public Trans As OleDbTransaction 
 Dim oCom As New OleDbCommand 
 
 
 Private Sub Form2_Load（ByVal sender As System.Object，ByVal e As System.EventArgs）Handles MyBase.Load 
 fillCombo（Me.cmb_products，productid，pro_name， products）
 End Sub 
 
 Private Sub cmb_products_SelectedIndexChanged（ByVal sender As System.Object，ByVal e As System.EventArgs）处理cmb_products.SelectedIndexChanged 
 Me.txt_unitprice.Text = GetValueByQuery （从产品中选择unitprice e productid ='& Get_ComboID（Me.cmb_products）& '）
 End Sub 
 
 Private Sub txt_quantity_KeyPress（ByVal sender As Object，ByVal e As System.Windows.Forms.KeyPressEventArgs）处理txt_quantity.KeyPress 
 Check_Digit（e） 
 End Sub 
 
 Private Sub txt_quantity_TextChanged（ByVal sender As Object，ByVal e As System.EventArgs）处理txt_quantity.TextChanged 
如果Me.txt_quantity.TextLength> 0然后
 Me.txt_totalprice.Text = Val（Me.txt_unitprice.Text）* Val（Me.txt_quantity.Text）
 Else 
 Me.txt_totalprice.Clear（）
 End如果
 End Sub 
 
 Public Sub fillCombo（ByVal cmb As ComboBox，ByVal tabid As String，ByVal tabVal As String，ByVal mytable As String）
 Dim str As String 
 Dim ocom As New OleDbCommand 
 Dim oRead As OleDbDataReader = Nothing 
 str =select& tabid& ，& tabVal& 来自& mytable& 
 ocom.CommandText = str 
如果oCon.State = ConnectionState.Closed那么
 oCon.Open（）
结束如果
 ocom.Connection = oCon 
尝试
 oRead = ocom.ExecuteReader 
 str =
 cmb.Items.Clear（）
而oRead.Read（）
 cmb.Items.Add （New ValueDescriptionPair（oRead（0），oRead（1）））
 End 
 Catch ex As Exception 
 MsgBox（ex.Message）
最后
 oRead。 Close（）
 oCon.Close（）
 End Try 
 End Sub 
 
函数GetValueByQuery（ByVal Query As String）As String 
 Dim temp As String 
 Dim ocom As New OleDbCommand 
 Dim oRead As OleDbDataReader 
 If oCon.State = ConnectionState.Closed Then 
 oCon.Open（）
 End if 
 
 ocom.Connection = oCon 
 ocom.Command Text = Query 
 
 oRead = ocom.ExecuteReader 
如果oRead.HasRows = True则
 oRead.Read（）
如果IsDBNull（oRead（0））= True然后
 temp =0
否则
 temp = oRead（0）
结束如果
 oRead.Close（）
 
否则
 temp =0
 oRead.Close（）
结束如果
返回temp 
结束函数
结束类

I have three tables in my MS ACCESS database clients,products and sales.the fields of the table clients are clientid,name,address,phoneno
products are productid, name, unitprice
sales are salesid, fkclientid,fkproductid,salesdate,quantity. FK means foreign key.

this tables are connected to visual basic.

now i want a sales form where i record the sales transactions.
a combobox in the sales form which shows the productlist and its unitprice
and a calculated field subtotal where unitprice*quantity

解决方案

Imports System.Data.OleDb
Public Class Form2


    Public oCon As New System.Data.OleDb.OleDbConnection("provider=microsoft.jet.OLEDB.4.0;data source=" & Application.StartupPath & "\Data\mydata.mdb")
    Public Trans As OleDbTransaction
    Dim oCom As New OleDbCommand


    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        fillCombo(Me.cmb_products, "productid", "pro_name", "products")
    End Sub

    Private Sub cmb_products_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_products.SelectedIndexChanged
        Me.txt_unitprice.Text = GetValueByQuery("select unitprice from products where productid='" & Get_ComboID(Me.cmb_products) & "'")
    End Sub

    Private Sub txt_quantity_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_quantity.KeyPress
        Check_Digit(e)
    End Sub

    Private Sub txt_quantity_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_quantity.TextChanged
        If Me.txt_quantity.TextLength > 0 Then
            Me.txt_totalprice.Text = Val(Me.txt_unitprice.Text) * Val(Me.txt_quantity.Text)
        Else
            Me.txt_totalprice.Clear()
        End If
    End Sub

    Public Sub fillCombo(ByVal cmb As ComboBox, ByVal tabid As String, ByVal tabVal As String, ByVal mytable As String)
        Dim str As String
        Dim ocom As New OleDbCommand
        Dim oRead As OleDbDataReader = Nothing
        str = "select " & tabid & "," & tabVal & " from " & mytable & ""
        ocom.CommandText = str
        If oCon.State = ConnectionState.Closed Then
            oCon.Open()
        End If
        ocom.Connection = oCon
        Try
            oRead = ocom.ExecuteReader
            str = ""
            cmb.Items.Clear()
            While oRead.Read()
                cmb.Items.Add(New ValueDescriptionPair(oRead(0), oRead(1)))
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            oRead.Close()
            oCon.Close()
        End Try
    End Sub

    Function GetValueByQuery(ByVal Query As String) As String
        Dim temp As String
        Dim ocom As New OleDbCommand
        Dim oRead As OleDbDataReader
        If oCon.State = ConnectionState.Closed Then
            oCon.Open()
        End If

        ocom.Connection = oCon
        ocom.CommandText = Query

        oRead = ocom.ExecuteReader
        If oRead.HasRows = True Then
            oRead.Read()
            If IsDBNull(oRead(0)) = True Then
                temp = "0"
            Else
                temp = oRead(0)
            End If
            oRead.Close()

        Else
            temp = "0"
            oRead.Close()
        End If
        Return temp
    End Function
End Class

这篇关于如何在vb中进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！