Display SQL Query in Multi-Line Text Box VB.NET (2008)

I'm writing a basic VB 2008 application to report data from my SQL server. The requested layout needs to look similar to outlook - I have a DataGridView on the left to display "Message Titles" and on the right I have a large texbox. I would like to have the textbox display the results of a query (18 fields) one on top of the other. ie. if the query is:

SELECT        MR_ID, MR_RequestorName, MR_ItemNum, MR_Description,
                         MR_Quantity, MR_UM ,MR_Location, MR_Priority, MR_AreaType,  MR_AreaSide,       MR_ItemNum2, MR_Description2, MR_Quantity2, MR_UM2, MR_ItemNum3,
                         MR_Description3, MR_Quantity3, MR_UM3, MR_TimeStamp
WHERE       MR_ID = (I haven't finished this part yet - waiting for the result of this question)

Can I display the results in the textbox as:

MR_RequestorName
MR_ItemNum
MR_Description
MR_Quantity
MR_UM
MR_Location
MR_Priority
Etc...

Any help would be greatly appreciated.
cdicenteAsked:
Who is Participating?
 
ZhaolaiConnect With a Mentor Commented:
Try this:

Private Sub Populate_Results(ByVal dRow  As DataGridViewRow )

        Dim strText As New System.Text.StringBuilder
        strText.Append(dRow.Item("MR_RequestorName").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Location").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Priority").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaType").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaSide").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_TimeStamp").ToString)
        txtRequestView.Text = strText.ToString
    End Sub

Private Sub cdmSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdmSelect.Click
Dim row As DataGridViewRow = grdRequestList.SelectedRows(0)
    Call Populate_Results(row)
    End Sub

0
 
puru1981Commented:
you can call the string.Replace to do this

you need to replace the ' with linefeed.
0
 
ZhaolaiCommented:
Try this:

        Dim strSQL As String = "SELECT MR_ID, MR_RequestorName, MR_ItemNum, MR_Description, MR_Quantity, " _
                                & "MR_UM, MR_Location, MR_Priority, MR_AreaType, MR_AreaSide, MR_ItemNum2, " _
                                & "MR_Description2, MR_Quantity2, MR_UM2, MR_ItemNum3, MR_Description3, " _
                                & "MR_Quantity3, MR_UM3, MR_TimeStamp WHERE MR_ID = (I haven't finished this " _
                                & "part yet - waiting for the result of this question)"
        Dim strSub As String = strSQL.Replace("SELECT", "").Trim
        strSub = strSub.Substring(0, strSub.IndexOf("WHERE")).Trim
        Dim arrTemp() As String = strSub.Split(",")
        Dim strText As String
        strText = "SELECT" & vbTab
        Dim count As Integer
        For Each sItem As String In arrTemp
            count += 1
            If count = 1 Then
                strText &= sItem.Trim & "," & vbNewLine
            Else
                strText &= vbTab & sItem.Trim & "," & vbNewLine
            End If
        Next
        strText = strText.TrimEnd(",")
        strText &= strSQL.Replace(strSub, "").Replace("SELECT", "").Trim
        TextBox1.Text = strText

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
cdicenteAuthor Commented:
This is very close. However it is showing the SQL query as the string, I need the results as the string. I am vb6 programmer just starting to peek at .NET (yes, I know it is 2010). I normally use stored procedures to run SQL querys, but it looks as if I can run them directly from VB now with a dataset - do I need to replaced the "string" with a dataset command?
0
 
puru1981Commented:
hey hang on what are you trying to do?
0
 
cdicenteAuthor Commented:
I'm trying to run an SQL query(which i  also have created a dataset for) and have the results displayed one field on top of another in a muilt-line text box.
0
 
ZhaolaiCommented:
Add the following line to the top of your form class:

Imports System.Text

Add the following code to the event where you want to display the record (assume that your dataset is named as DataSet1):

        Dim dRow As DataRow = DataSet1.Tables(0).Rows(0)
        Dim strText As New StringBuilder
        strText.Append(dRow.Item("MR_RequestorName").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Location").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Priority").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaType").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaSide").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_TimeStamp").ToString)
        TextBox1.Text = strText.ToString

Open in new window

0
 
puru1981Connect With a Mentor Commented:
apply the solution advised by Zhaolai: if you have only one row.

if multple rows then do like this

 Dim strText As New StringBuilder
 For Each dRow As DataRow In dt.Rows
 strText.Append("New Record" & vbNewLine)
 strText.Append(dRow.Item("MR_RequestorName").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Location").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Priority").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaType").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaSide").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_TimeStamp").ToString)


Next

 TextBox1.Text = strText.ToString
0
 
Parth MalhanPrincipal EngineerCommented:
try the following code:

dim cmd as new sqlcommand
dim conn as new sqlconnection
dim re as sqldatareader

'Initialize the connection to conn as
'conn=new sqlconnection("connection string goes here...")

cmd=new sqlcommand("select col1,col2,col3,col4,col5,col6 from table1",conn)
re=cmd.executereader
dim i as integer
i=0
while re.read()
textbox1.text=re.getvalue(i)
textbox1.text=textbox1.text & vbnewline
i=i+1
end while

re.close()

0
 
cdicenteAuthor Commented:
Zhaolai, your solution is almost spot on. I apologize if this next part is amateur, but this sub is fired from a "Select" Button when a selected row in a datagrid is highlighted. the whole datagrid thing is new to me, I'm very used to vb6 and spread 7. Here is what I'm working with for related code. I just need something that will perform something similar to a "where dRow.Item("MR_ID") = grdRequestList.Rows(MR_ID). One other snafu may be that MR_ID is an Identity (Integer) so therefore cannot be converted to a string. I really appreciate all the help...

    Private Sub Populate_Results(ByVal intMR_ID As Integer)

        Dim dRow As DataRow = BOATTRACK_DV_MR_Location.Tables(0).Rows(0)
        Dim strText As New System.Text.StringBuilder
        strText.Append(dRow.Item("MR_RequestorName").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Location").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Priority").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaType").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaSide").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_TimeStamp").ToString)
        txtRequestView.Text = strText.ToString


    End Sub

    Private Sub cdmSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdmSelect.Click

        Dim intMR_ID As Integer

        intMR_ID = grdRequestList.Rows(MR_ID)

        Call Populate_Results(intMR_ID)
    End Sub
0
 
cdicenteAuthor Commented:
Please ignore my "conversion to string" comment above - I've resolved that part of the issue.
0
 
puru1981Commented:
ok in this context i think you already have the data in place so change your method like

Private Sub Populate_Results(ByVal dRow  As DataRow )

        Dim strText As New System.Text.StringBuilder
        strText.Append(dRow.Item("MR_RequestorName").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Location").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Priority").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaType").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_AreaSide").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM2").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_ItemNum3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Description3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_Quantity3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_UM3").ToString & vbNewLine)
        strText.Append(dRow.Item("MR_TimeStamp").ToString)
        txtRequestView.Text = strText.ToString
strText.Dispose()

    End Sub

Private Sub cdmSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdmSelect.Click
Dim row As GridViewRow = grdRequestList.SelectedRow
    Call Populate_Results(row)
    End Sub

0
 
cdicenteAuthor Commented:
Alright, a few (hopefully) final questions...

Dim row As GridViewRow = grdRequestList.SelectedRow - I got a mismatch here, so I changed this to:
Dim row As DataRow = grdRequestList.SelectedRows - correct?

however, once that is changed I get  the following:
Value of type 'System.Windows.Forms.DataGridViewSelectedRowCollection' cannot be converted to 'System.Data.DataRow'.

on grdRequestList.SelectedRows

is this because of the Integer field?

also, strText.Dispose() gets a "not a member" error - I'm nto familiar with dispose (not sure what the VB6 equivalent was).

Thanks again everyone.
0
 
cdicenteAuthor Commented:
Very close to a solution here

Dim row As DataGridViewRow = grdRequestList.SelectedRows(0)

throws "ArgumentOutOfRangeException was unhandled:
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
0
 
ZhaolaiCommented:
Private Sub cdmSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdmSelect.Click
   If grdRequestList.SelectedRows.Count > 0 Then
       Dim row As DataGridViewRow = grdRequestList.SelectedRows(0)
        Call Populate_Results(row)
   End If
End Sub

0
 
ZhaolaiCommented:
Basically, one of the rows has to be selected in order to display the record.
Or event simpler, Remove the Select button. Display the record when one of the rows is clicked, like this:

Private Sub grdRequestList_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdRequestList.CellClick
       Dim row As DataGridViewRow = grdRequestList.SelectedRows(0)
        Call Populate_Results(row)
 End Sub
0
 
ZhaolaiCommented:
A better way:

Private Sub grdRequestList_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdRequestList.CellClick
        Call Populate_Results(grdRequestList.CurrentRow)
 End Sub
0
 
cdicenteAuthor Commented:
That is exactly what I was looking for! the only compile issue I run into now is dRow.Item -  It appears to not be a member of datagridviewrows, I've tried a few of the other suggested members, but I'm just getting data, not the actual contents.
strText.Append(dRow.Item("MR_RequestorName").ToString & vbNewLine)
0
 
puru1981Commented:
dRow.Item("MR_RequestorName").ToString -> dRow("MR_RequestorName").ToString
0
 
ZhaolaiCommented:
strText.Append(dRow.Cells("MR_RequestorName").FormattedValue.ToString & vbNewLine)

make similar changes to all the other fields.
0
 
cdicenteAuthor Commented:
I can't thank you enough! fantastic work! As I can most definitely tell, I'll be spending some time pawing through some Upgrading VB6 to VB.NET books!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.