Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-05
5
Medium Priority
?
630 Views
Last Modified: 2014-03-11
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

0
Comment
Question by:EDW_Gideon
  • 3
5 Comments
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 39908626
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
 
LVL 3

Expert Comment

by:Yannick Lapierre
ID: 39909018
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
 

Author Comment

by:EDW_Gideon
ID: 39909557
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
 

Accepted Solution

by:
EDW_Gideon earned 0 total points
ID: 39909655
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
 

Author Closing Comment

by:EDW_Gideon
ID: 39919997
I figured out the issue w/ the assistance of my DBA.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question