My Procedure not have output in C# for Datasoure GridView

a h

10y1.5kMicrosoft Office

hi all,

I have a procedure:

ALTER Procedure [dbo].[Prc_TBMonPerfSecNBase_BlnForm](@P_Year varchar(4),@P_Month varchar(2))
As
Declare @Cols varchar(max),
@Sql varchar(max)
Select @Cols=Stuff((Select ',''''AS ' + quotename(Sec_Name)+',''''AS '+quotename(Sec_Code)
From Tb_Section
Where Sec_Base = 1
For xml path('')), 1, 1, '')
Set @Sql = 'Select Sec_Name,
Sec_Code,'
+@P_Year+'[???]'+
','
+@P_Month+'[???]'+
','
+ @Cols
+ 'From Tb_Section Where Sec_Base = 0 Order by Sec_Name'
exec (@Sql)

this procedure has output in Sql.But When this procedure set Datagridview.DataSource,DataGridView not have any row.

why?

please help me...

thanks

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

a h10y ago
It is detail and not important different between datagrid and gridview!!

The one who solved my problem anymore?
Shubham Kumar10y ago
be specific its datagrid not gridview both r different and second this what ru using windos or wpf
a h10y ago
I used of Reverse Engineering!!!.Result This:
1.procedure is:

ALTER Procedure [dbo].[test3] as

Declare @Cols varchar(max),

@Sql varchar(max)

Set @Cols=( '''''AS [2]');

print @cols

Set @sql=' select 1 as[one], ' + @Cols

exec (@Sql)
And Output in Sql server is:

And Output in C#:

But 2.I changed My Procedure:

ALTER Procedure [dbo].[test3] as

Declare @Cols varchar(max),

@Sql varchar(max)

Set @Cols=(Select '''''AS [2]');

print @cols

Set @sql=' select 1 as[one], ' + @Cols

exec (@Sql)
I have image(1) above in Sql server But image for Datagridview in C#:
a h10y ago
Dgv_MntPerSecNBase is name of DataGridView and this not have DataBind().

I used VS2013.While the I think the problem is elsewhere.
Shubham Kumar10y ago
what Dbc.Teeeest() returns its dataset or datatable

DataBaseClassesDataContext Dbc = new DataBaseClassesDataContext();
Dgv_MntPerSecNBase.DataSource = Dbc.Teeeest();
Dgv_MntPerSecNBase.DataBind();
a h10y ago
DataBaseClassesDataContext Dbc = new DataBaseClassesDataContext();
Dgv_MntPerSecNBase.DataSource = Dbc.Teeeest();

also in others forms not used procedure similar this Procedure.they are simple!
a h10y ago
I used DataClassesDataContext.also i used this method for other form and no any problem...
Ranjit Powar10y ago
Share datasource binding code of gridview.
Shubham Kumar10y ago
Ok i got it !!! and your proc give output on ssms so now check your dataset or datatable has lod data or not !! or just share the code
a h10y ago
1.When I used 'as' show error Incorrect Syntax near keyword 'as'.

2.@cols is varaible.For example now by Value in TB_Section Is:
''AS [Sec5],''AS [Sec7],''AS [Sec8],''AS [Sec9],''AS [Sec11]
and I want schema for above columns exactly:Sec5,Sec7,Sec8,Sec9,Sec11 and nothing else.

3.output in Sql server:
Shubham Kumar10y ago
just run the proc on sqlserver studio and check what output has come

Set @Sql = 'Select Sec_Name,'
+ @Cols
+ ' as Somename From Tb_Section Where Sec_Base = 0 Order by Sec_Name'
exec (@Sql)
a h10y ago
please see summarized codes of my procedure:
Declare @Cols varchar(max),
@Sql varchar(max)
Set @Cols=stuff((Select ',''''AS ' + quotename(Sec_Name)--+',''''AS '+quotename(Sec_Code)
From Tb_Section
Where Sec_Base = 1
For xml path('')), 1, 1, '');

Set @Sql = 'Select Sec_Name,'
+ @Cols
+ 'From Tb_Section Where Sec_Base = 0 Order by Sec_Name'
exec (@Sql)

------------------
In final select schema for Sec_Name is 'Sec_Name' and other schema for others columns I want set value in @Cols.
But Procedure above still no output for DataSource DataGridView.
Shubham Kumar10y ago
Because you do not define the column schema that is why your column come with no name

define schema like this

Set @Sql = 'Select Sec_Name,
Sec_Code ,'
+@P_Year+'[???]'+
','
+@P_Month+'[???]'+
'As columnName,'/////change made here
+ @Cols as OtherColumnName /////change made here
+ 'From Tb_Section Where Sec_Base = 0 Order by Sec_Name'