Fill datagridview from access

Question

4.00/5 (2 votes)

See more:

Hi to all

I'm try to create a add,delete,update datagridview
I ave create a datagridview1 and a have add 2 textboxcolumns and 1 comboboxcolumns

I use this code to fill the datagrid

VB

Dim Con As OleDbConnection
Dim adapter As New OleDbDataAdapter
Dim dataSet As New DataSet
Dim sqlCmd As OleDbCommand = New OleDbCommand("Select line_id, line_code, line_descr from line order by line_id")
Con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source = C:\Documents and Settings\Roidoulis\Επιφάνεια εργασίας\Roller_Database\bin\Debug\Roller_old.mdb")
Try
    Con.Open()
    sqlCmd.Connection = Con
    adapter.SelectCommand = sqlCmd
    adapter.Fill(dataSet, "lalakis")
    With DataGridView1
        .DataSource = dataSet
        .DataMember = "lalakis"
    End With
Catch 'ex As OledbException
End Try
adapter.Dispose()
Con.Close()
Con.Dispose()

One of my problem is......
The datagrid leave my 3 columns empty and add 3 new columns with header the name from my access table.
Question 1 ) How i can make datagridview write in my first three columns and not create 3 new one????????
If i change me code to this

VB

With DataGridView1
    .AutoGenerateColumns = False

the datagriv add 6 empty rows (6 is the record in my file)

After this i delete my own columns and i use this to change the header text

VB

.Columns(0).HeaderText = "ID"
.Columns(1).HeaderText = "Code"
.Columns(2).HeaderText = "Descr"

Question 2 ) Please help me to change the second column to combobox

Thanks and regards.

If you see something wrong please infor me.......

Posted 18-Apr-11 21:32pm

Vagelisr

Updated 18-Apr-11 21:50pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wayne Gaylard · Answer 1 · 2011-04-18T22:32:00

Personaly I think you are going about this the wrong way. I would create a class LineItem with three properties LineID, LineCode (which would be an Enum) and LineDescription. This Class would have a shared Function which would return a list of all current line items from the Database. Something like this:

VB

Enum LineCode

    Code1
    Code2
    Code3

End Enum

Class LineItem

    Public Property LineID As Integer
    Public Property LineCode As LineCode
    Public Property LineDescription As String

    Public Shared Function GetCurrentLineItems() As List(Of LineItem)

        Dim lineItems As List(Of LineItem) = New List(Of LineItem)
        Using con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source = D:\Documents\LineItems.mdb")
            Dim cmd As New OleDb.OleDbCommand("SELECT LineID, LineCode, LineDescription FROM LineItems Order By LineID", con)
            con.Open()
            Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader()
            While dr.Read()
                Dim newLine As New LineItem()
                newLine.LineID = dr.GetInt32(0)
                newLine.LineCode = dr.GetInt32(1)
                newLine.LineDescription = dr.GetString(2)
                lineItems.Add(newLine)
            End While
            dr.Close()
        End Using
        Return lineItems

    End Function

End Class

On my Form I would manually set up the DataGridView with the three relevant columns and I would have a private field in my Form class that would hold a List(Of LineItem). You can populate this in the Forms Load event calling the GetCurrentLineItems method. Then you can either bind to the list of LineItems or manually enter each row (which I personally prefer) like this

VB

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        currentLineItems = LineItem.GetCurrentLineItems()
        colCode.DataSource = [Enum].GetValues(GetType(LineCode))
        colCode.DataPropertyName = "LineCode"
        For Each lineItem As LineItem In currentLineItems
            Dim dgRow As New DataGridViewRow()
            dgRow.CreateCells(DataGridView1)
            dgRow.Cells(0).Value = lineItem.LineID
            dgRow.Cells(1).Value = lineItem.LineCode
            dgRow.Cells(2).Value = lineItem.LineDescription
            DataGridView1.Rows.Add(dgRow)
        Next
    End Sub

where colCode is my ComboBox column

Hope this helps