Click here to Skip to main content
12,760,377 members (32,602 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# stored-procedure
Hello Friends,
This is my, stored Proceedure code

CREATE PROCEDURE str_insert_register 
@username nvarchar(50),
@firstname nvarchar(50),
@lastname nvarchar(50),
@fullname nvarchar(50),
@city nvarchar(50),
@street nvarchar(50),
@postcode nvarchar(50),
@telephone nvarchar(50), 
@dob nvarchar(50),
@occupation nvarchar(50),
@emailid nvarchar(50),
@pwd nvarchar(50),
@abtsite nvarchar(50),
@entrydate datetime,
@activestatus int
AS
BEGIN
SET NOCOUNT ON 
declare @id as int
SET @username=@firstname+right('0'+convert(varchar,datepart(dd,getdate())) ,2)
insert into register(id,username,firstname,lastname,fullname,city,street,postcode,telephone,dob,occupation,emailid,pwd,adtsite,entrydate,activestatus)values(@id,@username,@firstname,@lastname,@fullname,@city,@street,@postcode,@telephone,@dob,@occupation,@emailid,@pwd,@abtsite,@entrydate,@activestatus)
declare @stid as int
insert into login(stid,username,emailid,pwd)values(@stid,@username,@emailid,@pwd)
END 
GO


to execute, it, I have written C# code which is this...

protected void btn_submit_Click(object sender, EventArgs e)
    {
        try
        {
            cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
        SqlCommand cm = new SqlCommand("register",cnn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.CommandText = "str_insert_register";
        cm.Parameters.Add("title",SqlDbType.NVarChar,50).Value = ddl_title.SelectedItem.ToString();
        cm.Parameters.Add("firstname", SqlDbType.NVarChar, 50).Value=txt_fname.Text;
         cm.Parameters.Add("lastname", SqlDbType.NVarChar, 50).Value=txt_lname.Text;
         cm.Parameters.Add("fullname", SqlDbType.NVarChar, 50).Value=txt_fname.Text +" "+txt_lname.Text;
         cm.Parameters.Add("city", SqlDbType.NVarChar, 50).Value=txt_city.Text;
         cm.Parameters.Add("street", SqlDbType.NVarChar, 50).Value=txt_street.Text;
         cm.Parameters.Add("postcode", SqlDbType.NVarChar, 50).Value=txt_postcode.Text;   
         cm.Parameters.Add("telephone", SqlDbType.NVarChar, 50).Value=txt_telephone.Text;
         cm.Parameters.Add("dob", SqlDbType.NVarChar, 50).Value=txt_dob.Text;
         cm.Parameters.Add("occupation", SqlDbType.NVarChar, 50).Value=ddl_occup.SelectedItem.ToString();
         cm.Parameters.Add("emailid", SqlDbType.NVarChar, 50).Value = txt_email.Text;
        cm.Parameters.Add("pwd",SqlDbType.NVarChar,50).Value=txt_pwd.Text;
        cm.Parameters.Add("abtsite", SqlDbType.NVarChar, 50).Value = ddl_abtsite.SelectedItem.ToString();
        SqlDataReader dr;
            cnn.Open();
        dr = cm.ExecuteReader();
        cnn.Close();
        
            lbl_msg.Text = "Registration is Done Successfully!!";
        }
        catch(Exception ex)
        {
           lbl_msg.Text = "There is some Error!!";
        }
    }


I m getting execption in this.... it is not executing..plesae help..
Posted 27-Feb-13 2:59am
Updated 27-Feb-13 3:07am
CHill60162.3K
v3
Comments
Ankit_Sharma1987 27-Feb-13 8:03am
   
yes sir i have editted it... now plese check this
Bhushan Shah1988 27-Feb-13 8:03am
   
please specify exception.
boogac 27-Feb-13 8:03am
   
where is the exception point and what exception do you get?
Ankit_Sharma1987 27-Feb-13 8:05am
   
Actually in Catch, i have called exeption...but , i want registration should be done...i cant recognise, that, why it is going to catch and giving exeption???
Ankit_Sharma1987 27-Feb-13 8:06am
   
Actually in Catch, i have called exeption...but , i want registration should be done...i cant recognise, that, why it is going to catch and giving exeption???
CHill60 27-Feb-13 8:08am
   
Replace lbl_msg.Text = "There is some Error!!"; with lbl_msg.Text = ex.Message; and then post details of the exception
boogac 27-Feb-13 8:11am
   
because there is an error and it is giving exception..try to insert breakpoint to begining of your code and move with F11 line by line..see which line the error occurs
Ankit_Sharma1987 27-Feb-13 8:12am
   
it is Showing this sir:
Procedure or function 'str_insert_register' expects parameter '@username', which was not supplied
boogac 27-Feb-13 8:14am
   
cm.Parameters.Add( "username".. thats you forget I think
Ankit_Sharma1987 27-Feb-13 8:17am
   
sir, that is auto generated...that, field, will bhi generated, automatically, please see, proceedure...
SET @username=@firstname+right('0'+convert(varchar,datepart(dd,getdate())) ,2)
Ankit_Sharma1987 27-Feb-13 8:19am
   
i have , not metioned..textbox, because, i want it , auto generated..
boogac 27-Feb-13 8:20am
   
insert into register(id,username,firstname,... thats your procedure's line..

while in code cm.Parameters.Add("firstname", SqlDbType.NVarChar, 50).Value=txt_fname.Text;
cm.Parameters.Add("lastname", ...

id generated automaticly i assume..but there is no username parameter at button submit click??
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

In your procedure your first parameter is @username nvarchar(50),
but you haven't put it into your C# code ...
cm.Parameters.Add("username", SqlDbType.NVarChar, 50).Value=...


{Edit] - from the comments attached OP wants the username to be autogenerated by the line
SET @username=@firstname+right('0'+convert(varchar,datepart(dd,getdate())) ,2)

so I've suggested he removes it from the list of parameters to the SP and declares it in the body of the SP i.e. ...
CREATE PROCEDURE str_insert_register
--@username nvarchar(50),
@firstname nvarchar(50),
...
declare @id as int
declare @username nvarchar(50)
...
  Permalink  
v2
Comments
Ankit_Sharma1987 27-Feb-13 8:22am
   
sir, that is auto generated...that, field, will bhi generated, automatically, please see, proceedure...
SET @username=@firstname+right('0'+convert(varchar,datepart(dd,getdate())) ,2)
boogac 27-Feb-13 8:24am
   
even if you want to set value at Stored Procedure you need to add this line, I think..send with null value maybe?
Ankit_Sharma1987 27-Feb-13 8:24am
   
I want, as the user will make, a registration, username, will be generated automatically...for that i have mentioned this, line in proceedure...
SET @username=@firstname+right('0'+convert(varchar,datepart(dd,getdate())) ,2)
Ankit_Sharma1987 27-Feb-13 8:25am
   
Acctually i can do this, by trigger...but i want it, by proceedure...ok , i m checking...
CHill60 27-Feb-13 8:32am
   
Then remove it from the list of input parameters to the stored procedure and put the declaration into the body of the SP
Ankit_Sharma1987 27-Feb-13 8:47am
   
USE [JIN]
GO
/****** Object: StoredProcedure [dbo].[str_insert_register] Script Date: 02/27/2013 14:52:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[str_insert_register]
@title nvarchar(50),
@firstname nvarchar(50),
@lastname nvarchar(50),
@fullname nvarchar(50),
@city nvarchar(50),
@street nvarchar(50),
@postcode nvarchar(50),
@telephone nvarchar(50),
@dob nvarchar(50),
@occupation nvarchar(50),
@emailid nvarchar(50),
@pwd nvarchar(50),
@abtsite nvarchar(50)
AS
BEGIN
SET NOCOUNT ON
declare @username as nvarchar(50)
declare @entrydate as datetime
SET @username=@firstname+right('0'+convert(varchar,datepart(dd,getdate())) ,2)
insert into register(title,username,firstname,lastname,fullname,city,street,postcode,telephone,dob,occupation,emailid,pwd,abtsite,entrydate)values(@title,@username,@firstname,@lastname,@fullname,@city,@street,@postcode,@telephone,@dob,@occupation,@emailid,@pwd,@abtsite,@entrydate)
insert into login(username,emailid,pwd)values(@username,@emailid,@pwd)
END
i have done changes in proceedure....
and then finally registration is done...thanks, sir..thanks 4 guidance
Ankit_Sharma1987 27-Feb-13 8:47am
   
USE [JIN]
GO
/****** Object: StoredProcedure [dbo].[str_insert_register] Script Date: 02/27/2013 14:52:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[str_insert_register]
@title nvarchar(50),
@firstname nvarchar(50),
@lastname nvarchar(50),
@fullname nvarchar(50),
@city nvarchar(50),
@street nvarchar(50),
@postcode nvarchar(50),
@telephone nvarchar(50),
@dob nvarchar(50),
@occupation nvarchar(50),
@emailid nvarchar(50),
@pwd nvarchar(50),
@abtsite nvarchar(50)
AS
BEGIN
SET NOCOUNT ON
declare @username as nvarchar(50)
declare @entrydate as datetime
SET @username=@firstname+right('0'+convert(varchar,datepart(dd,getdate())) ,2)
insert into register(title,username,firstname,lastname,fullname,city,street,postcode,telephone,dob,occupation,emailid,pwd,abtsite,entrydate)values(@title,@username,@firstname,@lastname,@fullname,@city,@street,@postcode,@telephone,@dob,@occupation,@emailid,@pwd,@abtsite,@entrydate)
insert into login(username,emailid,pwd)values(@username,@emailid,@pwd)
END
i have done changes in proceedure....
and then finally registration is done...thanks, sir..thanks 4 guidance.
ThePhantomUpvoter 27-Feb-13 11:36am
   
5 for the solution in the comment.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

hello,

change this

dr = cm.ExecuteReader();
TO
dr = cm.ExecuteNonQuery (); or
dr = cm.ExecuteScalar ();


hopes this solve your problem.
  Permalink  

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

    Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170217.1 | Last Updated 27 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100