我的SQL检索功能有逻辑错误 [英] I have a logical error in my SQL retrieving function

问题描述

我创建了一个基于会话检索值的函数。



该函数将获取会话值并返回我想要的值



问题是：它适用于某些行，但它不适用于其他人



我所包含的表格包含超过2000行



这是我的功能。

I created a function to retrieve a value based on the session.

The function will take the session value and return the value I want

The problem is : it works with some rows, but it''s not with others

The table I have contains more than 2000 rows

This is my function.

Public Shared Function GetUserNational(ByVal NationalID As Integer) As Int64
        Const sql = "SELECT Applicant_ID FROM DIP_Applicant where NationalID = @NationalID"
        Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("mydbConnectionString").ConnectionString)
            Using cmd = New SqlCommand(sql, con)
                cmd.Parameters.AddWithValue("@NationalID", NationalID)
                con.Open()
                Using reader = cmd.ExecuteReader()
                    If reader.HasRows Then
                        reader.Read()
                        Dim count As Int64 = reader.GetInt32(0)
                        Return count
                    End If
                End Using
            End Using
        End Using
    End Function

我的表包含Applicant_Id类型int和主键还包含National_Id as nvarchar（50）



在我的网站中用户应输入他或她的国家ID和我将返回申请人ID



同样的调用同样的问题



例如我已经是national_Id 102323232当我在我的文本框中输入它时，它向我显示申请人身份



但是当我给他们一个像100555666的身份时;它返回0但值不应该为零 -



它已经在表中并且它不是0

My table contain Applicant_Id type int and primary key contain also National_Id as nvarchar(50)

in my website the user should enter his or her national Id and i will return the applicant Id

the same call the same queriy

for example I have already national_Id "102323232" when I enter it in my text box it shows me the applicant Id

but when I give anouther Id like "100555666"; it return 0 but the value should not be zero-

it''s already in the table and it''s not 0

推荐答案

你说它在表中，但你的代码没有显示 - 表中第一个与NationalID为100555666匹配的Applicant_ID为零，或者没有这样的记录。



首先要做的是使用调试器逐步执行该函数，并确保reader.HasRows返回true - 至少有一行匹配。如果这是真的，那么请查看阅读器中的值并查看其内容。



如果它是零，那么你需要检查你的数据库并查看与NationalID匹配的所有值 - 我只能假设有多个申请人来自一个国家......

You say it''s in the table, but your code doesn''t show that - either the first Applicant_ID in the table that matches a NationalID of 100555666 is zero, or there is no such record.

The first thing to do is use the debugger to step through the function and make sure that the reader.HasRows returns true - at least one row matches. If that is true, then look at the value in the reader and see what it says.

If it is zero, then you need to check your database and look at all the values that match the NationalID - I can only assume there is more than one Applicant from a single country...

这篇关于我的SQL检索功能有逻辑错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！