crystal report parameterized views using C#.net

Question

I want to create a crystal report which will be based on user input. User will give some inputs and on the basis of such input query will be performed and report will be displayed. I have created view like: CREATE VIEW first_view AS SELECT Book_id ISBN_no Tittle Author Publisher Page Countryuser1 FROM book_information My coding is: string connstring = Data Source=localhost; Initial Catalog=Book_Shop; Integrated Security=True; SqlConnection conn = new SqlConnection(connstring); try { CrystalReport_First_User_Book objRpt = new CrystalReport_First_User_Book(); string selectstring = select Book_idISBN_noTittleAuthorPublisherPageCountry from first_view where user1=@user1; SqlCommand comm = new SqlCommand(selectstring conn); SqlDataAdapter adapter = new SqlDataAdapter(selectstring conn); comm.Parameters.Add(new SqlParameter(@user1 userselect)); conn.Close(); conn.Open(); comm.ExecuteNonQuery(); DataSet ds = new DataSet(); adapter.Fill(ds first_view); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show(No Data Found); } objRpt.SetDataSource(ds); //CrystalDecisions.CrystalReports.Engine.TextObject root; //root = (CrystalDecisions.CrystalReports.Engine.TextObject) //objRpt.ReportDefinition.ReportObjects[txtHeader]; //root.Text = Sample Report With Parameter!!; crystalReportViewer1.ReportSource = objRpt; } catch (SystemException exc) { MessageBox.Show(exc.Message); } finally { conn.Close(); } But message is displayed when I run the program like: Must declare @user1 What should I do? Regards Shomen

Shomen Deb · Answer

yes you are right. But my problem is I couldn't be able to set parameter. I mean to say I want a query like that select Book_id ISBN_no Tittle from xyz where userid=@userid. This is not working for VIEWS in sql server 2000. The value of @userid will be sent by user input (combo box or any other windows form controls). Then in the coding section I'm writing like this: string selectstring = selectBook_idISBN_noTittle from first_view where user1=@user1; comm.Parameters.AddwithValue(@user1userselect); Rest of the coding r same what u told. How can I solve the problem?

Rajendra Deopura · Answer

Dear I think there are few mistakes in the code that I have figure out : 1. You have called comm.ExecuteNonQuery( ). This method is to be fired against InsertUpdateDelete and not against the Select command. 2. Instead of using comm.Parameters.Add( ) go with comm.Parameters.AddWithValue( ) 3. In this code you are using both the connected and disconnected approach together to fill the dataset. Use only one at a time. If you want to fill a dataset go with disconnected approach. In Disconnected approach no need to build the Sqlconnection and Sqlcommand object. Here is the code string constr =your connection string; string cmdstr=your command string; SqlDataAdapter da=new SqlDataAdapter(cmdstrconstr); DataSet ds=new DataSet(); da.fill(dsany name); DataTable dt=ds.Tables[any name]; Now you youuse this DataTable object dt. Thanks Happy Coding