Datagrid Issues

1)  Create a connection - that will be the same as you use at the moment.

2)  Create a dataset - this will be different from what you do at the moment

    Private ds As New DataSet

Alternatively, if you only have one table, you can just create a datatable

    Private dt As New DataTable

3)  Create an SQL query string selecting the data that you want from the database - that will be the same as you use at the moment for your datareader

4)  Create a dataadapter - this will be different from what you do at the moment

    Private da As SqlDataAdapter
    da = New SqlDataAdapter(<yourSQLQueryString>, <yourConnection>)

That gives the dataadapter the necessary SELECT command.

5)  Use a command builder to generate the necessary update commands for the dataadapter

    Dim cb As New SqlCommandBuilder(da)

6)  Ready to roll!  Get the data from the database

    da.Fill(ds, <yourtablename>)

That will put all the data you have requested into the dataset in a datatable with the name you give.  Or, if you work with a freestanding datatable.  Not, there is no need explicitly to open and close the connection: dataadapters do that automatically.

    da.Fill(dt)

7)  Bind the datagrid to the datatable

    yourDataGrid.DataSource = ds.Tables(<yourtablename>)

or

    yourDataGrid.DataSource = dt

8)  Edit, add and delete records using the datagrid

9)  Update the database

    da.Update(ds, <yourtablename>)

or

    da.Update(dt)

and then, to make sure that the database and your dataset/datatable are in synch

    ds.AcceptChanges

or

    dt.AcceptChanges.

That's it.  I had better warn you that this very basic set of instructions can go wrong (e.g. if for some reason the command builder cannot find a Primary Key for the table that needs updating) but I suggest you try it and come back if there are any problems with the specific details of those: error message/s, line/s on which they are reported, and so on.

As to the Money question, as I'm not sure what you've tried I'm not sure what to suggest.  Have you looked at this, for instance

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskHandlingErrorsWithWindowsFormsDataGridControl.asp

Roger

>>
6)  Ready to roll!  Get the data from the database

    da.Fill(ds, <yourtablename>)

That will put all the data you have requested into the dataset in a datatable with the name you give.  Or, if you work with a freestanding datatable.  Not, there is no need explicitly to open and close the connection: dataadapters do that automatically.

    da.Fill(dt)
<<

That bit got a bit garbled.  It should have read

>>
6)  Ready to roll!  Get the data from the database

    da.Fill(ds, <yourtablename>)

That will put all the data you have requested into the dataset in a datatable with the name you give.  Or, if you work with a freestanding datatable ....  

    da.Fill(dt)

Note, there is no need explicitly to open and close the connection: dataadapters do that automatically.
<<

Roger

Ok - I managed to get the grid populated with your help - Thanks

Now I have another question now.

One of the fields i select is defined in the database as a bit .. so it shows up in the grid as a checkbox .. i need it to show "Yes" if it is true and "No" if it is false (this is a read only field in my grid)

Now  i also want to do the following.

If the bit field is "NO" -> That means the row is still editible, if it is yes .. the row is not editible.

If the row is not editible .. the entire row must be red.
If it is editible then the entire row must be gray except for the 2 Editible fields must be green so i can indicate the editible rows

Any help on that?

On the checkbox/yes/no issue, it sounds like you need either to restructure your SQL query so it returns "Yes" or "No" rather than the bit value, or add an Expression Column to your datatable and map your datagrid column to that.  In both cases, I think you would need to use SQL's IIF(test,valueIfTrue,valueIfFalse) function.  Have a look at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskCodeCreatingExpressionColumnVisualBasic.asp

and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataColumnClassExpressionTopic.asp

On coloring the rows, so far as I know, the only way to do it is with a technique like this

http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q745q

That only deals with coloring a specific cell, but you could apply it to all cells on the row concerned.

Or, if you are in VB.NET 2005 - but I would expect, if that were the case, you would be referring to DataGridView, rather than DataGrid - here's another approach (iboutchkine's post)

https://www.experts-exchange.com/questions/21679697/Changing-row-colors-in-DataGridView-in-VB-NET-2005.html

Roger

I am using 2005 and using the DataGridView..
Is there a big difference between the 2?

Ok, I got working what i needed to ..

The save is not working though
i Keep getting this error when it runs this function :  da.Update(dt)

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

??

Thanks - That worked.

        Dim SvrKeys(0) As DataColumn
        SvrKeys(0) = dt.Columns(7)
        dt.PrimaryKey = SvrKeys

this also seemed to do the trick :)

Yes, it would.  The reason I suggested the first (at least first) is that (if it works) it makes sure that the PK is the same as that in the DB.  Doing it the second way could produce anomalies.  But it was on my list of other things to try if my first suggestion failed ;-)

Thanks for the points.

Roger