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

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

Open in new window

EDW_GideonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
Does it help if you removed the line:
command.CommandTimeout = 1200


ps.  I assume you have copied and pasted the SQL statement from the code into a database view, not just retyped it as you think it is.  (That way you know it is identical.)
0
Yannick LapierreSoftware DirectorCommented:
I can't test right now, but i suspect the Command object.
Can you try to user a different command object for the second query?

like.
dim command2 as DbCommand = con.CreateCommand
0
EDW_GideonAuthor Commented:
Andy,

I copied the entire statement and manually deleted the VB portion into a SQL executer and it worked.  I also tried to comment out the timeout without success.

Yannick,

I removed the first query and ran this as a standalone and it still showed false on records.  I can try declaring a secondary command but I don't think it will change anything.  

P.S.  I've also tried to apply this as one whole string to a variable in case something was happening in the SqlQuery.Append step.  I'm now having a DBA pull a report to see what Teradata is receiving to see if somehow the query is being interrupted differently...  I'm at a complete and utter loss...  Similar code (Different SQL) is used in other subs in my app and it works fine so I really don't get it.
0
EDW_GideonAuthor Commented:
Figured it out...  The ANSI login was causing the query compares to be case sensitive...  Thats what I get for copying other code!  I removed the ANSI line and received the record I was looking for, Thanks!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EDW_GideonAuthor Commented:
I figured out the issue w/ the assistance of my DBA.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.