VB.Net and MS Access

Dear All,

I am using an OLEDbDataAdapter to conect to a table in a MS Access database, I am filling the data into a DataSet, and I am also using a DataGrid control to display results. Everything works well (using SQL statement with all fields selected, e.g: SELECT * FROM table1) and all the data are displayed in the DataGrid. However, when I want to choose few fields (e.g. SELECT a, b, c FROM Table1) then the problem I am facing is that the DataGrid displays the fields "a,b and c" but at the same time the other fields (with null as their values). I know that I should be clearing the DataGrid or the DataAdapter (which I am doing by having DataSet.Clear()).

Any Idea for the New Year.

Cheers.
LVL 1
khaledcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rfgkevCommented:
If you've used the visual studio wizard to create your dataset it will be expecting data for the other columns aswell and put in null values by default. You could either manually create the dataset at runtime or modify it so that it only contains the fields you require
khaledcAuthor Commented:
How can I do that (create or modify)?
rfgkevCommented:
to create dynamically

add "Imports System.Data.OleDb" to the start of the code

    Private Function Execute(ByVal SelectStatement As String) As DataSet
        Dim command As OleDbCommand
        Dim data As New DataSet
        Dim adapter As OleDbDataAdapter

        Try
            command = New OleDbCommand(SelectStatement)
            command.CommandType = CommandType.Text
            command.Connection = New OleDbConnection("connectionstring")
            command.Connection.Open()

            adapter = New OleDbDataAdapter(command)
            adapter.Fill(data)

            Return data
        Catch ex As Exception
            Throw
        Finally
            If Not command Is Nothing Then
                If Not command.Connection Is Nothing Then command.Connection.Close()
                command.Dispose()
            End If
        End Try
    End Function

then put your connection string in that function and pass it a SELECT sql statement and it will return you a dataset.

for a VS generated dataset, double click on it's .xsd file and you can modify it's structure from there

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
khaledcAuthor Commented:
It is somewhere near what I want.

Cheers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.