Solved

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

Posted on 2014-03-05
5
543 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 44

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now