DataGridView Manipulation

[m.davide]

I was told to create a new thread by kevininstructor

Public Sub queryCity()
        Dim cmd2 As New OracleCommand
        conn.Open()

        cmd2.Connection = conn
        cmd2.CommandText = "select e.employer_name, e.pen, e.contact_no, b.street1, b.street2, e.employer_status, e.mapping_status from employer e, building b, block k, map m where e.building_id = b.building_id and b.block_id = k.block_id and k.map_id = m.map_id and k.city = '" & employerReports.categoryResults_cb.Text & "'"
        'MsgBox(cmd2.CommandText)
        cmd2.CommandType = CommandType.Text
        Dim dr As OracleDataReader = cmd2.ExecuteReader
        Dim dt As New DataTable
        dt.Load(dr)
        employerReports.DataGridView1.DataSource = dt
        conn.Close()
    End Sub

here's the output, dunno if this will work. i can't upload it to any imagehosting site. our office restricts em.


i need a way to screen each employer status and mapping status before placing them in the DGV. I need to convert the numerical values to their corresponding status. 1 = Good, 2 = Non-Reporting, 3 = Under-Remitting and 4= Non-Remitting(which i already know how to do). I just don't know how to get the values being placed on the DGV. I would love to do this on oracle but i don't know how to.

thank you!

[jmcilhinney]

You should populate DataTables with those values from the database and then bind those DataTables to combo box columns in the grid. If you don;t want the user to be able to edit the cells in those columns then configure the columns to be read-only and not display a drop-down button. To learn how to add a combo box column to a grid, follow the CodeBank link in my signature and read my thread on the subject.

[m.davide]

thanks! but sadly its too complicated for me. T_T

can you point me somewhere or better, post a tutorial on how to add rows and columns manually.

something like column 1 row 1 = "id", column 2 row 1 = "name" etc..

[jmcilhinney]

That CodeBank thread does add columns manually, or at least a column, and there's no reason to add rows manually because you are binding data. I would suggest that you forget your own project for the moment and just step through the instructions provided in the CodeBank fully and see that code in action. If there is anything that you don't understand, ask a question there or here. Once you understand it all, then you can replace the parts that correspond to what you already have in your current project. Finally, you can add the extra configuration to the combobox columns if required.

[m.davide]

ok tnx. gonna try it now.

[m.davide]

Hi I read the codebank entry you pointed out. I only used stage 1 because it already served my purpose since I already have a function that converts the numerical values of my estat and mstat to responding strings. (im still working on using parameters on my sql statements)

Here goes:

Public Sub queryAll()
        Dim cmd1, cmd2 As New OracleCommand
        Dim queryAllCT As String
        Dim counter As Integer
        conn.Open()

        cmd1.Connection = conn
        cmd2.Connection = conn
        cmd1.CommandText = "select  count(e.employer_id) from employer e, building b, block k, map m where e.building_id = b.building_id and b.block_id = k.block_id and k.map_id = m.map_id and m.map_name != 'MAP ZERO'"
        cmd1.CommandType = CommandType.Text
        cmd1.ExecuteReader()
        Dim dr0 As OracleDataReader = cmd1.ExecuteReader
        dr0.Read()
        counter = dr0.Item("count(e.employer_id)")
        dr0.Close()
        queryAllCT = "select  e.employer_id as NO, e.employer_name as EMPLOYER, e.pen as PEN, e.contact_person||' - '||e.contact_no as CONTACT, m.map_name as MAP, b.building_no as BUILDING, b.street1||' - '||b.street2 as STREET, e.employer_status as ""E-STATUS"", e.mapping_status as ""M-STATUS"", e.main_office as ""MAIN BRANCH"" from employer e, building b, block k, map m where e.building_id = b.building_id and b.block_id = k.block_id and k.map_id = m.map_id"
        cmd2.CommandText = queryAllCT & eStatusString & mStatusString & " and m.map_name != 'MAP ZERO' order by b.building_no"
        cmd2.CommandType = CommandType.Text
        Dim dr As OracleDataReader = cmd2.ExecuteReader
        employerReports.BindingSource1.DataSource = fillEmployerReport(dr, counter)
        employerReports.DataGridView1.DataSource = employerReports.BindingSource1
        conn.Close()
    End Sub

Public Function fillEmployerReport(ByVal oreader As OracleDataReader, ByVal ocounter As Integer) As DataTable
        Dim table As New DataTable
        displayRowsReturns(ocounter)
        With table.Columns
            .Add("NO", GetType(Integer))
            .Add("EMPLOYER", GetType(String))
            .Add("PEN", GetType(String))
            .Add("CONTACT", GetType(String))
            .Add("MAP", GetType(String))
            .Add("BUILDING NO", GetType(String))
            .Add("STREET", GetType(String))
            .Add("E-STATUS", GetType(String))
            .Add("M-STATUS", GetType(String))
            .Add("MAIN BRANCH", GetType(String))
        End With

        With table.Rows
            While ocounter > 0
                oreader.Read()
                .Add(oreader.Item("NO"), oreader.Item("EMPLOYER"), oreader.Item("PEN"), oreader.Item("CONTACT"), oreader.Item("MAP"), oreader.Item("BUILDING"), oreader.Item("STREET"), getEStatus(oreader.Item("E-STATUS")), getMStatus(oreader.Item("M-STATUS")), oreader.Item("MAIN BRANCH"))
                ocounter = ocounter - 1
            End While
            oreader.Close()
        End With
        Return table
    End Function

Public Function getEStatus(ByVal estat As Integer)
        Dim estatus As String = ""
        Select Case (estat)
            Case 1
                estatus = "Good"
            Case 2
                estatus = "Non-Reporting"
            Case 3
                estatus = "Under-Remitting"
            Case 4
                estatus = "Non-Remitting"
        End Select

        Return estatus
    End Function

    Public Function getMStatus(ByVal mstat As Integer)
        Dim mstatus As String = ""
        Select Case (mstat)
            Case 0
                mstatus = "GD"
            Case 1
                mstatus = "NA"
            Case 2
                mstatus = "CR"
            Case 3
                mstatus = "IN"
            Case 4
                mstatus = "SE"
            Case 5
                mstatus = "MB"
        End Select
        Return mstatus
    End Function

Here's the result:

[jmcilhinney]

Where's the result?

[m.davide]

lol fail. used a gmail link.

i cant upload to any image hosting site. damn office firewall.

[m.davide]

hope this works

[m.davide]

http://postimage.org/image/3jq7j1zxd/

only site i can find

[jmcilhinney]

You don't need to upload to any image hosting site. Just attach your image directly to your post. That way we can see it without having to leave this page, never mind this site.

[m.davide]