none
How to auto calculate the available vacant slot RRS feed

  • Question

  • Good day,

    I have a simple registration program where a person will register under 1 table, each table have 10 empty vacant slot.

    I would like to know on the logic for both vb.net and SQL on how to achieve the result.

    Example.


    As there is 2 person register under table 2, the vacant slot should be 8 left.

    When i register another person under table 2, the vacant slot will be 7 left.

    Wednesday, February 19, 2020 4:04 AM

All replies

  • Hi Zetsubo69,

    Thank you for posting here.

    According to your description, you can use a variable to store 'vacant slot'.

    Check the following example:

        Private Shared number As Integer = 10
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ds As DataSet = New DataSet()
            Dim connString As String = "connection string"
    
            Using conn As SqlConnection = New SqlConnection(connString)
                conn.Open()
                Dim cmdInsert As String = $"INSERT INTO TableTest VALUES ('{TextBox1.Text}');"
    
                Using cmd As SqlCommand = New SqlCommand With {
                    .Connection = conn
                }
                    cmd.CommandText = cmdInsert
                    cmd.ExecuteNonQuery()
                    number -= 1
                    Label1.Text = number.ToString()
                End Using
    
                Dim cmdSelect As String = $"SELECT * FROM TableTest"
    
                Using da As SqlDataAdapter = New SqlDataAdapter(cmdSelect, conn)
                    da.Fill(ds)
                    DataGridView1.DataSource = ds.Tables(0)
                End Using
            End Using
    
            TextBox1.Clear()
        End Sub

    Result:

    Besides, you can also use a file to save the 'vacant slot'.

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    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.

    Wednesday, February 19, 2020 9:57 AM
    Moderator
  • Good day,

    Thanks for helping me as it has give me an idea.

    Is it possible to replicate the below formula from excel into SQL table ?

    =10-COUNTIF(I2:I3,1)

    If it is possible than whether i register a person to the table, in the back end the table will auto calculate itself. Do let me know if my understanding is wrong 

    

    Thursday, February 20, 2020 8:39 AM
  • Hi Zetsubo69,

    Thanks for your feedback.

    Yes, you can use COUNTIF function to count all registered people, then minus it by 10. You need to avoid negative result.

    Best Regards,

    Xingyu Zhao


    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.

    Thursday, February 20, 2020 9:57 AM
    Moderator
  • Good day,

    Is it possible to demo with a sample code as I don't really understand how it works 

    Friday, February 21, 2020 8:54 AM
  • Hi Zetsubo69,

    I use 'COUNTIF' function to count all registered people and save the result, you can refer to the following code.

    First of all, you need to install 'Microsoft.Office.Interop.Excel'.

     

    Code:

    Imports Excel = Microsoft.Office.Interop.Excel
    Module Module1
        Sub Main()
            Dim lst As List(Of Double) = New List(Of Double)()
            Dim path As String = "your file path"
            Dim xlApp As Excel.Application = New Excel.Application()
            Dim XLWB As Excel.Workbook = xlApp.Workbooks.Open(path, 0, True, 5, "", "", True, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, vbTab, False, False, 0, True, 1, 0)
            Dim wsht As Excel.Worksheet = XLWB.Sheets("Sheet1")
            Dim last As Excel.Range = wsht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing)
            ' 'A' is the column you want to count.
            Dim range As Excel.Range = wsht.Range("A2", "A" & last.Row)
    
            For i As Integer = 2 To last.Row
                Dim s As String = Convert.ToString(wsht.Cells(i, 1).Value)
                Dim count As Double = 10 - xlApp.WorksheetFunction.CountIf(range, s)
                lst.Add(count)
            Next
    
            For i As Integer = 0 To lst.Count - 1
                ' '3' means that you want to save values in 'C' column.
                wsht.Cells(i + 2, 3).Value = lst(i)
            Next
            wsht.Cells(1, 3).Value = "Count"
            xlApp.Visible = False
            xlApp.UserControl = False
            XLWB.SaveAs("your save path")
            XLWB.Close()
            xlApp.Quit()
        End Sub
    End Module

    My original excel:

    Result of my test:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    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, February 25, 2020 8:12 AM
    Moderator