troubleshooting Question

reader.HasRows showing false but the query shows true?!

Avatar of EDW_Gideon
EDW_GideonFlag for United States of America asked on
.NET ProgrammingMicrosoft Development
5 Comments1 Solution873 ViewsLast Modified:
When I run the second data reader Select statement outside of VB.Net I get rows but when I run it in VS10 it's showing none... What am I missing?!

    Dim SelStmt, MyID As String
    Dim pf As DbProviderFactory = DbProviderFactories.GetFactory("Teradata.Client.Provider")
    Dim con As DbConnection = pf.CreateConnection()
    Dim stringBuilder As New Teradata.Client.Provider.TdConnectionStringBuilder()
    Dim SqlQuery As StringBuilder = New StringBuilder

    If InStr(ddUser.Text, ":") > 2 Then
      If rblSort.SelectedValue = 0 Then
        Session.Contents("UserID") = Mid$(ddUser.Text, 1, (InStr(ddUser.Text, ":") - 2))
        Session.Contents("EmpID") = Mid$(ddUser.Text, (InStr(ddUser.Text, "~") + 2), 10)
        Session.Contents("lName") = Mid$(ddUser.Text, (InStr(ddUser.Text, ":") + 2), (InStr(ddUser.Text, ",") - (InStr(ddUser.Text, ":") + 2)))
        If InStr(ddUser.Text, ".") > 0 Then
          Session.Contents("fName") = Mid$(ddUser.Text, (InStr(ddUser.Text, ",") + 2), ((InStr(ddUser.Text, ".") - 2) - (InStr(ddUser.Text, ",") + 2)))
          Session.Contents("mInt") = Mid$(ddUser.Text, (InStr(ddUser.Text, ".") - 1), 1)
        Else
          Session.Contents("fName") = Mid$(ddUser.Text, (InStr(ddUser.Text, ",") + 2), ((InStr(ddUser.Text, "~") - 1) - (InStr(ddUser.Text, ",") + 2)))
          Session.Contents("mInt") = ""
        End If
      Else
        Session.Contents("UserID") = Mid$(ddUser.Text, (InStr(ddUser.Text, ":") + 2), (InStr(ddUser.Text, "~") - 1) - (InStr(ddUser.Text, ":") + 2))
        Session.Contents("EmpID") = Mid$(ddUser.Text, (InStr(ddUser.Text, "~") + 2), 10)
        Session.Contents("lName") = Mid$(ddUser.Text, 1, (InStr(ddUser.Text, ",") - 1))
        If InStr(ddUser.Text, ".") > 0 Then
          Session.Contents("fName") = Mid$(ddUser.Text, (InStr(ddUser.Text, ",") + 2), ((InStr(ddUser.Text, ".") - 2) - (InStr(ddUser.Text, ",") + 2)))
          Session.Contents("mInt") = Mid$(ddUser.Text, (InStr(ddUser.Text, ".") - 1), 1)
        Else
          Session.Contents("fName") = Mid$(ddUser.Text, (InStr(ddUser.Text, ",") + 2), ((InStr(ddUser.Text, "~") - 1) - (InStr(ddUser.Text, ",") + 2)))
          Session.Contents("mInt") = ""
        End If
      End If
    Else
      MsgBox("No user has been selected", vbExclamation, "Error")
      Exit Sub
    End If

    stringBuilder.DataSource = Session.Contents("DataSource")
    stringBuilder.UserId = Session.Contents("Login")
    stringBuilder.Password = Session.Contents("Passwd")
    stringBuilder.PersistSecurityInfo = True
    stringBuilder.SessionMode = "ANSI"

    MyID = Trim(Session.Contents("UserID").ToString)

    con.ConnectionString = stringBuilder.ConnectionString

    SqlQuery.Clear()
    SqlQuery.Append("Select user_status, user_last_logon_date, user_suspend_date, user_drop_date, consultant_expiration_date ")
    SqlQuery.Append("from pims2_prdrpt_tbls.teradata_user_details where user_id  = '" & MyID.ToString & "';")

    SelStmt = SqlQuery.ToString()
    Dim command As DbCommand = con.CreateCommand
    command.CommandText = SelStmt
    command.CommandType = CommandType.Text
    'command.CommandTimeout = 1200

    Try
      con.Open()
      Dim reader As DbDataReader = command.ExecuteReader()
      If reader.Read Then
        If reader.HasRows Then
          If IsDBNull(reader("user_status")) Then
            txtCurrStat.Text = ""
          Else
            txtCurrStat.Text = reader("user_status")
          End If

          If IsDBNull(reader("user_last_logon_date")) Then
            txtLastLogDt.Text = ""
          Else
            txtLastLogDt.Text = reader("user_last_logon_date")
          End If

          If IsDBNull(reader("user_suspend_date")) = True Then
            txtSusDt.Text = ""
          Else
            txtSusDt.Text = reader("user_suspend_date")
          End If

          If IsDBNull(reader("user_drop_date")) = True Then
            txtDropDt.Text = ""
          Else
            txtDropDt.Text = reader("user_drop_date")
          End If

          If IsDBNull(reader("consultant_expiration_date")) = True Then
            txtConVenExp.Text = ""
          Else
            txtConVenExp.Text = reader("consultant_expiration_date")
          End If
        Else
          MsgBox("User Not Found on Teradata User Details", vbExclamation, "Warning")
          txtCurrStat.Text = "Unknown"
          txtLastLogDt.Text = "Unknown"
          txtSusDt.Text = "Unknown"
          txtDropDt.Text = "Unknown"
          txtConVenExp.Text = "Unknown"
        End If
        reader.Close()
      End If
    Catch ex As Exception
      MsgBox("Error Retrieving Teradata User Details: " & ex.Message, vbExclamation, "ERROR")
      Exit Sub
    End Try

'*************************************************'
'*****************  ISSUE BELOW  *******************'
'*************************************************'

    SelStmt = ""
    SqlQuery.Clear()
    SqlQuery.Append("Select DBASE.commentstring ,")
    SqlQuery.Append(" DBASE.PasswordModTime AS PasswordLastModDate,")
    SqlQuery.Append(" DB2.DATABASENAME as LastAlterName,")
    SqlQuery.Append(" DBASE.LASTALTERTIMESTAMP,")
    SqlQuery.Append(" ((100 * ((4 * DBASE.LockedDate - 1) / 146097)+ (4 * (((4 * DBASE.LockedDate - 1) MOD 146097) / 4) + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * DBASE.LockedDate - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) / 12) * 10000 + (((5 * (((4 * (((4 * DBASE.LockedDate - 1)MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12 + 1) * 100 + ((5 * (((4 * (((4 * DBASE.LockedDate - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5 (date, format 'yy/mm/dd', Named LockedDate),")
    SqlQuery.Append(" (Dbase.LockedTime / 60 ) * 100 +(Dbase.LockedTime MOD 60) (Integer, format '99:99', Named LockedTime), ")
    SqlQuery.Append(" DBASE.LockedCount ")
    SqlQuery.Append("From dbc.dbase, ")
    SqlQuery.Append("DBC.DBASE DB2 ")
    SqlQuery.Append("Where   DBASE.databasename = '" & MyID.ToString & "'  And    DBASE.rowtype = 'u' AND dbase.LastAlterUID = DB2.DatabaseID;")

    SelStmt = SqlQuery.ToString()

    command = con.CreateCommand
    command.CommandText = SelStmt
    command.CommandType = CommandType.Text
    command.CommandTimeout = 1200

    Try
      Dim reader As DbDataReader = command.ExecuteReader()

      If reader.Read Then
        If reader.HasRows Then
          If IsDBNull(reader("commentstring")) = True Then
            txtCurrStat.Text = ""
          Else
            txtCurrStat.Text = reader("commentstring")
          End If

          If IsDBNull(reader("passwordlastmoddate")) = True Then
            txtCurrStat.Text = ""
          Else
            txtCurrStat.Text = reader("passwordlastmoddate")
          End If

          If IsDBNull(reader("lastaltername")) = True Then
            txtCurrStat.Text = ""
          Else
            txtCurrStat.Text = reader("lastaltername")
          End If

          If IsDBNull(reader("lastaltertimestamp")) = True Then
            txtCurrStat.Text = ""
          Else
            txtCurrStat.Text = reader("lastaltertimestamp")
          End If

          If IsDBNull(reader("lockeddate")) = True Then
            txtCurrStat.Text = ""
          Else
            txtCurrStat.Text = reader("lockeddate")
          End If

          If IsDBNull(reader("lockedtime")) = True Then
            txtCurrStat.Text = ""
          Else
            txtCurrStat.Text = reader("lockedtime")
          End If

          If IsDBNull(reader("lockedcount")) = True Then
            txtCurrStat.Text = ""
          Else
            txtCurrStat.Text = reader("lockedcount")
          End If
        Else
          MsgBox("User not found on DBC.Users", vbExclamation, "Error")
          reader.Close()
          txtCommStr.Text = "Unknown"
          txtPassLstMod.Text = "Unknown"
          txtLastAltNm.Text = "Unknown"
          txtLastAltrd.Text = "Unknown"
          txtPassLkDt.Text = "Unknown"
          txtLocTime.Text = "Unknown"
          txtLocCt.Text = "Unknown"
        End If
      End If
      reader.Close()
    Catch ex As Exception
      MsgBox("Error Retrieving Comment String from DBC.Users: " & ex.Message, vbExclamation, "ERROR")
      Exit Sub
    Finally
      con.Close()
    End Try
ASKER CERTIFIED SOLUTION
EDW_Gideon

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros