在select statment中返回一行数据 [英] Return a row of data in select statment

问题描述

我正在开发一个学生指纹注册系统。我正在使用带有onetouch sdk的数字角色u.r.u4500。到目前为止，我已经能够将指纹保存到mysql中并完成验证。我现在所困的是如何在验证指纹时从数据库中检索studentID。验证完成后出现此错误



System.Data.dll中发生类型'System.IndexOutOfRangeException'的异常，但未在用户代码中处理其他信息：位置0没有行。此行发生错误



我尝试过：



我能够执行验证。

I'm developing a student fingerprint registration system in vb. I'm using the digital persona u.r.u4500 with the onetouch sdk. I have so far been able to save the fingerprint into mysql and done verification also. What l'm stuck now is how to retrieve the studentID from the database when the fingerprint is verified. This error comes up after verification is complete

An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code Additional information: There is no row at position 0. The error occurs on this line

What I have tried:

As l am able to perform verification.

Private Sub verifyControl_OnComplete(ByVal Control As Object, ByVal FeatureSet As DPFP.FeatureSet, ByRef EventHandlerStatus As DPFP.Gui.EventHandlerStatus) Handles verifyControl.OnComplete

        Try

        cn.Open()
        sqlCommand.CommandText = "Select * from student"
        sqlCommand.CommandType = CommandType.Text
        sqlCommand.Connection = cn
        Dim lrd As MySqlDataReader = sqlCommand.ExecuteReader()

        While lrd.Read()
            usr = lrd("print")

        End While
        lrd.Dispose()
        bytes = Nothing
        bytes = usr
        template = New DPFP.Template()
        template.DeSerialize(usr)
        'Perform match
        matcher.Verify(FeatureSet, template, matchResult)
        If matchResult.Verified Then
            EventHandlerStatus = Gui.EventHandlerStatus.Success

            MessageBox.Show("Verified!, Fingerprint exist in database")
I suspect the fault to be from this block

            sqlCommand.CommandText = " select StudentID from student  where 
            print like '%template%' "

            Dim publictable As New DataTable
            Try

                adapter.SelectCommand = sqlCommand

                adapter.Fill(publictable)
                txtstudentID.Text = publictable.Rows(0).Item(1)
            Finally


                adapter.Dispose()
                cn.Close()
            End Try

我传递的变量到一个mysql语句。现在变量驻留在代码而不是数据库中，因此查询它将不会返回我当前获得的结果。有没有办法使用select语句查询mysql来检索与数据库中的指纹模板相关联的studentID（BLOB）。

I am passing a variable to an mysql statement. Now the variable resides in the code and not the database so querying it will return no results which l am currently getting. Is there a way to use select statement to query mysql to retrieve the studentID associated with the fingerprint template in the database(BLOB).

推荐答案

我怀疑你需要一个存储过程以加载数据并返回它插入的行的ID。然后，您将 ExecuteNonQuery ，并在VB应用程序中将ID检索为整数。在Microsoft SQL Server中，这是Scope_Identity（）函数返回的值。我确信MySQL有一些相同的东西，但是，因为我比MySQL更熟悉Microsoft SQL Server，我不知道什么是等效函数。接下来，您将执行从学生中选择*，其中id = InsertResult ，其中 InsertResult 是VB变量的名称接收 ExecuteNonQuery 返回的结果。

I suspect that you need a stored procedure to load the data and return the ID of the row that it inserts. Then, you would ExecuteNonQuery, and retrieve the ID into an integer in your VB application. In Microsoft SQL Server, this is the value returned by the Scope_Identity() function. I feel certain that MySQL has something equivalent, although, since I am much more familiar with Microsoft SQL Server than MySQL, I don't know what is the equivalent function. Next, you would do Select * from student where id = InsertResult, where InsertResult is the name of the VB variable that receives the result returned byExecuteNonQuery.

这篇关于在select statment中返回一行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！