Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Visual Basic ( VB ) (http://www.go4expert.com/forums/visual-basic/)
-   -   Displaying a query help (http://www.go4expert.com/forums/displaying-query-help-t3532/)

CaJack 22Mar2007 03:10

Displaying a query help
 
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

DaWei 22Mar2007 08:53

Re: Displaying a query help
 
Quote:

it doesn’t work so good
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.


All times are GMT +5.5. The time now is 23:30.