Skip to content
+1-888-319-3663

COMMUNITY FORUM

Return SQL Query results (multiple rows) to a message box

Sherry Whitten asked 4 years ago
I need an after map fails task that executes a sql query (Select * from ZRMTRXError) and displays all columns/all rows in a text/message box with an OK button.  
 
This code returns the first two columns of my sql query, one row at a time, requiring a click each time.  I want all columns, all rows displayed at once, in one text/message box.  What am I missing?  I feel like I’m so close.  Thanks in advance!
 
‘———————————– CONFIGURATION ————————————-
Dim server As String = “server”
Dim database As String = “db”
Dim user As String = “user”
Dim password As String = “password”
dim ErrorResults as String
 
 ‘Define the query command to be run
Dim execute As String = “select * from ZRMTrxError order by docnumbr, disttype, account”
Dim mapInterface As Integer = 1
‘—————————————————————————————
Try
    ‘Declare the connection string based on the configuration variables
    Dim conString As New String(“Data Source=” & server & “;Initial Catalog=” & database & “;User=” & user & “;Password=” & password & “;”)
    ‘Define the SQL connection and open it
    Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
    myConn.Open()
    ‘Declare the SQL command as the query string
    Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(execute, myConn)
    ‘Define the SQL Reader and execute it
     Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
 
       While sqlReader.Read()
           Messagebox.Show(String.Format(“{0}, {1}”, _
               sqlReader(0), sqlReader(1)))
end while
‘Close the reader
sqlReader.Close()
 ‘Close the connection
    myConn.Close()
Catch ex As Exception
    If (mapInterface = 1) Then
        Messagebox.Show(ex.Message, “ErrorsMsgBox Script Error”)
    End If
    Return False
End Try
Return True
Sherry Whitten replied 4 years ago

I refined it, and now I have a text box with all my rows, but I need column headings.

SNIPPET OF NEW CODE:
‘assign errors to global variable; insert hard return
While sqlReader.Read()
GBL_ERRORRESULTS = GBL_ERRORRESULTS & String.Format(“{0}, {1}”, _
sqlReader(0), sqlReader(1)) & Environment.NewLine
end while

‘messagebox.show(GBL_ERRORRESULTS)

‘Create a new textbox
Dim ErrorBox As TextBox = New TextBox

‘Allow it to span multiple lines and set the initial height and width
ErrorBox.Multiline = True
ErrorBox.Height = 260
ErrorBox.Width = 293

‘Add a scroll bar and set the text to the global variable holding the missing accounts
ErrorBox.ScrollBars = ScrollBars.Vertical
ErrorBox.Text = GBL_ERRORRESULTS

‘Create a new form
Dim form As New Form

‘Set the form name
form.Text = “Run Errors”

‘Add the text box and anchor it to each side of the form so it resizes with it
form.Controls.Add(ErrorBox)
form.Controls(0).Anchor = AnchorStyles.Top Or AnchorStyles.Bottom Or AnchorStyles.Left Or AnchorStyles.Right

‘Use the ShowDialog() method instead of just Show() so the form persists until the user closes it
form.ShowDialog()

‘Close the reader
sqlReader.Close()

‘Close the connection
myConn.Close()

Catch ex As Exception
If (mapInterface = 1) Then
Messagebox.Show(ex.Message, “ErrorsMsgBox Script Error”)
End If
Return False
End Try

Return True


If you would like to submit an answer or comment, please sign in to the eOne portal.