Filling a datagrid with varying columns

[johnc83]

Hi all. Hoping someone can help me with this please...

My stored procedure is like this:

Select Product, (Quantity * Price) As TotalPrice
From tblProducts

Could someone show me how to get a datagridview to populate with the results of this query..

if this was a normal query I would just drag the fields from the table on to my grid and then do a normal fill but of course this 'TotalPrice' column does not really exist.

How do I get this TotalPrice column to display with correct result?

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[RiverGuy]

Well, let's assume you have created a Connection object, a Command object, a DataAdapter and a DataTable. Let's also assumed that you've set them up and have used your DataAdapter to fill your DataTable from your stored procedure. Put a brand new DataGridView onto your form. Then, in your code, after you have filled up your DataTable, set the .DataSource of your DataGridView to your DataTable.

 

[Qik3Coder]

John, this should be possible even with the drag and drop stuff. you should be able to click on the little green arrow and get a column preview and you should be able to add a column with an expression in it. If this basic functionality isn't there I would be surprized.

-Sometimes the answer to your question is the hack that works

 

[johnc83]

Hi ppl, thanks for the replies and apologies for the limited information.

After reading some other posts I think I need to un-simplify my request as it may not actually be possible. Here is my actual query..

SELECT     a.MethodCode, COALESCE (b.RECustPrice, a.REListPrice) AS Price
FROM         tblMethods AS a LEFT OUTER JOIN
                      tblCustPrices AS b ON a.MethodCode = b.MethodCode 
WHERE     (a.MethodCode = @MethodCode)

So this outputs to 2 columns (MethodCode and "Price")

How do I populate a datatable with the results from this query and then show it on the datagridview?

Riverguy - I have everything ready except for the datatable and datagridview so the connection object etc.. is there.

As you can see I don't understand how all this works just yet but I'm sure that if I can get this working my understanding will be a lot better!

Thanks again

John



.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[RiverGuy]

John,
How are you getting this data to your application, or what is your plan? Are you using DataSet(s) (strongly typed or not), just DataTables, DataReaders, or what?

The default behavior of a DataGridView is that when you set a data source, it will dynamically create columns based on what exists in the data source.

 

[johnc83]

I am using datasets (not sure what strongly typed means, i'll take a look). When doing something like this I generally drag an item from the table in the dataset onto a form which then creates the tableadapter and bindingsource. I then fill/update etc.. using these.

Hope that helps.

Thanks for your time, really appreciate it..

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[RiverGuy]

OK, then you're using the designer to create the objects. Are you dragging them from the DataSources tab? You should be able to drag your stored procedures on the form as well, provided you've selected to show them in the DataSources tab.

 

[johnc83]

Hi, I've never chosen to show my SP's in the DataSources tab because I didn't see the benefit.

I do know though and it is working perfectly!

Thank you so much for your help.

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express