none
Sql Error RRS feed

  • Question

  • I hate to be stupid (a constant state at times), but I posted the wrong code. Similar, but still, Wrong.
     
    Below is the actual code. Sorry, life is hectic.
     
    The counter I put in there will reflect 981 when it goes to the catch block. There are only 980 rows.
     
    As you can see at the bottom, I do not have a finally in my try/catch block, but I do close and dispose.
     
    This is a default search which means the noInput will be true and the below string will be used.
     
    I did get some very odd results when I started changing the reads around.
     
    If I comment out while dbreader.hasrows() and use if (dbreader.read()) instead, I get only a single result.
    if I put the if (dbreader.read()) inside of the while dbreader.hasrows(), the while continues to execute, while the if stops at 490 rows and they are not even in the correct order.
     
    Strange.
     

    ' start trying to find out what the search criteria is first

    Public Function getSearchResults() As Integer

    ' get the parameters to be used for the search

    Dim noInput As Boolean = getSearchParms()

    ' no parms, means get all

    If noInput Then

    sqlString = "SELECT resume.resumeId, resume.title, CONVERT(char(20), resume.dateModified, 107) AS dateModified, resumeUser.firstName, resumeUser.lastName, resumeUser.city, resumeUser.state FROM resume INNER JOIN resumeUser ON resume.userId = resumeUser.userId ORDER BY dateModified "

    Else

    ' builds the sql strings for the various components of the search

    ' when done, an array of strings will be available to parse

    buildSqlStrings()

    For ix = 0 To sqlStringSet.Length - 1

    If Not utils.isEmpty(sqlStringSet(ix)) Then

    tempSql += sqlStringSet(ix)

    If ix < sqlStringSet.Length - 1 Then

    tempSql += " And "

    End If

    End If

    Next

    tempSql = tempSql.Substring(0, tempSql.LastIndexOf(")") + 1)

    sqlString += tempSql

    ' finalize string

    sqlString += " ORDER BY dateModified2 DESC"

    End If

    sqlCmd = New SqlCommand

    sqlCmd.Connection = sqlConn

    sqlCmd.CommandText = sqlString

    sqlCmd.CommandType = CommandType.Text

    dbReader = Nothing

    ' define the new datatable to hold our results

    dt = New DataTable()

    ' define the columns we will be saving

    Dim dcIT As New DataColumn("item", GetType(Integer))

    Dim dcRI As New DataColumn("resumeID", GetType(Integer))

    Dim dcLN As New DataColumn("lastName", GetType(String))

    Dim dcFN As New DataColumn("firstName", GetType(String))

    Dim dcTi As New DataColumn("title", GetType(String))

    Dim dcCt As New DataColumn("city", GetType(String))

    Dim dcSt As New DataColumn("state", GetType(String))

    Dim dcDM As New DataColumn("dateModified", GetType(String))

    ' add the columns to the table

    dt.Columns.Add(dcIT)

    dt.Columns.Add(dcRI)

    dt.Columns.Add(dcLN)

    dt.Columns.Add(dcFN)

    dt.Columns.Add(dcTi)

    dt.Columns.Add(dcCt)

    dt.Columns.Add(dcSt)

    dt.Columns.Add(dcDM)

    ' define datarow

    Dim dr As DataRow = Nothing

    Dim itCount As Integer = 0

    Try

    sqlConn.Open()

    dbReader = sqlCmd.ExecuteReader()

    If dbReader.HasRows Then

    While dbReader.HasRows()

    dbReader.Read()

    itCount += 1

    ' build the data table item from the database

    dr = dt.NewRow()

    dr("item") = itCount

    dr("resumeID") = dbReader.Item("resumeID")

    dr("lastName") = dbReader.Item("lastName").ToString()

    dr("firstName") = dbReader.Item("firstName").ToString()

    dr("title") = dbReader.Item("title").ToString()

    dr("city") = dbReader.Item("city").ToString()

    dr("state") = dbReader.Item("state").ToString()

    dr("dateModified") = dbReader.Item("dateModified").ToString()

    dt.Rows.Add(dr)

    End While

    End If

     

    Catch ex As Exception

    Dim exMsg As String = Request.ServerVariables("Script_Name") + ", getSearchResults(" & sqlString & "): msg=" + ex.Message.ToString()

    utils.writeApplicationLog(exMsg, System.Configuration.ConfigurationManager.AppSettings("UtilityDbName"))

    End Try

    dbReader.Close()

    If sqlConn.State = ConnectionState.Open Then

    sqlConn.Dispose()

    End If

    rResumeResults.DataSource = dt

    rResumeResults.DataBind()

    Return itCount

    End Function

    Friday, July 27, 2007 1:06 PM

Answers

All replies