0

Below is an update to your question: This is from the module. I took all this over after someone quit and its been a bit of a mess.

Public Function MPCS_SELECT_SQL(ByVal strSQL As String, Optional ByRef readerObj As OleDbDataReader = Nothing) As OleDbDataReader 'ADODB.Recordset
    Try


        If OPEN_CONNECTIONS() = False Then
            MessageBox.Show("Error connecting to database.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            MPCS_SELECT_SQL = Nothing
            Exit Function
        Else


            If Not readerObj Is Nothing Then
                If readerObj.IsClosed - False Then readerObj.Close()
            End If
            Dim cmdMPCS As OleDbCommand = New OleDbCommand(strSQL, conMPCS)

            MPCS_SELECT_SQL = cmdMPCS.ExecuteReader()
            cmdMPCS.Dispose()

        End If

    Catch ex As Exception
        MessageBox.Show(ex.ToString & "     " & strSQL)
        Stop
        Return Nothing
    End Try

End Function
Lee
  • 95
  • 2
  • 9
  • The ListView is (still) ill-suited for data displays; the checkbox is associated with the ListViewItem, so it is always and forever in column1. Use a DGV and you can put the columns in any order you want. – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 16:06
  • ...all that code to populate the rows and columns and set the alternating backcolor could be reduced to `dgvProducts.DataSource = mySimpleDataTable`. As a bonus, things like quantity and price (numeric values!) wont be converted to text – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 16:14
  • not much to learn for the default behavior: the one line I showed creates all the columns and shows all the rows. Each column knows what the datatype of the data is; edits to the DGV will automagically flow back into the DT – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 18:29
  • I'm not using a datatable or OleDbDataAdapter so I'm not sure how to make it work. – Lee Mar 31 '16 at 19:08
  • How are you getting the data from the DB now; what do you do with that SQL? Why are you creating a DGV instead of adding it in the IDE designer? – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 19:18
  • I did add it to the desiger. Everything is done in the vb module with OleDbDataReader's. I assumed my only problem now was figuring out how to fill a datatable since I didn't use a dataadapter. – Lee Mar 31 '16 at 19:25
  • `Dim dgvStock As New DataGridView` creates a new control; you dont need that. `myDataTable,Load(cmd.ExecuteReader)` will fill the DT, you dont have to have a DataAdapter, but it would probably make sense in the long run. then `dgvNameOnForm.DataSource = myDataTable` – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 19:27
  • I guess in my case then it would just be `DataGridView1.DataSource = rsMPCS` But that doesn't work – Lee Mar 31 '16 at 19:40
  • I have no idea what `MPCS_SELECT_SQL` is or what it returns. does the rs mean it returns an old fashioned RecordSet? – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 19:42
  • Update the post above. You are correct. – Lee Mar 31 '16 at 19:46
  • `myDataTable.Load(rsMPCS .ExecuteReader)` it might work even though you have already executed the reader in that funky sub. – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 19:51
  • `ExecuteReader' is not a member of 'OleDbDataReader'` – Lee Mar 31 '16 at 19:56
  • Oops. Sorry - it would be the OleDBCOmmand object which you threw away in that method. That thing is of no value. Not only are you not closing and disposing of things as you should (your app will leak). but queries tend to be specialized that they dont lend themselves to helper things like that. – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 19:59
  • I got it `myDataTable.Load(rsMPCS)` Now I just gotta figure out how to work with the column arranges and am I able to do checkboxes and things I originally was looking for so someone can add to cart? Is there a good way with DataGrid to do something like that. – Lee Mar 31 '16 at 20:01
  • The column order is based on the SQL, so you can often just change that. To add "virtual" fields, add them to the SQL: `...Picked As Boolean`. You can also add columns manually in the IDE and gobs and gobs of properties to control the display, display order, color, format etc – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 20:04
  • Can you tell me where I am not disposing things so I can learn from that? If you have time. Thanks again for all your help. – Lee Mar 31 '16 at 20:55
  • everything and anything which has a `Dispose` method means that it allocates some sort of resource under the hood and should be Disposed when you are done with it. I did not see your `cmdMPCS.Dispose()` - the more common way is `Using` blocks: http://stackoverflow.com/a/29187199/1070452 – Ňɏssa Pøngjǣrdenlarp Mar 31 '16 at 21:18

0 Answers0