Solved

Datagrid Issues

Posted on 2006-06-19
9
243 Views
Last Modified: 2012-05-05
Hi,

I have never actually worked with Datasets ...
I always use the sqlConnection , sqlCommand, and the sqlDataReader ,

I have a project where i have to read most of the columns i have in a database into a datagrid.
Make all the columns red except for 2 columns which must be green.
The Red Columns must be read only and the green the user can change.

1 of the green columns is a (currency) field .. so the user must only be able to put in Money values 32.12 for example.
and the other column is a Bit field ... which shows a Checkbox.

As i said , i've never worked with datasets, so i actually got it to show the columns from the database by actually adding row by row .. (reading from a datareader).
I got it to colour the rows and only make those 2 NOT read only.

The problems i am having now is

1).  When i put in a non Money value ... Like  231afd.12..  and i leave the cell - it complains ... and i have tried putting error handling everywhere which i think is necessary ..

2).  Saving - I must loop through the entire grid and apply the changes ...  where i hear in a dataset you can just call an update command that will do everything ? (i don't know how to loop through the datagrid to make it save....)


Please help me fix these 2 problems or maybe you can help me to make it load the grid from the database using a dataset (a quick crash course) :) . So the saving can be easy.

Ciao
Pierre
0
Comment
Question by:PierreBeukes
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 16932786
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16932806
>>
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
0
 
LVL 3

Author Comment

by:PierreBeukes
ID: 16932967
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?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16933152
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)

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21679697.html

Roger
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 3

Author Comment

by:PierreBeukes
ID: 16933257
I am using 2005 and using the DataGridView..
Is there a big difference between the 2?
0
 
LVL 3

Author Comment

by:PierreBeukes
ID: 16933409
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.

??
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 16933775
>>
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
<<

As I said, "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)".

Does your database table have a Primary Key?  Assuming that it does, try adding this line

     da.MissingSchemaAction = MissingSchemaAction.AddWithKey

immediately after

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

Roger
0
 
LVL 3

Author Comment

by:PierreBeukes
ID: 16933882
Thanks - That worked.

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

this also seemed to do the trick :)
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16933966
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now