Stored procedure for listbox items

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.

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

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.


 

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_list",strColumns));
 
  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


 
 

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 'Name,shiftname,machine,line,plantname'



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_list",strColumns.Substring(1));
            param1.Value = strColumns.Substring(1);
            cmd1.Parameters.Add(param1);
            SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
            DataSet ds1 = new DataSet();
            da1.Fill(ds1,strColumns.Substring(1));
            BindingSource bsProperties = new BindingSource();
            bsProperties.DataSource = ds1.Tables[0];
            dataGridView1.DataSource = bsProperties;
         
            }

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

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.

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.........


Thanx,This wiil definatly solve your problem.

Pravin.