none
How to see if a database has data RRS feed

  • Question

  • i have 3 forms first form is to insert a new person second is to insert a computer and the third is to see the info about the computer and the person and the forth is to search for persons and after open the person i want to open other form only in case if that person has computers and if he already has any i want to create other 

    1)search for person

    2)After i find the person it shows me the computers

    Monday, May 7, 2018 10:05 AM

All replies

  • Here are some conceptual thoughts. There are two methods in a class, one demo's getting computer count for a person while the other uses a slightly different method where we want to know if they have any computers no matter the count where if the return from the select statement in InsertDemo returns 1 if there is at least one computer or Nothing if no computers.

    InsertDemo receives a person primary key and data for adding a computer.

    Skeleton class for computer data

    ''' <summary>
    ''' Start of a class to represent a computer for a person
    ''' </summary>
    Public Class Computer
        ''' <summary>
        ''' Primary key
        ''' </summary>
        ''' <returns></returns>
        Public Property id() As Integer
        ''' <summary>
        ''' Key to link back to person
        ''' </summary>
        ''' <returns></returns>
        Public Property PersonIdentifier() As Integer
        ''' <summary>
        ''' Computer name
        ''' </summary>
        ''' <returns></returns>
        Public Property Name() As String
    End Class

    This class is a skeleton class for conceptual ideas on working with person and computers

    Imports System.Data.SqlClient
    ''' <summary>
    ''' This sample uses SQL-Server using SqlClient, if 
    ''' MS-Access use System.Data.OleDb data provider for
    ''' connection and command objects.
    ''' </summary>
    Public Class Operations
        Private ConnectionString As String = "TODO"
        ''' <summary>
        ''' Get count of computers for a person
        ''' </summary>
        ''' <param name="pIdentifier">Person primary key</param>
        ''' <returns></returns>
        Public Function PersonHasComputer(pIdentifier As Integer) As Integer
            Dim computerCount As Integer = 0
    
            Using cn As New SqlConnection() With {.ConnectionString = ConnectionString}
    
                Dim statement = "SELECT 1 FROM  dbo.Computers AS C WHERE C.Identifier = @Identifier"
    
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
    
                    cmd.Parameters.AddWithValue("@Identifier", pIdentifier)
    
                    Try
    
                        cn.Open()
    
                        computerCount = CInt(cmd.ExecuteScalar())
    
                    Catch ex As Exception
                        ' decide how to handle
                    End Try
                End Using
            End Using
    
            Return computerCount
    
        End Function
        ''' <summary>
        ''' See if person has computer(s) skeleton and
        ''' pathways for adding.
        ''' </summary>
        ''' <param name="pIdentifier">Person primary key</param>
        ''' <param name="pComputer">An instance of the computer to add</param>
        ''' <returns></returns>
        Public Function InsertDemo(pIdentifier As Integer, pComputer As Computer) As Boolean
            Dim success As Boolean = True
    
            Using cn As New SqlConnection() With {.ConnectionString = ConnectionString}
    
                Dim statement = "SELECT 1 FROM  dbo.Computers AS C WHERE C.Identifier = @Identifier"
    
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
    
                    cmd.Parameters.AddWithValue("@Identifier", pIdentifier)
    
                    Try
    
                        cn.Open()
    
                        ' see if computer exists using query above
                        If cmd.ExecuteScalar() Is Nothing Then
                            ' no computer exists, insert the first one
                        Else
                            ' at least one computer exists, add another
                        End If
    
                    Catch ex As Exception
                        success = False
                    End Try
                End Using
            End Using
    
            Return success
    
        End Function
    
    End Class

    Of course there are indeed variations on the above, I didn't want to get into multiple ideas and be confusing so I only did one.

    Note that the above is for SQL-Server but changing the data provider it will work with other databases e.g. MS-Access, MySQL, Oracle etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, May 7, 2018 10:28 AM
    Moderator
  • i have 3 forms first form is to insert a new person second is to insert a computer and the third is to see the info about the computer and the person and the forth is to search for persons and after open the person i want to open other form only in case if that person has computers and if he already has any i want to create other 

    Hi Rob12212,

    According to your description, I am not sure what you want to do, so I want to confirm some information.

    One is to insert new person and computer, these are two tables? And you want to insert some new record into these two tables?

    Second is about Person and computer, if these are tables, are they related?

    Third is about this sentence, can you explain this:

     forth is to search for persons and after open the person i want to open other form only in case if that person has computers and if he already has any i want to create other 

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 8, 2018 2:43 AM
    Moderator
  • Yea human logic and logic with computers is different, most people don't recognize it. 

    Therefore you want to see if persons have computers and then you can open for them a form. It seems the same as you wrote and your human mind accept it like that. But for a program it is a world of difference. 

    You first create therefore an sql statement around the computer 

    Select * from computers where person = TheNameOfTheClient

    And if that does not return nothing

    Select * from persons where @person = TheNameOfTheClient

    The way you do that is complete depending from what database you use and what technology to retrieve the data.

    That can be hundred ways. 


    Success
    Cor


    Tuesday, May 8, 2018 9:25 AM