Click here to Skip to main content
11,928,078 members (45,428 online)
Rate this:
Please Sign up or sign in to vote.
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)
                Using reader = cmd.ExecuteReader()
                    If reader.HasRows Then
                        Dim count As Int64 = reader.GetInt32(0)
                        Return count
                    End If
                End Using
            End Using
        End Using
    End Function

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
Posted 26-Apr-13 23:20pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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...
Ali M Alramil 27-Apr-13 4:33am
but the problem is I have Zero record with the value "0 " in my database
and it's not only the National Id I gave return 0 and mony other national Id's return the value I want
the national Id and the Applicant Id both are not repeted

OriginalGriff 27-Apr-13 4:35am
How did you check?
Ali M Alramil 27-Apr-13 4:38am
I'm creating a website so I have checked it throug the page I created
I enter the national Id and then Click submit
the I should recieve the applicant Id
OriginalGriff 27-Apr-13 4:42am
Not a good check - you need to use something like Sql Server Management Studio (or SSMS for short) to look at the raw data - the chances are that you are using very similar code to "prove" your data is right to that which you are testing!

Look at the raw table in SSMS - you will get a better idea of what is happening.
Ali M Alramil 27-Apr-13 4:45am
I looked for the table and the record I checkd and there is no problem with it
is that what you mean,,, to see the data and check the answers in the database??
OriginalGriff 27-Apr-13 4:51am
Yes - but not just that record. Look at the DB and find all records with the NationalID - is there more than one?
Ali M Alramil 27-Apr-13 4:55am
yes i checkd and there is only and only one because the national_ID constrain is to be not repetrd!!! and for each applicant Id should be only one national Id

this may help
in my website I have a form to fill (Application) after filling it
I should send Email with the new Applicant Id
it send Zero and it's the same method and it's new national Id!!1
OriginalGriff 27-Apr-13 5:11am
Whoa! back up a little:
"only and only one because the national_ID constrain is to be not repetrd"
"for each applicant Id should be only one national Id"
Does not mean that there is only one Applicant with a National:
Id Name
1 England
2 India
3 Australia

Id Name National
1 Fred 1
2 Joe 1
3 Kumar 2
4 Steve 3
5 Asok 2

Since you retrieve records based on National ID a SELECT based on "2" would give you two records: "Kumar" and "Asok"
Ali M Alramil 27-Apr-13 4:48am
and by looking at the table I know what should I recive
Ali M Alramil 27-Apr-13 5:19am
no you did not get what I mean

I have only one table called Applicant:
Applicant Id
National ID

the national ID is the government Id (national not nationality)


Applicant Id: 1
name : ali
National Id: 1000001

Applicant Id: 2
name : jessus
National Id: 100002

in my website the user should enter for example
100002 and he will recive 2 because it's his Applicant Id

OriginalGriff 27-Apr-13 5:25am
Then you need to look at the value you are passing through as National ID, because the only other way to return zero is if no records are found at all.

Have you tried adding an "Else" condition to the HasRows and indicating a problem there?
Ali M Alramil 27-Apr-13 5:26am
NO WHAT SHOULD i write in the else?
OriginalGriff 27-Apr-13 6:12am
Given it's a shared function you can't display a message directly to your user, so I would log the problem in a file (DB, Text, whatever - it's for debugging not release so it doesn't matter overmuch) and return a negative number or throw an exception. You can then use that in the calling code to tell the user what the problem is.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web03 | 2.8.151126.1 | Last Updated 27 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100