Solved

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

Posted on 2010-08-31
21
499 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:cdicente
  • 8
  • 7
  • 5
  • +1
21 Comments
 
LVL 9

Expert Comment

by:puru1981
ID: 33567451
you can call the string.Replace to do this

you need to replace the ' with linefeed.
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33567454
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
 

Author Comment

by:cdicente
ID: 33570654
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 9

Expert Comment

by:puru1981
ID: 33570773
hey hang on what are you trying to do?
0
 

Author Comment

by:cdicente
ID: 33570792
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33573447
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
 
LVL 9

Assisted Solution

by:puru1981
puru1981 earned 100 total points
ID: 33573716
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
 
LVL 4

Expert Comment

by:parthmalhan
ID: 33574090
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
 

Author Comment

by:cdicente
ID: 33575641
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
 

Author Comment

by:cdicente
ID: 33576264
Please ignore my "conversion to string" comment above - I've resolved that part of the issue.
0
 
LVL 9

Expert Comment

by:puru1981
ID: 33576424
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
 

Author Comment

by:cdicente
ID: 33576758
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
 
LVL 17

Accepted Solution

by:
Zhaolai earned 400 total points
ID: 33576805
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
 

Author Comment

by:cdicente
ID: 33577159
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33577220
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33577262
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
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33577281
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
 

Author Comment

by:cdicente
ID: 33577467
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
 
LVL 9

Expert Comment

by:puru1981
ID: 33577499
dRow.Item("MR_RequestorName").ToString -> dRow("MR_RequestorName").ToString
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33577540
strText.Append(dRow.Cells("MR_RequestorName").FormattedValue.ToString & vbNewLine)

make similar changes to all the other fields.
0
 

Author Comment

by:cdicente
ID: 33577579
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlServer no dupes 25 35
ASP/VB email question 4 35
How to open form using item in Listbox. 8 21
How to duplicate form in Visual Studio 2015 2 17
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question