Please Help!!!!!Here I Am Using Stored Procedure For Bulk Insertion(Have To Insert 1.5 Million Data) But Stored Procedure Showing Error That No Such Parameter In Sp.But I Have..

Question

0.00/5 (No votes)

See more:

created table type alredy and how can i insert and display the whole data in grid?

here is my stored procedure-createtype_perdraw is my table type

SQL

CREATE PROCEDURE SP_PERDRAWT
@tblperdraw createtype_perdraw READONLY
 AS
 BEGIN
SET NOCOUNT ON
INSERT INTO mytable(name,address ,age)
SELECT * FROM @tblperdraw
END

here am passing sp and dt1 is my data table

C#

public void bulk_insert()
       {

           using (SqlConnection con = new SqlConnection(@"Dat=D:\MyPractice\browse_fie\browse_fie\browse.mdf;Integrated Security=True"))
           {
               using (SqlCommand cmd = new SqlCommand("SP_PERDRAWT"))
               {

                   cmd.CommandType = CommandType.StoredProcedure;

                   cmd.Connection = con;

                   cmd.Parameters.AddWithValue("@tblperdraw ", dt1);   // dt1 getting all values

                   con.Open();

                   cmd.ExecuteNonQuery();


                   con.Close();

               }

           }

       }

and am getting this error in bulk insert()

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: The procedure "SP_PERDRAWT" has no parameter named "@tblperdraw ".

pls help me

Posted 14-Jul-15 0:25am

Manidas Payyoli

Add a Solution

3 solutions

Solution 2

Remove the extra space after @tblperdraw
cmd.Parameters.AddWithValue("@tblperdraw " <-- here

Posted 14-Jul-15 0:35am

Kornfeld Eliyahu Peter

Solution 3

the problem is there is no such datatype as createtype_perdraw, you will need to pass the tablename with a valid datatype, prob. varchar is best

SQL

CREATE PROCEDURE SP_PERDRAWT
@tblperdraw varchar(10)

and then use that to build a SQL string that you execute in the stored procedure

SQL

DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT (name, address, age)
                 INTO mytable
                 FROM ' + @tblperdraw

EXEC sp_executesql @cmd

Posted 14-Jul-15 0:44am

TenmanS14

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-14T00:33:00

Solution 1

It looks like yuo have an extra space in the end of the variable name. Instead of

C#

cmd.Parameters.AddWithValue("@tblperdraw ", dt1);

try

C#

cmd.Parameters.AddWithValue("@tblperdraw", dt1);

Posted 14-Jul-15 0:33am

Wendelius

Comments

[no name] 14-Jul-15 7:02am

wow!!!!!!!!!!!!!!!! its working thanks!! I have another doubt yet. can I able to pass update and delete in this same stored procedure using flag?how

Wendelius 14-Jul-15 7:09am

You're welcome.

You can always add more parameters to your SP and based on the values of those parameters you can do whatever you like inside the procedure.

[no name] 14-Jul-15 7:20am

okay am I able to create rest of the things with if(@flag=''delete')?

Wendelius 14-Jul-15 7:35am

I'm not quite sure what you mean but you can add parameters to the stored procedure or you can create additional columns in your datatable if you want to flag each row separately.

I suggest posting a completely new question and describe the whole situation what you're after.