Stored procedure for listbox items

Question

Hi i have developed one windows form application in that i have two list boxesin 1st list box iam binding column headings of table by writing stored procedure. i have add and remove buttons if i click on add the item from 1st listbox should be added to 2nd list boxif i click remove it should removed from 2nd list box. now my problem is in 2nd list box suppose i have 2 items means i have to write stored procedure for fetching data from database for those two column names and bind to datagrid. if i have 3 or 4 items in 2nd listbox again i have to write stored procedure for fetching 4 column values. here for this problem i have to write only one stored procedure for fetching number of column values data depending upon number of items in 2nd list box how to do write stored procedure for this please help me regarding this. Thanks in advance.

Pravin More · Answer

hi kavya..... what i understood about your problem is you want data of only those columns which are in 2nd ListBox....hope i am right... If so then take 1 varchar parameter to your sproc which will be comma seperated list of current column names of 2nd list then i guess you have some select query in sproc for fetching data....then just attach this parameter in select command so that your sproc return data for dynamic number of columns..... somthing like this............... create processure Col_values @col_list varchar(max) as Begin @query varchar(max); Set @query='Select '+@col_list+' from Table_name '; exec(@query) end but pass comma seperated list of column names....you create it in string variable in code behind...by for each or somthing..... Thanxhope it will solve your problem. Pravin.

Ashwini · Answer

somewhere recursive calls happening in the code what i did is just i dropped that stored procedure and again i recreated it and same stored procedure i called in my codethen its worked fine. Thanks for your help also really you gave me good ideas to do it. With Regards Kavya

Pravin More · Answer

Hi kavya you most welcome ....n its good that u got solution at end.......what was last problem of recursion? what was reason? Thanx Pravin.

Ashwini · Answer

Hi pravin really great thanks for you it is working fine. i like your response for my problems great thanks for you. With Regards Kavya

Ashwini · Answer

k pravin i will try

Pravin More · Answer

recursive calls from somewhere are going on ....checkout where its from..................try 1 thing drop procedure n recreate it with i pasted in above post...

Pravin More · Answer

Hi kavya It happens when there is many nested call in your procedure or endless loop of calls may be because of tables triggers...........checkout your database/table other properties...our code is fine both c# and sproc because i have created same sproc on my machine and it worked fine........ this is sproc that i was created ........recrete same at your machin......... create PROCEDURE [dbo].[prodatalogsep] @col_list varchar(max) AS BEgin declare @query varchar(max) Set @query='Select '+@col_list+' from datalogging1'; exec(@query) END Check create table script of that datalogging1 table.....is there any trigger over there....... i hope you will solve problem. Thanxs Pravin.

Ashwini · Answer

Hi pravin i have replaced my code and after debugging its showing error like Maximum stored procedure function trigger or view nesting level exceeded (limit 32). what's the problem i am not getting.

Pravin More · Answer

Hi kavya done with it...its working properly now......now dont touch sproc its correct... just replace button click code with below code......... private void button1_Click(object sender EventArgs e) { string strColumns = ; foreach (string str in listbox2.Items) { if (strColumns == ) { strColumns = str; } else strColumns = strColumns +  + str; } SqlConnection con1 = new SqlConnection(data source=hobvision03;initial catalog=product;user id=sa;password=hobvision); SqlCommand cmd1 = new SqlCommand(dbo.prodatalogsep con1); cmd1.CommandType = CommandType.StoredProcedure; cmd1.Parameters.Add(new SqlParameter(@col_liststrColumns)); SqlDataAdapter da1 = new SqlDataAdapter(cmd1); DataSet ds1 = new DataSet(); da1.Fill(ds1); BindingSource bsProperties = new BindingSource(); bsProperties.DataSource = ds1.Tables[0]; dataGridView1.DataSource = bsProperties; } It will work now ....... Thanx Pravin

Ashwini · Answer

Hi pravin my stored procedure is like this alter procedure dbo.prodatalogsep(@col_list varchar(max)) as Begin declare @query varchar(max) Set @query='Select '+@col_list+' from datalogging1'; exec(@query) end EXEC dbo.prodatalogsep 'Nameshiftnamemachinelineplantname' and my c# code is as shown below private void button1_Click(object sender EventArgs e) { string strColumns = ; foreach (string str in listbox2.Items) strColumns = strColumns +   + str; SqlConnection con1 = new SqlConnection(data source=hobvision03;initial catalog=product;user id=sa;password=hobvision); SqlCommand cmd1 = new SqlCommand(); cmd1.Connection = con1; cmd1.CommandText = dbo.prodatalogsep; cmd1.CommandType = CommandType.StoredProcedure; SqlParameter param1 = new SqlParameter(@col_liststrColumns.Substring(1)); param1.Value = strColumns.Substring(1); cmd1.Parameters.Add(param1); SqlDataAdapter da1 = new SqlDataAdapter(cmd1); DataSet ds1 = new DataSet(); da1.Fill(ds1strColumns.Substring(1)); BindingSource bsProperties = new BindingSource(); bsProperties.DataSource = ds1.Tables[0]; dataGridView1.DataSource = bsProperties; }

Pravin More · Answer

Hi Kavya i am not able to download attachment there is erroe on page.......can you please paste your main C# code and sproc code...... Thanx Pravin

Ashwini · Answer

Hi praveen i have tried according to your suggestion in the previoue replay iam passing listbox values as camma separated column names and i am binding data to the gridview control. but i am getting one error like Maximum stored procedure function trigger or view nesting level exceeded (limit 32). please give me some ideas to solve this problem. i have attached my example code in the attached file please find the attachment if any mistakes in the code means suggest me how to do it. thanks for your kind replay.

Pravin More · Answer

Hi Kavya u mean u done with sproc part and now u want to generate columnname string to pass parameter (i.e comma seperated string of col names) so its simple now....i have attached simple project please download it and run .it will show you how generate that comma seperated string of colm names from list box......... ThanxThis wiil definatly solve your problem. Pravin.

Ashwini · Answer

hi pravin great thanks for you its working fine i am very happy for your replay. Now i wanted to pass the 2nd listbox items(column names) as parameters to the stored procedure using windows form application and display generated data in gridview please give me some idea to do it. thanks in advance.

Pravin More · Answer

ok sure kavya.....

Ashwini · Answer

k pravin i will try this if any doubts i will come back you again thanks for your replay