如何使用存储过程插入数据 [英] how to insert data using stored procedure

问题描述

Hai All，



这是我的存储过程，

Hai All,

this is my stored procedure,

USE [Practise]
GO
/****** Object:  StoredProcedure [dbo].[empcrud]    Script Date: 04/11/2013 16:56:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[empcrud](
@Id int=0,
@Name nchar(10)=null,
@Sal int=0,
@City nchar(10)=null,
@Action varchar(10))
as
begin
set nocount on;
if (@Action='insert')
begin

insert emp(Id,Name,sal,city) values(@Id,@Name,@Sal,@City)
end
else if (@Action='update')
begin

update emp set Name=@Name,sal=@Sal,city=@City where Id=@Id
end
 else if (@Action='select')

begin
select * from emp
end
else if (@Action='delete')

begin
delete from emp where Id=@Id
end
End

asp.net：

asp.net:

{
            int id =Convert.ToInt32(txtid.Text);
            string name = Txtname.Text;
            int sal = Convert.ToInt32(Txtadrs.Text);
            string city = Txtdob.Text;
           // Procedure or function empcrud has too many arguments specified.
            SqlConnection con = new SqlConnection(strcon);
            cmd.Connection.Open();
            cmd.CommandText = "empcrud";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 50));
            cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@sal", SqlDbType.Int, 50));
            cmd.Parameters.Add(new SqlParameter("@city", SqlDbType.VarChar, 50));
            cmd.Parameters["@Action"].Value = "insert";
            cmd.Parameters["@Id"].Value = id;
            cmd.Parameters["@name"].Value = name;
            cmd.Parameters["@sal"].Value = sal;
            cmd.Parameters["@city"].Value = city;
            SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
            ds = new DataSet();
            sqlda.Fill(ds);
            con.Close();
          
          
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();
          //  getdata();

        }
 
    }

由于程序或函数empcrud指定的参数太多，我收到错误。



如何解决此错误。

I am getting error as Procedure or function empcrud has too many arguments specified.

how to resolve this error.

推荐答案

为什么使用SqlDataAdapter。因为您要向数据库插入值，所以您的代码应该是这样的： -

我只粘贴了最后几行代码。

Why are you using SqlDataAdapter. Because you are inserting values to database so your code should be like this :-
I have pasted only last lines of codes.

cmd.Parameters["@Action"].Value = "insert";
 cmd.Parameters["@Id"].Value = id;
 cmd.Parameters["@name"].Value = name;
 cmd.Parameters["@sal"].Value = sal;
 cmd.Parameters["@city"].Value = city;

 //Following  code is used when you are retrieving values from table.
 //SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
 //ds = new DataSet();
 //sqlda.Fill(ds);
 //con.Close();

 con.Open();
 cmd.ExecuteNonQuery();
 con.Close();
 //cmd.Connection.Close();

祝你好运。

Good luck.

这样使用可能会解决您的错误

use like this may be solve your error

{
            int id =Convert.ToInt32(txtid.Text);
            string name = Txtname.Text;
            int sal = Convert.ToInt32(Txtadrs.Text);
            string city = Txtdob.Text;
           // Procedure or function empcrud has too many arguments specified.
            SqlConnection con = new SqlConnection(strcon);
            cmd.Connection.Open();
            cmd.CommandText = "empcrud";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Action", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 50));
            cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@sal", SqlDbType.Int, 50));
            cmd.Parameters.Add(new SqlParameter("@city", SqlDbType.VarChar, 50));
            cmd.Parameters["@Action"].Value = "insert";
            cmd.Parameters["@Id"].Value = id;
            cmd.Parameters["@name"].Value = name;
            cmd.Parameters["@sal"].Value = sal;
            cmd.Parameters["@city"].Value = city;
            SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
            ds = new DataSet();
            sqlda.Fill(ds);
            con.Close();
          
          
            cmd.ExecuteNonQuery();
           cmd.Parameters.Clear();
            cmd.Connection.Close();
          //  getdata();

        }
 
    }

嗨..我认为你不需要为整数类型定义大小

Hi.. I don''t think you need to define size for Integer types

cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 50));

cmd.Parameters.Add(new SqlParameter("@sal", SqlDbType.Int, 50));

应该是

it should be

cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));

cmd.Parameters.Add(new SqlParameter("@sal", SqlDbType.Int));

这可能会清除你得到的错误。

This may clear the error you get.

这篇关于如何使用存储过程插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！