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
To All and Sundry. Ignore the post please. It is amazing what trying to pass nulls into a string variable can do for you. I thought I knew better by now.
NOT!
Sorry, T
No comments:
Post a Comment