1
\$\begingroup\$

Aim: Get percentage based on if field is blank or not

Issue: Unknown amount of variables based on search and thus speed

Solution: Dynamic SQL build up

Looking for: Tips to speed up even more/spot injection as required!

Notes: removed all but one vString SQL line for viewing purposes

  Dim vString As String = ""
        If Trim(sUPRN.Text) <> "" Then vString = "p.UPRN Like '%" & Trim(sUPRN.Text) & "%'"


    If vString <> "" Then
        vString = " and " & vString
    End If


Dim ConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQL").ToString()
'Count total lines of data and see if they have a certifacte then get percentage
        Try


        Using connection = New SqlConnection(ConnectionString)
            '######### VERY IMPORTANT --> DOCURL MUST BE NULL FOR THIS TO WORK! (not blank!!)
            Using command As New SqlCommand("Select count(d.docurl)  * 100.0 / sum(count(d.id)) over() From TblData   AS D  JOIN TBLPROPERTYLIST AS p ON d.UPRN = p.UPRN WHERE 1 = 1 " & vString & "", connection)
                connection.Open()
                lblCertOutput.Text = Math.Floor(command.ExecuteScalar())
            End Using

        End Using

    Catch ex As Exception
        lblCertOutput.Text = "N/A"
    End Try
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.