Retreive Data from sql server along with headers or column name
-
hello everyone, i am using vb.net2003 ( for a windows application ) along with sql server 2005 i need to display data of a sql procedure whose columns are being generated dynamically. The problem arise when i want to give header of flexgrid as i am not able to find out how many sql column will be retrieved/generated after the execution of the procedure and what their headers will be (i.e column name as i am using pivot to generate column from a temp table in which n number of distinct values can appear as rows, which will be used in pivot for which n number of column fields will be generated accordingly ). As i have to give header of grid and number of column is unknown , i am struck here...Plz help
ashish sharma
-
hello everyone, i am using vb.net2003 ( for a windows application ) along with sql server 2005 i need to display data of a sql procedure whose columns are being generated dynamically. The problem arise when i want to give header of flexgrid as i am not able to find out how many sql column will be retrieved/generated after the execution of the procedure and what their headers will be (i.e column name as i am using pivot to generate column from a temp table in which n number of distinct values can appear as rows, which will be used in pivot for which n number of column fields will be generated accordingly ). As i have to give header of grid and number of column is unknown , i am struck here...Plz help
ashish sharma
The short answer is if the columns are being generated dynamically and you don't know what columns will be there, guess what neither will .Net be able to work this out! However if you return the results of the stored procedure into a Datatable you will be able to iterate through the Columns collection -> clickety[^]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
hello everyone, i am using vb.net2003 ( for a windows application ) along with sql server 2005 i need to display data of a sql procedure whose columns are being generated dynamically. The problem arise when i want to give header of flexgrid as i am not able to find out how many sql column will be retrieved/generated after the execution of the procedure and what their headers will be (i.e column name as i am using pivot to generate column from a temp table in which n number of distinct values can appear as rows, which will be used in pivot for which n number of column fields will be generated accordingly ). As i have to give header of grid and number of column is unknown , i am struck here...Plz help
ashish sharma
Please don't crosspost, and don't repeat a question without additional information. The FlexGrid is part of Visual Studio 6. It should die along with VB6. In VB.NET, one would use the DataGridView. Once you fetch the data, you can get the schema information from the datareader.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
hello everyone, i am using vb.net2003 ( for a windows application ) along with sql server 2005 i need to display data of a sql procedure whose columns are being generated dynamically. The problem arise when i want to give header of flexgrid as i am not able to find out how many sql column will be retrieved/generated after the execution of the procedure and what their headers will be (i.e column name as i am using pivot to generate column from a temp table in which n number of distinct values can appear as rows, which will be used in pivot for which n number of column fields will be generated accordingly ). As i have to give header of grid and number of column is unknown , i am struck here...Plz help
ashish sharma
If you have a temporary table you can use
select * from tempdb.sys.columns where object_id = object_id('tempdb..#yourtable');
you can create a header. From here there are a couple of ways to handle the data and not being very good at VB.NET I will let you handle that.
-
The short answer is if the columns are being generated dynamically and you don't know what columns will be there, guess what neither will .Net be able to work this out! However if you return the results of the stored procedure into a Datatable you will be able to iterate through the Columns collection -> clickety[^]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
GuyThiebaut wrote:
, guess what neither will .Net be able to work this out!
Most modern databases provide a way to query for most or even all of the meta data associated with the database. SQL Server provides a way to query for table column names.
The OP says: "i need to display data of a sql procedure whose columns are being generated dynamically" Because the columns are being generated "dynamically" there is no way to retrieve the meta data relating to the SP before the SP is run. Also meta data relating to SPs pretty much only extends to getting the input parameters.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
The OP says: "i need to display data of a sql procedure whose columns are being generated dynamically" Because the columns are being generated "dynamically" there is no way to retrieve the meta data relating to the SP before the SP is run. Also meta data relating to SPs pretty much only extends to getting the input parameters.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
GuyThiebaut wrote:
"i need to display data of a sql procedure whose columns are being generated dynamically"
Misread that. I thought they were using tables which had been created by some runtime process.
You've got a good point there - as there is some ambiguity in the word dynamic. I just answered the question based on how I dynamically generate columns in SPs - so yes,. if the columns were generated through the .net code or based on some meta data in a table it would be possible to know information regarding the columns before they were generated.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens