Click here to Skip to main content
15,898,987 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

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 :)
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......
@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.

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