none
Find the last row in a datset? RRS feed

  • Question

  • I have a textbox which the user types the ID number of the record to update that record.  I found when I inadvertantly typed a number greater than the last ID number the computer hung, because there whas no such record.

    Therefore I want to check the last row number so that I can cause a message to be displayed if that number is exceeded.

    Here is the code:

    Imports System.Data.SqlClient
    Public Class FrmOne
        Private MyDatAdp As New SqlDataAdapter
        Private MyDataTbl As New DataTable
        Private MyRowPosition As Integer = 0
        Private Sub Findbtn_Click(sender As Object, e As EventArgs) Handles Findbtn.Click
            Dim VNo As String = VNotxt.Text
            Dim VID As String = VNotxt.Text
            Dim cn As New SqlConnection With {.ConnectionString = "Data Source=DESKTOP-S7FRNAL\SQLEXPRESS;Initial Catalog=Verses_Find;Integrated Security=True"}
            Dim MyDatAdp As New SqlDataAdapter("Select VID, VERSE from Verse where VID = @VID", cn)
            MyDatAdp.SelectCommand.Parameters.AddWithValue("@VID", VNo)
            Dim MyDataTbl As New DataTable
            MyDatAdp.Fill(MyDataTbl)
     
            If String.IsNullOrEmpty(VNotxt.Text) Then
                MsgBox("You did not enter a number")
    
            Else
                Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
                Dim strVerse As String = MyDataRow("Verse").ToString
                Versetxt.Text = strVerse
            End If
    
        End Sub
    
        Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
            VNotxt.Text = ""
            Versetxt.Text = ""
        End Sub
        Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    
            Using cn As New SqlConnection With {.ConnectionString = "Data Source=DESKTOP-S7FRNAL\SQLEXPRESS;Initial Catalog=Verses_Find;Integrated Security=True"},
                    cmd As New SqlCommand
    
                cn.Open()
                cmd.Connection = cn
                cmd.CommandText = "Update Verse Set [Verse] = @Verse Where VID = @VID"
                cmd.Parameters.AddWithValue("@VID", Me.VNotxt.Text)
                cmd.Parameters.AddWithValue("@Verse", Me.Versetxt.Text)
    
                Dim num = cmd.ExecuteNonQuery
                MessageBox.Show("Number of Records Updated = " & num.ToString)
            End Using
    
        End Sub
    
        Private Sub btnShowSecond_Click_1(sender As Object, e As EventArgs)
            Dim SecondForm As New frmSecond
            SecondForm.Show()
        End Sub
    
        Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
            Close()
        End Sub
    
        Private Sub FrmOne_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Me.TopMost = True
            Me.WindowState = FormWindowState.Maximized
        End Sub
    End Class

    I was going to put an EsleIf expression after the mesage box that tells you if you have not typed a number.

    The knowledge is limited so not sure how to do this?

    Your help will be appreciated!


    TEH

    Monday, March 11, 2019 4:31 PM

Answers

  • Maybe you should change the first Else to:

    . . .

    Else

       If MyDataTbl.Rows.Count = 0 Then

          MsgBox(“No such data in database”)

       Else

          Dim MyDataRow As DataRow = MyDataTbl.Rows(0)

          Dim strVerse As String = MyDataRow("Verse").ToString

          Versetxt.Text = strVerse

       End If

    End If

    • Marked as answer by Rocky48 Monday, March 11, 2019 6:37 PM
    Monday, March 11, 2019 5:58 PM

All replies

  • Hi

    Here, a DataGridView called DGV may have or may not have the AllowUserToAddRows set to True or False.  Assuming the row count required must ignore the New Row at the bottom, then:

        Dim rowcount As Integer = 0
        Select Case DGV.AllowUserToAddRows
          Case True
            ' do not include blank New Row
            rowcount = DGV.RowCount - 1
          Case Else
            rowcount = DGV.RowCount
        End Select

    If you just want the row count for a DataTable then:

    ' for DataTable only
     rowcount = dt.Rows.Count


    Regards Les, Livingston, Scotland



    • Edited by leshay Monday, March 11, 2019 5:20 PM
    Monday, March 11, 2019 5:09 PM
  • Maybe you should change the first Else to:

    . . .

    Else

       If MyDataTbl.Rows.Count = 0 Then

          MsgBox(“No such data in database”)

       Else

          Dim MyDataRow As DataRow = MyDataTbl.Rows(0)

          Dim strVerse As String = MyDataRow("Verse").ToString

          Versetxt.Text = strVerse

       End If

    End If

    • Marked as answer by Rocky48 Monday, March 11, 2019 6:37 PM
    Monday, March 11, 2019 5:58 PM
  • I prefered the first answer.

    Thanks for the help!


    TEH

    Monday, March 11, 2019 6:39 PM