[RESOLVED] How to Auto copy last record of column to text box and divide the value.

hi All,

I want to make a program that will auto copy the last value of a column in sql server table to a text box and divide the value and show result to another text box.

I want if i click load it will automatic compute show all value in text box.
is that possible ? i'm using vb 2010
thanks in advance.

Attachment 160211

here is my current code to get the query .

Code:

---

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        connection.Open()
        sqlCmd = connection.CreateCommand
        ' sqlCmd.CommandText = "SELECT * FROM Muscatel_MPCA_FCT1_LogData_T where SQLDateTime >= '2018-07-02T08:00:00 AM' AND SQLDateTime <= '2018-07-03T07:59:00 AM'"

        sqlCmd.CommandText = "WITH Src AS (SELECT station_no,CASE WHEN TestResult = 'ok' THEN 1 END AS ok,CASE WHEN TestResult = 'NG' " &
            "THEN 1 END AS NG,CASE WHEN TestResult = 'RR' THEN 1 END AS RR FROM test_table WHERE SQLDateTime >= '2018-07-02T08:00:00 AM'AND SQLDateTime <= '2018-07-03T07:59:00 AM'and Station_No in (1,2,3,4,5,6))SELECT station_no, COUNT(ok) AS TotalOK,COUNT(NG) AS TotalNG,COUNT(RR) AS TotalRR,COUNT(ok) + COUNT(NG) + COUNT(RR) AS Total FROM Src GROUP BY ROLLUP (Station_No)ORDER BY Station_No DESC"
        Dim dr As SqlDataReader = sqlCmd.ExecuteReader
        If dr.HasRows Then
            Dim dtRecords As New DataTable
            dtRecords.Load(dr)
            DataGridView1.DataSource = dtRecords
        End If
        dr.Close()
        connection.Close()

    End Sub

---

It's a simple case of getting the last DataRow from the DataTable, getting the values from the two fields, performing the division and then displaying the three values:

vb.net Code:

Dim rows = dtRecords.Rows
Dim lastRow = rows(rows.Count - 1)
Dim totalOk = CInt(lastRow("TotalOK"))
Dim total = CInt(lastRow("Total"))
Dim yield = totalOk / total
 
totalOkTextBox.Text = totalOk.ToString()
totalTextBox.Text = total.ToString()
yieldTextBox.Text = yield.ToString("p2")

Note that the format specifier "p2" formats a number as a percentage with two decimal places, which includes multiplying by 100.

Thanks it works!!

hi Sir Jm,

I try to add text box to compute efficiency .
total input is same.
target = user input .example 250 then efficiency
Efficiency = total input / target

Code:

---

    Dim noFixture = CInt(txtTarget.Text) * 6 'multiply with no.of fixture.
            Dim Efficiency As Integer
            Efficiency = CInt(txtTotalInputEff.Text) / CInt(txtTarget.Text)
            txtEfficiency.Text = Efficiency.ToString("p2")
            txtTarget.Text = noFixture      'target(user input) * no of fixture

---

I got an error convesion from string to integer not valid ???

Attachment 160215

Well, what line of code threw the exception and what was the String that wasn't valid? It should be fairly obvious why it can't be converted to an Integer. The question is why are you trying to convert that text in the first place if it isn't a valid number? The other question is why do you need to convert at all when, if you're displaying a number in a TextBox, you must have actually had the data as a number in the first place so why are you not using that? The final question is why are you asking a different question in this same thread when the issue this thread is about has already been resolved? Please keep each thread to a single topic and each topic to a single thread.