Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# StoredProcedures
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 1:59am
Edited 27-Feb-13 2:07am
CHill6061.2K
v3
Comments
Ankit_Sharma1987 at 27-Feb-13 8:03am
   
yes sir i have editted it... now plese check this
Bhushan Shah1988 at 27-Feb-13 8:03am
   
please specify exception.
Ankit_Sharma1987 at 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???
boogac at 27-Feb-13 8:03am
   
where is the exception point and what exception do you get?
Ankit_Sharma1987 at 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 at 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
Ankit_Sharma1987 at 27-Feb-13 8:12am
   
it is Showing this sir:
Procedure or function 'str_insert_register' expects parameter '@username', which was not supplied
boogac at 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
boogac at 27-Feb-13 8:14am
   
cm.Parameters.Add( "username".. thats you forget I think
Ankit_Sharma1987 at 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 at 27-Feb-13 8:19am
   
i have , not metioned..textbox, because, i want it , auto generated..
boogac at 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 at 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 at 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 at 27-Feb-13 8:25am
   
Acctually i can do this, by trigger...but i want it, by proceedure...ok , i m checking...
Ankit_Sharma1987 at 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 at 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)
CHill60 at 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 at 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 at 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
0 OriginalGriff 6,651
1 Sergey Alexandrovich Kryukov 6,400
2 CPallini 5,230
3 George Jonsson 3,574
4 Gihan Liyanage 2,542


Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 27 Feb 2013
Copyright © CodeProject, 1999-2014
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