Solved

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

Posted on 2010-08-31
21
477 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now