用存储过程选择 [英] select with store procedure

问题描述

我是存储过程的新手，所以如果有很小的错误，也可以帮助我解决它...
我已经使用存储过程进行了Select，add，update，delete编程……编码看起来像.....
*********存储过程************

i am very new to store procedure so if there is small error then also help me to solve it......
i have doing Select,add,update,delete programing with the use of store procedure ... the coding is look like.....
*********store procedure ************

ALTER procedure [dbo].[selectstudent] @firstname varchar(50),@lastname varchar(50),@gender varchar(50),@address varchar(50),@phone varchar(50),@dob datetime,@email varchar(50)
as
select * from student

ALTER procedure [dbo].[insertstudent]
@firstname varchar(50),@lastname varchar(50),@gender varchar(50),@address varchar(50),@phone varchar(50),@dob datetime,@email varchar(50)
as
Insert into Student( firstname, lastname, gender, address, phone, dob, email)
values (@firstname, @lastname, @gender, @address, @phone, @dob, @email)

****************************************************** ******
现在aspx.cs文件编码..

********************************************************
now aspx.cs file coding..

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace storeprocedure_demo
{
    public partial class _Default : System.Web.UI.Page
    {
        SqlConnection cn = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            cn = new SqlConnection(@"Data Source=PURVPARI-PC\SQLEXPRESS;Initial Catalog=demomydb;Integrated Security=True ");
            if (!Page.IsPostBack)
            {

                fillgrid();
            }

        }
        protected void fillgrid()
        {
            SqlCommand cm = new SqlCommand("selectstudent", cn);
            cm.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cm;
            DataSet ds = new DataSet();
            cn.Open();
            da.Fill(ds, "student");
            cn.Close();
            gvstudent.DataSource = ds;
            gvstudent.DataBind();

        }

        protected void btnsave_Click(object sender, EventArgs e)
        {
            cn.Open();
            SqlCommand cm = new SqlCommand("insertstudent", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.Add("@firstname", SqlDbType.VarChar).Value = txtfirstname.Text;
            cm.Parameters.Add("@lastname", SqlDbType.VarChar).Value = txtlastname.Text;
            cm.Parameters.Add("@gender", SqlDbType.VarChar).Value = txtgender.Text;
            cm.Parameters.Add("@address", SqlDbType.VarChar).Value = txtaddress.Text;
            cm.Parameters.Add("@phone", SqlDbType.VarChar).Value = txtphone.Text;
            cm.Parameters.Add("@dob", SqlDbType.DateTime).Value = Convert.ToDateTime(txtdob.Text);
            cm.Parameters.Add("@email", SqlDbType.VarChar).Value = txtemail.Text;
         
            cm.ExecuteNonQuery();
            cn.Close();
            fillgrid();
        }
    }
}

现在在此编码中insertstudent存储过程可以完美地工作了，我已经成功地将数据插入到表中了....但是我在selectstudent过程中遇到了问题..它显示了类似...的错误. 过程或函数"selectstudent"需要未提供的参数"@firstname" ....
那么解决方案是什么..???

now in this coding insertstudent store procedure is perfectly working i have sxuccesfully insert data into table....but i have problem with selectstudent procedure....its shows error like...
Procedure or function ''selectstudent'' expects parameter ''@firstname'', which was not supplied....
so what is the solution..???

推荐答案

更改存储过程

change your store procedure

ALTER procedure [dbo].[selectstudent] 
as
select * from student

祝您编码愉快！
:)

Happy Coding!
:)

您没有为存储过程传递参数，这就是您的错误显示的内容.
试试这个:

You din''t passed parameter for you stored procedure, that''s what your error shows.
Try this:

protected void fillgrid()
        {
            SqlCommand cm = new SqlCommand("selectstudent", cn);
            cm.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@firstname", SqlDbType.VarChar).Value =   
            txtFirstName.Text;//Your text box of first name
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cm;
            DataSet ds = new DataSet();
            cn.Open();
            da.Fill(ds, "student");
            cn.Close();
            gvstudent.DataSource = ds;
            gvstudent.DataBind();
 
        }

有关详细信息，请参考:
存储过程 [ google [ ^ ]

For details refer:
Stored Procedures[^]
and google[^]

@firstname varchar(50)，@ lastname varchar(50)，@ gender varchar(50)，@ address varchar(50)，@ phone varchar(50)，@ dob datetime，@ email varchar(50)

这不是必需的，因为这是参数位置.
因此将其删除.

您所需的存储过程为:

@firstname varchar(50),@lastname varchar(50),@gender varchar(50),@address varchar(50),@phone varchar(50),@dob datetime,@email varchar(50)

This is not required because this is the argument position.
So remove it.

Your Desired storedprocedure is:

ALTER procedure [dbo].[selectstudent] 
begin 
select * from student
end

希望它会正常工作.

Hopefully It will work properly.

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