-2

Here is my code

                   cmd.Connection = con;
                   if (con.State == ConnectionState.Closed)
                   {
                       con.Open();
                   }
                   cmd.CommandText = "Drop View [Picklist]";
                   cmd.ExecuteNonQuery();

                   cmd.CommandText = "CREATE VIEW [Picklist] AS select A.desp_no,A.desp_date,A.custid,A.comp_name,A.ref_no,B.item_code as itemid,B.Pqty,C.serial_no,C.batchno,C.expiry,D.Item_name,D.Item_code,D.Mrp,E.cust_name,E.Address1,E.Address2,E.Address3,E.Phone,E.Email from dbo.Tbl_DespDet A inner join dbo.Tbl_Desp_Sub B on A.desp_no=B.desp_no inner join dbo.tbl_desp_barcode C on c.desp_no=A.desp_no inner join tbl_itemMast D on D.item_id=B.item_code inner join dbo.tbl_CustMast E on A.custid=E.cust_id  where A.desp_no=@despno";
                   cmd.Parameters.AddWithValue("@despno", beobj._Desno);
                   try 
                   {
                       cmd.ExecuteNonQuery();
                       return true;
                   }
                   catch(Exception ex)
                   {
                       return false;
                   }

the same create view statement(with just copy paste) works in Sql Server Management Studio.Some one Help me as i am stuck in this Fix.

update:

the name of view is given as [Picklist] for testing only.even i have tried with removind square brackets and found no difference.

12
  • 1
    Create parameterized VIEW in SQL Server Commented Mar 17, 2016 at 10:32
  • 2
    And I guess it won't in SSMS either. You probably set value hardcoded. Like CREATE VIEW [Picklist] AS ... where A.desp_no=10. So the correct way is to create table valued function and pass desp_no as parameter. So could we mark it as duplicate or your case is different? Commented Mar 17, 2016 at 10:33
  • but i have linked my report document with the view only.so its not as easy to change it to function. Commented Mar 17, 2016 at 10:41
  • There is no such thing as parametrized VIEW. Commented Mar 17, 2016 at 10:41
  • thanx.i will go with stored procedure itself.actually i have forgot the fact that view cannot be parameterized.even i have faced the same issue before. Commented Mar 17, 2016 at 10:43

1 Answer 1

0

Views cannot contain @parameters. What you could do is create a stored procedure (CREATE PROCEDURE ...) first, and then in C# code, set the name of the stored procedure as the cmd.CommandText. Or alternatively, remove the CREATE VIEW part altogether, and keep just the query itself as the content of the cmd.CommandText.

Sign up to request clarification or add additional context in comments.

1 Comment

thanx.i will go with stored procedure itself.actually i have forgot the fact that view cannot be parameterized.even i have faced the same issue before.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.