asked on

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

AndyAinscow

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.)

Yannick Lapierre

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

EDW_Gideon

ASKER

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.

ASKER CERTIFIED SOLUTION

EDW_Gideon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EDW_Gideon

ASKER

I figured out the issue w/ the assistance of my DBA.