Procedure and Codebehind Problem

[monika_vasvani]

Hi all I have created one procedure: CREATE PROCEDURE [dbo].[dynamic_insert](@table varchar(50),@parameter nvarchar(4000)) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; set @opration='insert into '+@table+'(+@testTable+) values(+ @parameter +)' end My front end code is: com.Parameters("@table").Value = "order1" com.Parameters.Add("@parameter", SqlDbType.NVarChar) com.Parameters("@parameter").Value = c2.cust_id & "," & c2.item_id & "," & c2.cust_items & "," & c2.item_name c2 is my class object. My problem is:when i assign values in parameter field,it takes as string. So data is not inserted. example: c2.cust_id = 1 c2.item_id = 1 c2.cust_items = "cd" c2.item_name = "pen" com.Parameters("@parameter").Value ="1,1,cd,pen" It's not possible to insert string. what i do?is there any another datatype? or another way? Thanks monika

[Scott Dorman]

I have created one procedure:

CREATE PROCEDURE [dbo].[dynamic_insert](@table varchar(50),@parameter nvarchar(4000))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @opration='insert into '+@table+'(+@testTable+) values(+ @parameter +)'
END

My front end code is:

com.Parameters("@table").Value = "order1"
com.Parameters.Add("@parameter", SqlDbType.NVarChar)
com.Parameters("@parameter").Value = c2.cust_id & "," & c2.item_id & "," & c2.cust_items & "," & c2.item_name

c2 is my class object. My problem is when I assign values in parameter field, it takes as string. So data is not inserted. example:

c2.cust_id = 1
c2.item_id = 1
c2.cust_items = "cd"
c2.item_name = "pen"
com.Parameters("@parameter").Value ="1,1,cd,pen"

It's not possible to insert string. what i do? is there any another datatype? or another way?

Scott Dorman

Microsoft® MVP - Visual C# | MCPD President - Tampa Bay IASA [Blog][Articles][Forum Guidelines]

---

Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai

[Scott Dorman]

You can't execute dynamic SQL like this. I believe you would need to call the "EXEC" function on the generated SQL. What is the actual SQL statement you want executed? Where does the @testTable parameter come from? Why are you not just creating a single stored procedure to handle inserts into the table and simply calling that, passing the appropriate parameters as discrete values?

Scott Dorman

Microsoft® MVP - Visual C# | MCPD President - Tampa Bay IASA [Blog][Articles][Forum Guidelines]

---

Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai