Solved

Datagrid Issues

Posted on 2006-06-19
9
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

622 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