Fail To Read Entire Table In .NET

The problem that I am having is that I am not getting the results from an entire table, just a small subset. The sql command is "Select * from login", which has always implied to me, get everything, to end of table, or, until I tell you to stop. I am getting exactly, 10 items as input, and then it stops. I am including the code below in the hopes that someone can spot what it is that I am doing wrong.

The purpose of the routine is to convert a user login database into a new, expanded format.

TIA, Tom


Imports Common.Utility.Utils

Partial Class UserDatabaseConversion

Inherits System.Web.UI.Page

Dim sqlConn As SqlConnection = getSQLConnection(ConfigurationManager.AppSettings("utilityDbName"))

Dim sqlConnN As SqlConnection = getSQLConnection(ConfigurationManager.AppSettings("utilityDbName"))

Dim sqlStringOld As String = "Select * from login"

Dim sqlStringNew As String = "usp_UT_AddNewUser"

Dim buildCnt As Integer = 0

Dim oldUserCnt As Integer = 0

Dim newUserCnt As Integer = 0

Dim oLocation As String = Nothing

Dim errors As Integer = 0

Dim mesg As String = Nothing

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

getOldUsers()

End Sub

Sub getOldUsers()

Dim odate As Date = Date.Today

Dim sqlCmdOld As SqlCommand = New SqlCommand()

sqlCmdOld.CommandText = sqlStringOld

sqlCmdOld.CommandType = CommandType.Text

sqlCmdOld.Connection = sqlConn

Dim dbReader As SqlDataReader = Nothing

Try

sqlConn.Open()

dbReader = sqlCmdOld.ExecuteReader()

If dbReader.HasRows Then

Do While dbReader.Read()

oldUserCnt += 1

Select Case IsDBNull(dbReader.Item("reg_date"))

Case True

buildNewUser(dbReader.Item("name"), dbReader.Item("password"), dbReader.Item("email"), dbReader.Item("type"), dbReader.Item("org"), dbReader.Item("occupation"), dbReader.Item("location"), odate)

Case False

buildNewUser(dbReader.Item("name"), dbReader.Item("password"), dbReader.Item("email"), dbReader.Item("type"), dbReader.Item("org"), dbReader.Item("occupation"), dbReader.Item("location"), dbReader.Item("reg_date"))

End Select

Loop

End If

Catch ex As Exception

Finally

sqlConn.Dispose()

End Try

If errors = 0 Then

lblMesg.Text = "Conversion completed succesfully.<br />Users In: " & oldUserCnt & " , Users Out:" & newUserCnt

Else

lblMesg.Text = "Conversion comleted with errors.<br />Users In: " & oldUserCnt & " , Users Out:" & newUserCnt & "<br />"

lblMesg.Text += mesg

End If

End Sub

Sub buildNewUser(ByVal oname As String, ByVal opassword As String, _

ByVal oemail As String, ByVal otype As String, _

ByVal oorg As String, ByVal oocc As String, _

ByVal oloc As String, ByVal oregdate As Date)

Dim sqlCmdNew As SqlCommand = New SqlCommand()

sqlCmdNew.CommandText = sqlStringNew

sqlCmdNew.CommandType = CommandType.StoredProcedure

sqlCmdNew.Connection = sqlConnN

Dim userName As String = Nothing

Dim firstName As String = Nothing

Dim middleName As String = Nothing

Dim lastName As String = Nothing

Dim title As String = Nothing

Dim emailAddress As String = Nothing

Dim passWord As String = Nothing

Dim userType As String = Nothing

Dim org As String = Nothing

Dim occ As String = Nothing

Dim loc As String = Nothing

Dim rdate As Date = Nothing

oLocation = oloc

' user name will be the first part of the email address

emailAddress = oemail

Dim uname() As String = oemail.Split("@")

' extend the length of the user name to at least 5 characters

Select Case uname(0).Length

Case Is > 4

userName = uname(0)

Case Is = 4

userName = uname(0) & "1"

Case Is = 3

userName = uname(0) & "12"

Case Is = 2

userName = uname(0) & "123"

Case Is = 1

userName = uname(0) & "1234"

End Select

' check for suffixes

Dim suffix() As String = oname.Split(",")

Select Case suffix.Length > 1

Case True

title = suffix(1)

End Select

' split names out

Dim names() As String = suffix(0).Split(" ")

' possibly 3 components to the name

Select Case names.Length

Case Is = 1

firstName = names(0)

Case Is = 2

firstName = names(0)

lastName = names(1)

Case Is = 3

firstName = names(0)

middleName = names(1)

lastName = names(2)

End Select

' setup password. must be at least 6 characters.

' if less, extend to 6.

Select Case opassword

Case Is > 5

passWord = opassword

Case Is = 5

passWord = opassword & "1"

Case Is = 4

passWord = opassword & "12"

Case Is = 3

passWord = opassword & "123"

Case Is = 2

passWord = opassword & "1234"

Case Is = 1

passWord = opassword & "12345"

End Select

' user type, organization and occupation

userType = otype

org = oorg

occ = oocc

' registration date

Select Case IsDBNull(oregdate) Or IsNothing(oregdate)

Case True

rdate = Today.Date

Case False

rdate = oregdate

End Select



' try to extract the location

' get the country

Dim country As String = getCountry(oloc)

' get the state

Dim state As String = getState(oloc)

' only thing left is the city

Dim city() As String = oloc.Split(",")

' add parameter values

sqlCmdNew.Parameters.AddWithValue("@NamePrefix", System.DBNull.Value)

sqlCmdNew.Parameters.AddWithValue("@FirstName", replaceSingleQuote(firstName))

Select Case IsNothing(middleName)

Case True

sqlCmdNew.Parameters.AddWithValue("@MiddleName", System.DBNull.Value)

Case False

sqlCmdNew.Parameters.AddWithValue("@MiddleName", replaceSingleQuote(middleName))

End Select

Select Case IsNothing(lastName)

Case True

sqlCmdNew.Parameters.AddWithValue("@LastName", System.DBNull.Value)

Case False

sqlCmdNew.Parameters.AddWithValue("@LastName", replaceSingleQuote(lastName))

End Select

sqlCmdNew.Parameters.AddWithValue("@UserName", replaceSingleQuote(userName))

Select Case IsNothing(title)

Case True

sqlCmdNew.Parameters.AddWithValue("@NameTitle", System.DBNull.Value)

Case False

sqlCmdNew.Parameters.AddWithValue("@NameTitle", title)

End Select

sqlCmdNew.Parameters.AddWithValue("@Password", replaceSingleQuote(passWord))

sqlCmdNew.Parameters.AddWithValue("@EmailAddress", emailAddress)

sqlCmdNew.Parameters.AddWithValue("@PasswordHint", userName)

Select Case isEmpty(org)

Case True

sqlCmdNew.Parameters.AddWithValue("@Organization", System.DBNull.Value)

Case False

sqlCmdNew.Parameters.AddWithValue("@Organization", replaceSingleQuote(org))

End Select

Select Case isEmpty(occ)

Case True

sqlCmdNew.Parameters.AddWithValue("@Occupation", System.DBNull.Value)

Case False

sqlCmdNew.Parameters.AddWithValue("@Occupation", replaceSingleQuote(occ))

End Select

sqlCmdNew.Parameters.AddWithValue("@Address1", System.DBNull.Value)

sqlCmdNew.Parameters.AddWithValue("@Address2", System.DBNull.Value)

Select Case isEmpty(city(0))

Case True

sqlCmdNew.Parameters.AddWithValue("@City", System.DBNull.Value)

Case False

sqlCmdNew.Parameters.AddWithValue("@City", replaceSingleQuote(city(0)))

End Select

sqlCmdNew.Parameters.AddWithValue("@ZipCode", System.DBNull.Value)

sqlCmdNew.Parameters.AddWithValue("@PostalCode", System.DBNull.Value)

Select Case IsNothing(state)

Case True

sqlCmdNew.Parameters.AddWithValue("@State", System.DBNull.Value)

Case False

sqlCmdNew.Parameters.AddWithValue("@State", state)

End Select

sqlCmdNew.Parameters.AddWithValue("@Other", System.DBNull.Value)

Select Case IsNothing(country)

Case True

sqlCmdNew.Parameters.AddWithValue("@Country", System.DBNull.Value)

Case False

sqlCmdNew.Parameters.AddWithValue("@Country", country)

End Select

sqlCmdNew.Parameters.AddWithValue("@GeneralEmail", False)

sqlCmdNew.Parameters.AddWithValue("@FundRaiseEmail", False)

sqlCmdNew.Parameters.AddWithValue("@GeneralUSPSMail", False)

sqlCmdNew.Parameters.AddWithValue("@FundRaiseUSPSMail", False)

sqlCmdNew.Parameters.AddWithValue("@DateAdded", rdate)

sqlCmdNew.Parameters.AddWithValue("@DateUpdated", rdate)

sqlCmdNew.Parameters.AddWithValue("@Active", True)

sqlCmdNew.Parameters.AddWithValue("@UserType", userType)

Dim iRows As Integer = 0

' try to insert the row into the table

Try

sqlConnN.Open()

iRows = sqlCmdNew.ExecuteNonQuery()

If iRows <> 1 Then

errors += 1

mesg += "<br />User: " & firstName & " " & lastName & " was not added.<br />"

End If

newUserCnt += 1

Catch ex As Exception

Dim exMsg As String = ex.Message.ToString()

writeApplicationLog(exMsg, ConfigurationManager.AppSettings("UtilityDBName"))

errors += 1

mesg += "<br />User: " & firstName & " " & lastName & " was not added due to " & exMsg & "<br />"

Finally

sqlConnN.Close()

sqlCmdNew.Dispose()

End Try

End Sub

Function getState(ByVal loc As String) As String

Dim state As String = Nothing

If loc.Contains("NY") Then

state = "NY"

ElseIf loc.Contains("WA") Then

state = "WA"

ElseIf loc.Contains("PA") Then

state = "PA"

ElseIf loc.Contains("AZ") Then

state = "AZ"

ElseIf loc.Contains("MI") Then

state = "MI"

ElseIf loc.Contains("WI") Then

state = "WI"

ElseIf loc.Contains("CT") Then

state = "CT"

ElseIf loc.Contains("NC") Then

state = "NC"

ElseIf loc.Contains("CA") Then

state = "CA"

ElseIf loc.Contains("NY") Then

state = "NJ"

End If

Return state

End Function

Function getCountry(ByVal cntry As String) As String

Dim country As String = "US"

If cntry.Contains("Canada") Then

country = "CA"

End If

Return country

End Function

End Class