Select Count not retreiving data

[Waldis_1]

When one is out of practice easy becomes difficult. In this function I want to count the records with icliente status "pendiente" as criteria so we know if customer has a pending loan, but the function returns 0. What's wrong in the function?

Public Function prestamospendientes()
        MsgBox(Me.txtid.Text)
        Me.Refresh()
        Dim prestamopendiente As Integer
        Dim prestamostatus As String
        Dim archivo As String
        archivo = Application.StartupPath
        Dim path As String
        path = archivo & "\prestamos.accdb"
        'clientes = path

        Dim connection = " Provider=Microsoft.ace.OLEDB.12.0;" & _
    "Data Source= " & path
        Dim conn As New OleDbConnection(connection)

        conn.Open()

        Dim str = "select count(*) as prestamopendiente from [tblprestamosinteres] where [idcliente] = '" + txtid.Text + "'"

        Dim cmd3 As OleDbCommand = New OleDbCommand(str, conn)
        Dim dr As OleDbDataReader
        dr = cmd3.ExecuteReader
        MsgBox(prestamopendiente)
        If dr.HasRows Then
            dr.Read()
            If IsDBNull(dr("prestamopendiente")) Then
                prestamostatus = MessageBox.Show("Cliente tiene préstamo pendiente.  " & vbCrLf _
                                & "No se puede realizar nuevo préstamo.", "Formulario Préstamo", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                gridclientes.Enabled = False
            Else
                prestamostatus = MessageBox.Show("Cliente no tiene préstamos pendientes. " & vbCrLf _
                                               & "Favor proceder a realizar el préstamo.", "Formulario Préstamo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Else
            prestamostatus = MessageBox.Show("Cliente xxxxno tiene préstamos pendientes. " & vbCrLf _
                               & "Favor proceder a realizar el préstamo.", "Formulario Préstamo", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
        Return prestamostatus

        dr.Close()
    End Function

[tinstaafl]

Based on what I can tell from translating your spanish, You're checking if the outstandingloan column is empty, then saying the customer has outstanding loans. I'm wondering if your conditional statement is reversed:

If IsDBNull(dr("prestamopendiente")) Then
    prestamostatus = MessageBox.Show("Cliente no tiene préstamos pendientes. " & vbCrLf _
    & "Favor proceder a realizar el préstamo.", "Formulario Préstamo", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
    prestamostatus = MessageBox.Show("Cliente tiene préstamo pendiente. " & vbCrLf _
    & "No se puede realizar nuevo préstamo.", "Formulario Préstamo", MessageBoxButtons.OK, MessageBoxIcon.Warning)
    gridclientes.Enabled = False    
End If

[Waldis_1]

I want to retrieve from the tblprestamos (prestamos means loan in english) if the customer already have an loan with pending balance. SQL statement must check in the table if exist a loan assigned to the client selected in a datagriedview and if there's one that is in status "Pendiente" (pendind in english) a message must pops up warning that customer has a loan open and he must pay everything before applying for a new one ("Cliente tiene préstamo pendiente.No se puede realizar nuevo préstamo.").

In case there's not a loan assigned to the customer a
message pops up saying that he can apply for the loan (("Cliente no tiene préstamos pendientes.Favor proceder a realizar el préstamo").

The problem is that the select statement:
Dim str = "select count(*) as prestamopendiente from [tblprestamosinteres] where [idcliente] = '" + txtid.Text + "'"

returns 0 even when the customer has a loan pending for payment, and besides I want to add to the statement the condition "status like 'Pendiente'.

[Santanu.Das]

Quite right. you should get the result is zero because you tried to count the number, but didn't try to get the number.
To get the appropriate result your codes should be the followings.

       Dim str = "select count(*) from [tblprestamosinteres] where [idcliente] = '" + txtid.Text + "'"

        Dim cmd3 As OleDbCommand = New OleDbCommand(str, conn)

            If (cmd3.ExecuteScalar > 0) Then
                prestamostatus = MessageBox.Show("Cliente tiene préstamo pendiente.  " & vbCrLf _
                                & "No se puede realizar nuevo préstamo.", "Formulario Préstamo", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                gridclientes.Enabled = False
            Else
                prestamostatus = MessageBox.Show("Cliente no tiene préstamos pendientes. " & vbCrLf _
                                               & "Favor proceder a realizar el préstamo.", "Formulario Préstamo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If


        cmd3.Dispose()
Return prestamostatus

[Santanu.Das]

There is another way to assign the return value. I suppose it should be help you.
Here I am trying to check if the client has any loan or not by clicking a button.

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        Dim msgStr As String = ""
        Dim msgIcon As MessageBoxIcon

        If prestamospendientes() Then
            msgStr = "Cliente tiene préstamo pendiente.  " & vbCrLf & _
                     "No se puede realizar nuevo préstamo."
            msgIcon = MessageBoxIcon.Warning

            gridclientes.Enabled = False
        Else
            msgStr = "Cliente no tiene préstamos pendientes. " & vbCrLf & _
                     "Favor proceder a realizar el préstamo."
            msgIcon = MessageBoxIcon.Information
        End If

        MessageBox.Show(msgStr, "Formulario Préstamo", MessageBoxButtons.OK, msgIcon)



    End Sub

    Public Function prestamospendientes() As Boolean
        MsgBox(Me.txtid.Text)
        Me.Refresh()
        Dim prestamopendiente As Integer

        Dim archivo As String
        archivo = Application.StartupPath
        Dim path As String
        path = archivo & "\prestamos.accdb"
        'clientes = path
        Dim connection = " Provider=Microsoft.ace.OLEDB.12.0;" & _
                         "Data Source= " & path
        Dim conn As New OleDbConnection(connection)
        conn.Open()

        Dim str = "select count(*) from [tblprestamosinteres] where [idcliente] = '" + txtid.Text + "'"
        Dim cmd3 As OleDbCommand = New OleDbCommand(str, conn)

        prestamopendiente = cmd3.ExecuteScalar 'Counts the number of records
        cmd3.Dispose()

        'Return the value
        If prestamopendiente > 0 Then
            Return True
        Else
            Return False
        End If


    End Function

[Waldis_1]

Done!!, thanks a lot.