The condition should be like below.
VB
Dim str As String = ConfigurationManager.ConnectionStrings(1).ConnectionString
Dim l_Connection As New SqlConnection(str)
l_Connection.Open()
Dim l_Command As New SqlCommand()
l_Command.CommandText = "SELECT MAX(CONVERT(INT, right(license_plate,8))) CurrentLP_No FROM lps_inner_label_hist where license_plate like 'G%'"
l_Command.CommandType = CommandType.Text
l_Command.Connection = l_Connection
Dim l_Adapter As New SqlDataAdapter(l_Command)
Dim l_Dataset As New DataSet()
l_Adapter.Fill(l_Dataset)
If l_Dataset.Tables(0).Rows.Count > 0 Then
If IsDBNull(l_Dataset.Tables(0).Rows(0).ItemArray(0)) Then
txt_licenseplate.Text = "GH00000001"
Else
Dim tempLicenseno As String = l_Dataset.Tables(0).Rows(0).ItemArray(0).ToString()
'Add your logic here.
txt_licenseplate.Text = tempLicenseno
End If
End If
l_Dataset.Clear()
l_Connection.Close()
As you are using MAX in the query it always return 1 row.