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

you can call the string.Replace to do this

you need to replace the ' with linefeed.

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

Select allOpen in new window

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?

hey hang on what are you trying to do?

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.

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

Select allOpen in new window

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()

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

Please ignore my "conversion to string" comment above - I've resolved that part of the issue.

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

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.

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

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

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

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

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)

dRow.Item("MR_RequestorName").ToString -> dRow("MR_RequestorName").ToString

strText.Append(dRow.Cells("MR_RequestorName").FormattedValue.ToString & vbNewLine)

make similar changes to all the other fields.

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!