Displaying a query help

Discussion in 'Visual Basic ( VB )' started by CaJack, Mar 21, 2007.

  1. CaJack

    CaJack New Member

    Joined:
    Mar 21, 2007
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    0
    Hi, I’m having trouble with my code and I’m starting to get lost. The Program takes the country that’s been typed in to the textbox and when the query button is clicked the program queries an access database for all records that contain the selected country. I think that part works, but I’m having trouble with the output. I want to display only certain fields of the record in a multiline text box, each matching record to a new line, like this…
    Bob, UK, 99946, red
    Joe, UK, 87777, Blue
    Mary, UK, 48565, green

    The database name is TestDB, the table is InfoTable and the fields are Ename, Region, Enumber, Ecolour. Here’s the code…

    Code:
     Imports System.IO
    Imports System.Data
    Imports System.Data.OleDb
    Public Class Test
        Dim myString As String
    
        Private Sub Test_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
        End Sub
    
    
        Private Sub butQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butQuery.Click
            myString = txtRegion.Text.ToString()
            Dim cs As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VB\Test\TestDB.mdb"
            Dim cn As New OleDb.OleDbConnection(cs)
            Dim dt As New DataTable
            Dim dSet As New DataSet
            Dim sqlString As String
    
            sqlString = "SELECT Ename, Enumber, Ecolour FROM CMSDB WHERE Region = '" & myString & "'"
            Dim cmd As New OleDb.OleDbCommand(sqlString, cn)
            cmd.Parameters.Add("@myString", OleDb.OleDbType.VarChar, 30).Value = myString
            Dim da As New OleDb.OleDbDataAdapter(cmd)
    da.Fill(dt)
    
        End Sub
    
    End Class
    Now thats the code and as far as I know it works ok, but when I try the code for the output to the multiline textbox it doesn’t work so good. Here’s the code… (this code goes right under the da.Fill(dt) above…

    Code:
     
    
            For Each dRow As DataRow In dSet.Tables("InfoTable").Rows
                For Each dCol As DataColumn In dRow
                    txtQuery.Text = txtQuery.Text & " " & dCol.ToString()
                Next dCol
    
                txtQuery.Text = txtQuery.Text & vbCrLf
            Next dRow 

    If anyone can help me get the code working that would be great.
    I also seem to get this error “Expression is of type 'System.Data.DataRow', which is not a collection type.” The error pops up on dRow from this line…
    Code:
     For Each dCol As DataColumn In dRow 
    Could someone help me get the output code working?
    Cheers,
    Jack
     
  2. DaWei

    DaWei New Member

    Joined:
    Dec 6, 2006
    Messages:
    835
    Likes Received:
    5
    Trophy Points:
    0
    Occupation:
    Semi-retired EE
    Location:
    Texan now in Central NY
    Home Page:
    http://www.daweidesigns.com
    Great explanation for the error associated with the first question. It makes me want to get out a magnifying glass and bust my *** looking.

    On the other hand, your statements regarding the second error are good: you give the precise error text. The only problem is that you only "seem" to get the error. Do you or don't you? I would suggest that you look at your post from the viewpoint of a potential helper who is not sitting in your chair, losing sleep and pulling their hair out.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice