Solved

Datagrid Issues

Posted on 2006-06-19
9
251 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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