Click here to Skip to main content
15,911,707 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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 ************
SQL
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


SQL
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)


********************************************************
now aspx.cs file coding..
C#
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();
        }
    }
}



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..???
Posted
Updated 22-Jul-12 20:29pm
v2

@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:
SQL
ALTER procedure [dbo].[selectstudent] 
begin 
select * from student
end


Hopefully It will work properly.
 
Share this answer
 
v2
Comments
pandya purvang 23-Jul-12 2:39am    
THanks.........its working
Kaushik Saha from Kolkata,India 23-Jul-12 2:53am    
most welcome.
You din't passed parameter for you stored procedure, that's what your error shows.
Try this:
C#
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();
 
        }


For details refer:
Stored Procedures[^]
and google[^]
 
Share this answer
 
Comments
pandya purvang 23-Jul-12 2:39am    
Thanks......
change your store procedure
SQL
ALTER procedure [dbo].[selectstudent] 
as
select * from student

Happy Coding!
:)
 
Share this answer
 
Comments
pandya purvang 23-Jul-12 2:39am    
Thanks really dear.........for your help its working.
Aarti Meswania 23-Jul-12 2:45am    
most welcome :)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900