Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Error Message to Appear When No Records are Pulled 1

Status
Not open for further replies.
dataman86

dataman86

Technical User
Oct 11, 2008
104
US
Hello out there,

I need to place an error message with a MessageBox.Show when the Select Statement does not pull any records from the Sql Server. No records will pull if incorrect parameters are entered. I just do not know where to place it in my coding.

Any help would be appreciated.

My Coding:


Imports System.Data.SqlClient
Imports System
Imports System.IO

Public Class Pull
Dim Irows As Integer
Dim _g As New PublicVars
Private Function TestIt() As Array

Dim sParm As String = Chr(39) & Me.TextBox1.Text.ToString & Chr(39)
Dim tParm As String = Chr(39) & Me.TextBox2.Text.ToString & Chr(39)
Dim uParm As String = Chr(39) & Me.TextBox3.Text.ToString & Chr(39)
Dim vParm As String = Chr(39) & Me.TextBox4.Text.ToString & Chr(39)
Dim wParm As String = Chr(39) & Me.TextBox5.Text.ToString & Chr(39)
Dim connectionString As String = "Data Source=10.3.3.123; " & _
"Initial Catalog=HOPE; " & _
"Persist Security Info=True; " & _
"User ID=GOPDER; " & _
"Password=GOPDER"

Dim sSQL As String = "SELECT LINE_NBR, COMPANY, VAR_LEVELS, OBJ_ID, ACCT_UNIT, ACCOUNT, TRAN_AMOUNT FROM GLTRANS WHERE COMPANY = " & sParm & " AND FISCAL_YEAR = " & tParm & " AND ACCT_PERIOD = " & uParm & " AND CONTROL_GROUP = " & vParm & " AND POSTING_DATE = " & wParm & ";"
Dim oCn As New SqlConnection(connectionString)
Dim oCm As New SqlCommand(sSQL, oCn)
oCm.CommandType = CommandType.Text

Dim oDa As New SqlDataAdapter(oCm)
Dim oTable As New DataTable()

oCn.Open()
oDa.Fill(oTable)
Dim MyDataReader As SqlDataReader = oCm.ExecuteReader()
Dim iLvalue As Integer
Dim iRows As Integer = oTable.Rows.Count

Dim sParams(iRows, 12) As String
With MyDataReader
If .HasRows Then
While .Read()
sParams(iLvalue, 0) = .GetValue(0).ToString() 'Line Number
sParams(iLvalue, 1) = .GetValue(1).ToString() 'compamy
sParams(iLvalue, 2) = .GetValue(2).SubString(1, 3).ToString() 'var levels apr
sParams(iLvalue, 3) = .GetValue(2).Substring(4, 4).ToString() 'var levels activity
sParams(iLvalue, 4) = .GetValue(5).ToString() 'Account can be Five or Six Digit Numbers
'sParams(iLvalue, 5) = .GetValue(5).ToString().Substring(4, 2) 'Account Last Two of Account

' sParams(iLvalue, 5) = .GetValue(5).ToString().Substring(3, 2)
'End If
'If sParams(iLvalue, 4) = 6 Then
' sParams(iLvalue, 5) = .GetValue(5).ToString().Substring(4, 2)
'End If
If sParams(iLvalue, 5) Is Nothing Then
sParams(iLvalue, 5) = Microsoft.VisualBasic.Right(sParams(iLvalue, 4), 2)
End If

sParams(iLvalue, 6) = .GetValue(6).ToString
If sParams(iLvalue, 6) = .GetValue(6).ToString.StartsWith("-") Then
sParams(iLvalue, 6) = "0.00"

Else
sParams(iLvalue, 6) = .GetValue(6).ToString
End If

sParams(iLvalue, 7) = .GetValue(6).ToString

If sParams(iLvalue, 7) = .GetValue(6).ToString.StartsWith("-") Then
sParams(iLvalue, 7) = CDbl(.GetValue(6).ToString) * -1
Else
sParams(iLvalue, 7) = "0.00"
End If




iLvalue += 1
End While
End If
End With

oCn.Close()
oCn.Dispose()
iLvalue = 0
Dim SName As String = "TextBox.Text"
Dim i As Integer
For i = 0 To 12
Dim TName As String = SName & CStr(iLvalue) & CStr(i)


For iLvalue = 0 To iRows - 1
TName = sParams(iLvalue, 0)
TName = sParams(iLvalue, 1)
TName = sParams(iLvalue, 2)
TName = sParams(iLvalue, 3)
TName = sParams(iLvalue, 4)
TName = sParams(iLvalue, 5)
TName = sParams(iLvalue, 6)
TName = sParams(iLvalue, 7)
TName = sParams(iLvalue, 8)
TName = sParams(iLvalue, 9)
TName = sParams(iLvalue, 10)
TName = sParams(iLvalue, 11)


Next
Next
Return sParams
End Function
Private Sub BtnPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPull.Click
Me.Hide()
Dim AParams As Array
AParams = TestIt()
Dim IRows As Integer
Dim IPages, IMod, IForms, Sta, Fin, IPageCtr, INumRows As Integer
Dim BNext As Boolean = False
Dim BPrev As Boolean = False
IRows = AParams.GetUpperBound(0)
If IRows < 12 Then
IForms = 0
IMod = IRows
Else
IForms = CType(Math.Floor(IRows / 12), Int32)
IMod = IRows Mod 12
End If
If IMod = 0 Then
IPages = IForms
Else
IPages = IForms + 1
End If
For IPageCtr = 1 To IPages
If IPageCtr < IPages Then
BNext = True

Else
BNext = False
End If
If IPageCtr > 1 Then
BPrev = True
Else
BPrev = False
End If
Sta = (12 * IPageCtr) - 12
If IMod = 0 Then
INumRows = (IPageCtr * 12) - 1
Else
If IPageCtr = IPages Then
BNext = False
If IPages = 1 Then
INumRows = (IMod - 1)
Else
INumRows = (IMod - 1) + Sta
End If
Else
INumRows = (IPageCtr * 12) - 1
End If

End If

Fin = INumRows
Form2New(IPageCtr, AParams, Sta, Fin, BPrev, BNext, IPages) ' call subroutine Form9New and pass it info it needs

Next ' repeat the process until all pages are built

_g.bF2Vis = True ' set global variable to true
Me.TopMost = False ' Form1 not the topmost form
Me.Visible = False ' Form1 not visible
My.Application.OpenForms.Item("f1").Visible = True ' make Form f1 visible

End Sub

'Private Function LoadNewArray() As Array ' returns an array
' Dim myData(,) As String = {{}, {}} ' dimension a two dimensional string array
' myData = CType(GetData(), String(,)) ' call the function GetData() which will return an array into the variable myData
' Dim iUB0, iUB1 As Int32 ' dimension some integer variables
' iUB0 = myData.GetUpperBound(0) ' determine the number of rows in the array
' iUB1 = myData.GetUpperBound(1) + 2 ' determine the number of columns in the array and increase that number by two
' ReDim Preserve myData(iUB0, iUB1) ' redimsnsion the array , preserving the contents and adding 2 additional columns

' Return myData ' return tha array myData to the routine that called the function

'End Function

Private Sub Form2New(ByVal iPageCtr As Int32, ByVal sMyArray As Array, ByVal sta As Int32, ByVal fin As Int32, ByVal bPrev As Boolean, ByVal bNext As Boolean, ByVal iPages As Int32)

Dim frm As New Form2(Me, sMyArray, sta, fin, bPrev, bNext, iPages) ' instantiate a new copy of Form2 and pass it the info it needs
With frm
.Location = New Point(100, 100) ' define upper left hand corner of the form
.Name = "f" & iPageCtr ' name the form
.Text = "Page " & iPageCtr ' text in the title bar
.ControlBox = False ' no control box
.Show() ' open the form
.Visible = False ' make form invisible
End With

End Sub


Private Sub btnMenu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMenu.Click
Me.Close()
Main.Show()
Main.TopMost = True


End Sub
End Class
 
Sort by date Sort by votes
How about

With MyDataReader
If .HasRows Then
While .Read().......
else
Messagebox.show("error"...
exit sub
endif


I'd also put everything inside a try block and make sure the connecton gets closed in the finally block
 
Upvote 0 Downvote
Where in my coding should your coding be placed? Also do you not need an End While and End With in your coding?

 
Upvote 0 Downvote
Also, I have MyDataReader inside a Function so you can't end Sub within a function.

Dataman86
 
Upvote 0 Downvote
You already have the information you need in this line:

Dim iRows As Integer = oTable.Rows.Count

so:

Dim sParams(iRows, 12) As String

[red]If iRows > 0 Then[/red]

With MyDataReader
If .HasRows Then
While .Read()
sParams(iLvalue, 0) = .GetValue(0).ToString() 'Line Number
sParams(iLvalue, 1) = .GetValue(1).ToString() 'compamy
sParams(iLvalue, 2) = .GetValue(2).SubString(1, 3).ToString() 'var levels apr
sParams(iLvalue, 3) = .GetValue(2).Substring(4, 4).ToString() 'var levels activity
sParams(iLvalue, 4) = .GetValue(5).ToString() 'Account can be Five or Six Digit Numbers
'sParams(iLvalue, 5) = .GetValue(5).ToString().Substring(4, 2) 'Account Last Two of Account

' sParams(iLvalue, 5) = .GetValue(5).ToString().Substring(3, 2)
'End If
'If sParams(iLvalue, 4) = 6 Then
' sParams(iLvalue, 5) = .GetValue(5).ToString().Substring(4, 2)
'End If
If sParams(iLvalue, 5) Is Nothing Then
sParams(iLvalue, 5) = Microsoft.VisualBasic.Right(sParams(iLvalue, 4), 2)
End If

sParams(iLvalue, 6) = .GetValue(6).ToString
If sParams(iLvalue, 6) = .GetValue(6).ToString.StartsWith("-") Then
sParams(iLvalue, 6) = "0.00"

Else
sParams(iLvalue, 6) = .GetValue(6).ToString
End If

sParams(iLvalue, 7) = .GetValue(6).ToString

If sParams(iLvalue, 7) = .GetValue(6).ToString.StartsWith("-") Then
sParams(iLvalue, 7) = CDbl(.GetValue(6).ToString) * -1
Else
sParams(iLvalue, 7) = "0.00"
End If




iLvalue += 1
End While
End If
End With

oCn.Close()
oCn.Dispose()
iLvalue = 0
Dim SName As String = "TextBox.Text"
Dim i As Integer
For i = 0 To 12
Dim TName As String = SName & CStr(iLvalue) & CStr(i)


For iLvalue = 0 To iRows - 1
TName = sParams(iLvalue, 0)
TName = sParams(iLvalue, 1)
TName = sParams(iLvalue, 2)
TName = sParams(iLvalue, 3)
TName = sParams(iLvalue, 4)
TName = sParams(iLvalue, 5)
TName = sParams(iLvalue, 6)
TName = sParams(iLvalue, 7)
TName = sParams(iLvalue, 8)
TName = sParams(iLvalue, 9)
TName = sParams(iLvalue, 10)
TName = sParams(iLvalue, 11)


Next
Next
[red]
Else
MsgBox("No records found.")
End If[/red]
Return sParams
End Function


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

marty1944
  • Locked
  • Question
error code 380 when trying to run the visual basic v6 deployment wizard
Replies
1
Views
609
marty1944
marty1944
R_a_i_n_e_r_8
  • Locked
  • Question
Compile error: Types of intolerance (need help)
Replies
3
Views
632
strongm
strongm
sal21
  • Locked
  • Question
SIMULATE via code the message when i click on cerca
Replies
10
Views
691
strongm
strongm
Ken01
  • Locked
  • Question
Areas under graphs and overlap areas between circles. 2
Replies
3
Views
619
Ken01
Ken01
gmmastros
  • Locked
  • Question
VB6 Post to webservice with MSXML2 error
Replies
4
Views
937
dilettante
dilettante

Part and Inventory Search

Sponsor

Back
Top