none
changing command button based on sql query

    Question

  • i have a cmd button (btnGenerateDocs) whose properties i want to change based on a couple of sql query result... i run the query and check the results in a function called (showGenerateDocsbtn)

    the problem i am getting is that if dr.HasRows returns true then dr1.HasRows also return true and vise-versa, even tho in reality dr1.HasRows shoudl return false. basically it give me the same result as dr.HasRows.... what am i doing wrong? is there a better way to do this?

    thanks

    below is the code

     'Checks if the employment verification and landlord document have been created. if so, it will hide the generate documents button.
    If showGenerateDocsbtn() = True Then
     
    Me.btnGenerateDocs.Visible = True
    Else
     
    Me.btnGenerateDocs.Visible = False
    End If

     

    Protected Function showGenerateDocsbtn() As Boolean

    Dim conn As New SqlConnection(My.Settings.connStr)

    Dim conn1 As New SqlConnection(My.Settings.connStr)

    conn.Open()

    conn1.Open()

    Dim ShowBtn As Boolean = False

    'check employer doc

    Dim sql As String = "SELECT tblProspectDocuments.ProsDoc_ID, tblProspectDocuments.ProsDoc_DocID, tblProspectDocuments.ProsDoc_UserID, tblProspectDocuments.ProsDoc_datetime, tblProspectDocuments.ProsDoc_ProsID, tblProspectDocuments.ProsDoc_FileLocation, tblDocuments.Doc_Purpose" & _

    " FROM tblProspectDocuments INNER JOIN tblDocuments ON tblProspectDocuments.ProsDoc_DocID = tblDocuments.Doc_ID" & _

    " WHERE tblProspectDocuments.ProsDoc_ProsID = " & Me.cmpProspectFind.SelectedValue & " AND (tblDocuments.Doc_Purpose = 'EmplVerification')"

    Dim cmd As New SqlCommand(sql, conn)

    Dim dr As SqlDataReader

    'check rental doc

    Dim sql1 As String = "SELECT tblProspectDocuments.ProsDoc_ID, tblProspectDocuments.ProsDoc_DocID, tblProspectDocuments.ProsDoc_UserID, tblProspectDocuments.ProsDoc_datetime, tblProspectDocuments.ProsDoc_ProsID, tblProspectDocuments.ProsDoc_FileLocation, tblDocuments.Doc_Purpose" & _

    " FROM tblProspectDocuments INNER JOIN tblDocuments ON tblProspectDocuments.ProsDoc_DocID = tblDocuments.Doc_ID" & _

    " WHERE tblProspectDocuments.ProsDoc_ProsID = " & Me.cmpProspectFind.SelectedValue & " AND (tblDocuments.Doc_Purpose = 'RentalFormerLandlord')"

    Dim cmd1 As New SqlCommand(sql, conn1)

    Dim dr1 As SqlDataReader

    dr = cmd.ExecuteReader

    dr.Read()

    If dr.HasRows Then 'employer doc found

    dr1 = cmd1.ExecuteReader

    dr1.Read()

    If dr1.HasRows Then 'rental doc found

    ShowBtn = False

    Else

    ShowBtn = True

    Me.btnGenerateDocs.Text = "Generate Landlord Docs"

    End If

    dr1.Close()

    cmd1 = Nothing

    conn1.Close()

    Else 'employer doc not found

    dr1 = cmd1.ExecuteReader

    dr1.Read()

    If dr1.HasRows Then 'rental doc found

    ShowBtn = True

    Me.btnGenerateDocs.Text = "Generate Employer Docs"

    Else

    ShowBtn = True

    Me.btnGenerateDocs.Text = "Generate Documents"

    End If

    dr1.Close()

    cmd1 = Nothing

    conn1.Close()

    End If

    dr.Close()

    cmd = Nothing

    conn.Close()

    showGenerateDocsbtn = ShowBtn

    End Function

    Tuesday, January 16, 2007 6:05 PM

Answers

  • Try this:

      Dim cmd1 As New SqlCommand(sql1, conn1)

    Note the extra "1".  Choosing good variable names keeps you out of trouble...
    Wednesday, January 17, 2007 12:16 AM