none
DISPLAY TO TEXTBOX VB.NET RRS feed

  • Question

  • HI, FRIEND
    I WANT TO ASK HOW TO DISPLAY CODING THIS,
    INSIDE TEXTBOX VB.NET

    CODE AS FOLLOWS

      'Command = New SqlCommand ("SELECT no FROM resep_n", kon)
             Command = New SqlCommand ("SELECT distinct STUFF ((SELECT ',' + cast (No as varchar (10)) FROM prescription_n where status = 'false' FOR XML PATH (''), TYPE) .value ('.', 'VARCHAR (MAX)'), 1,1, SPACE (0)) FROM resep_n ", kon)
             Search = Command.ExecuteReader
             Search.Read ()
             If Search.HasRows Then
                
                 TextBox5.Text = Search ("No")
             Else
                 TextBox5.Text = ""
             End If


    THANK YOU
    Tuesday, June 6, 2017 3:49 AM

All replies

  • Which one, the outcommented or the other one. 

    However, tell what you want, not show some code which you knitted (you show it because the outcommented row) where you could not go further because it failed and then how to solve that. We cannot, we don't have your database. 


    Success
    Cor

    Tuesday, June 6, 2017 5:33 AM
  • If the first commented SELECT worked, then make sure that the second statement contains the “No” output column too, maybe ‘SELECT DISTINCT STUFF(…) AS No FROM …’.

    Tuesday, June 6, 2017 7:47 AM
  • Here is a working example for how to work with FOR XML PATH, there is no WHERE yet one can be added. Important thing to know, I used a XmlDocument because there is an issue using SqlClient data provider which truncates return data at 2,033 or characters.

    In the following class has one function to get the data. There are several properties for setting up the connection string. You could setup the connection in the function, I did not as this is an extract from a MSDN code sample I wrote.

    Imports System.Data.SqlClient
    Imports System.Xml
    
    Public Class Operations
        ''' <summary>
        ''' Replace with your SQL Server name
        ''' </summary>
        Private Server As String = "KARENS-PC"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "CheckedListBoxDatabase"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        Public Function GetResults() As String
            Dim fields As String = "P.FirstName,P.LastName"
            Dim result As String = ""
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = $"SELECT ( SELECT {fields} " & "FROM dbo.Person AS P FOR XML PATH('Person') , TYPE) " & "FOR XML PATH('') , ROOT('Persons')"
    
                    cn.Open()
                    Dim reader As XmlReader = cmd.ExecuteXmlReader()
                    Dim doc As New XmlDocument()
                    doc.Load(reader)
                    'Create an XML declaration. 
                    Dim xmldecl As XmlDeclaration
                    xmldecl = doc.CreateXmlDeclaration("1.0", Nothing, Nothing)
                    xmldecl.Standalone = "yes"
    
                    'Add the new node to the document.
                    Dim root As XmlElement = doc.DocumentElement
                    doc.InsertBefore(xmldecl, root)
                    result = doc.OuterXml
                End Using
            End Using
    
            Return result
    
        End Function
    End Class

    Form code (kept simple)

    Public Class DemoForm
        Private Sub DemoForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New Operations
            TextBox1.Text = ops.GetResults
        End Sub
    End Class

    You can test this at design time by creating a .sql file in your project by adding a new text file and changing the extension from .txt to .sql.

    e.g.

    Click on the link in the result pane

    Now let's look at the results which is not formated as a TextBox does not know how to format xml.

    For formating use a WebBrowser

    Public Class DemoForm
        Private Sub DemoForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New Operations
            WebBrowser1.DocumentText = ops.GetResults
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, June 6, 2017 9:20 AM
    Moderator