根据输入值从数据库检索数据 [英] retrieve data from the database based on the enter value

问题描述

我正在尝试根据文本框上的enter值从数据库中检索数据，并将结果显示在相应的文本框上.但是我在做这件事上遇到了麻烦.到目前为止，我所能做的就是只检索最早记录在数据库中的一个人的数据.它不会与数据库中的其余行进行比较.所以我真的很需要帮助..帮助我....在此先感谢很多...我的代码如下；

I am trying to retrieve data from the database based on the enter value on the textbox and display the result on the corresponding textboxes. But i am having trouble doing it. So far all i can do is retrieve data of only one person which was recorded first in the database. It doesn''t compare with the remaining rows in the database. So i really need help..help me out guys....Many many thanks in advance...My code as is follows;

Private Function Retrieve_EmpNo() As Integer

        Dim connection As New OleDbConnection(My.Settings.ConnectionString)
        Dim adapter As New OleDbDataAdapter("SELECT EmpNo FROM Emp_Details", connection)
        Dim dt As New DataTable
        Dim filter As DataView = Nothing
        Dim ds As New DataSet()

        adapter.Fill(ds, "Emp_Details")

        If TextBox1.Text = ds.Tables(0).Rows(0)(0) Then
            Return 1
        Else
            Return 0
        End If


    End Function


  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim i As Short

        i = Retrieve_EmpNo()

        If i = 1 Then
            Dim str As String

            str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|PWDdb.mdb"
            connection.ConnectionString = str
            command.CommandType = CommandType.Text
            connection.Open()
            command.Connection = connection
            adapter.SelectCommand = command
            adapter.TableMappings.Add("table", "Emp_Details")
            ds = New Data.DataSet("Emp_Details")
            adapter.Fill(ds)

            TextBox2.DataBindings.Add("Text", ds, "table.Name")
            TextBox3.DataBindings.Add("Text", ds, "table.Department")
            TextBox4.DataBindings.Add("Text", ds, "table.Designation")
            TextBox5.DataBindings.Add("Text", ds, "table.Basicpay")
        Else
            MessageBox.Show("Employee ID not found!!", "PWD", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If
 End Sub

推荐答案

damaxy写道:

到目前为止，我所能做的就是只检索数据库中最先记录的一个人的数据.它不会与数据库中的其余行进行比较.

So far all i can do is retrieve data of only one person which was recorded first in the database. It doesn''t compare with the remaining rows in the database.

那就是你在这里做什么

Thats what you are doing here

damaxy写道:

如果TextBox1.Text = ds.Tables(0).Rows(0)(0)然后
返回1
其他
返回0
如果结束

If TextBox1.Text = ds.Tables(0).Rows(0)(0) Then
Return 1
Else
Return 0
End If

您只检查第一条记录，如果在文本框中输入了第一个empno，则将其显示，否则，您将不显示任何记录.

如果您还想显示其他记录，则必须更改此"if"条件.

You are checking only for 1st record, if first empno is entered in TextBox then you are displaying it, otherwise you are not displaying any record.

You have to change this "if" condition if you want to display other records too.

我尚不完全清楚您认为自己在做什么.书面的.

TextBox1中有什么? (顺便说一句，您应该为控件真正设置自己的名称，这样就很清楚了……如txtEmpNo.这样，其他人就不必猜测了……将来也不会.)

其次，您似乎不了解数据库或如何从数据库中提取信息.我认为这就是您想要的:

一个人输入员工编号.然后，您进入数据库，看看它是否有效.如果是，那么您将获得有关该员工的信息.

因此，您实际上需要研究SQL及其可用内容.首先，如果要搜索特定的内容，则需要指定"WHERE"参数.其次，我将看一下SQLCommand.ExecuteScalar方法.这将仅返回查询的第一列和第一行.因此，如果您的查询说

I''m not entirely clear what you think you''re doing with what you''ve written.

What is in TextBox1? (By the way, you should really set your own names for the controls so it''s clear...like txtEmpNo. Then, others wouldn''t have to guess...nor would you in the future).

Secondly, it doesn''t look like you understand databases or how to extract information from them. I assume this is what you want:

A person types in an Employee Number. Then, you go into the database and see if it''s valid. If it is, then you get the information about that employee.

So, you need to actually look into SQL and what is available. First, you need to specify a "WHERE" paramater if you want to search for something specific. Secondly, I would look at the SQLCommand.ExecuteScalar method. This will return only the first column and the first row of the query. So, if your query said

SELECT dayNumber FROM JulianCalendar WHERE (Month=3 AND Day=2 AND Year=2007)

如果数据库或Nothing
中存在匹配项，它将返回"2454162"
您也不必在按钮单击上设置SELECT字符串来设置数据绑定...，实际上，我什至不使用数据绑定.我只是用select填充表，应该只给您一行，然后用列中的内容填充TextBox值.

您将要完成所有任务.

it would return "2454162" if there was a match in the database or Nothing

You also don''t set a SELECT string on your button click to set the DataBindings...and really, I wouldn''t even use DataBindings. I would just fill the table with the select, which should only give you one row and then just fill the TextBox values with what is in the columns.

You''re going about this task all wrong.

如果我是我，那么我将使用一种更加结构化的面向对象方法.您应该做的是创建一个具有EmployeeNumber，Name，Department，Designation，BasicPay等属性的Employee Class.然后，您可以在该类中使用一个共享方法，该方法根据EmployeeNumber返回一个Employee，如下所示:

If I were you I would use a more structured Object Oriented approach. What you should do is create an Employee Class with properties such as EmployeeNumber, Name, Department, Designation, BasicPay. Then you could have a shared method in that class that returns an Employee based on an EmployeeNumber like this:

Public Shared Function GetEmployee(ByVal EmployeeNumber As Integer, ByVal ConnectionString As String) As Employee

        Using conEmployees As New OleDbConnection(ConnectionString)
            Dim newEmployee As New Employee
            Dim cmdEmployee As New OleDbCommand("SELECT Name, Department, Designation, BasicPay FROM Emp_Details WHERE EmployeeNumber = @EmpNum", conEmployees)
            cmdEmployee.Parameters.AddWithValue("@EmpNum", EmployeeNumber)
            conEmployees.Open()
            Dim dbrEmployee As OleDbDataReader = cmdEmployee.ExecuteReader
            While dbrEmployee.Read
                With newEmployee
                    If Not IsDBNull(dbrEmployee(0)) Then .Name = dbrEmployee.GetString(0) Else .Name = String.Empty
                    If Not IsDBNull(dbrEmployee(1)) Then .Department = dbrEmployee.GetString(1) Else .Department = String.Empty
                    If Not IsDBNull(dbrEmployee(2)) Then .Designation = dbrEmployee.GetString(2) Else .Designation = String.Empty
                    If Not IsDBNull(dbrEmployee(3)) Then .BasicPay = dbrEmployee.GetDecimal(3) Else .BasicPay = 0D
                End With
            End While
            Return newEmployee
        End Using

    End Function

然后，在您的click事件处理程序中，您只需要执行以下操作:

Then in your click event handler you just need to do this:

Try
            Dim CurrentEmployee As Employee = Employee.GetEmployee(EmployeeNumber, My.Settings.ConnectionString, )
            txtName.Text = CurrentEmployee.Name
            txtDepartment.Text = CurrentEmployee.Department
            txtDesignation.Text = CurrentEmployee.Designation
            txtBasicPay.Text = CurrentEmployee.BasicPay
        Catch ex As OleDbException
            MsgBox("There was an error retieving Employee Data.")
        End Try

希望这会有所帮助

快乐编码

Hope this helps

Happy Coding

这篇关于根据输入值从数据库检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！