?
Solved

Stored Procedure from C# ASP.net page

Posted on 2011-05-12
10
Medium Priority
?
259 Views
Last Modified: 2012-08-14
Hi,
I'm trying to execute a stored procedure from c# code and ASP page. Here is my code.
public partial class _Default : System.Web.UI.Page
{
    string strcon = "Data Source=BLVU316DB2\\PORTAL;Initial Catalog=u2kdb;Integrated Security=True";
    SqlConnection con = new SqlConnection();
    SqlCommand com = new SqlCommand();

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string fn = txtFN.Text;
        string mi = txtMI.Text;
        string ln = txtLN.Text;  
        string title = txtTitle.Text;
try
        {
            con.ConnectionString = strcon;
            con.Open();

            SqlCommand cmd = new SqlCommand("dbo.usp_createUserId", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@UserId", userid));
            cmd.Parameters.Add(new SqlParameter("@LastN", ln));
            cmd.Parameters.Add(new SqlParameter("@FirstN", fn));
            cmd.Parameters.Add(new SqlParameter("@MiddleI", mi));
            cmd.Parameters.Add(new SqlParameter("@Title", title));

            com.ExecuteNonQuery();
            con.Close();
            Label1.Text = "success";
        }
        catch
        {
            Label1.Text = "error";
        }

}
}
It does not add a new entry to the DB but it also does not error out either. The Label do not get written to.
0
Comment
Question by:centem
10 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 35745409
0
 
LVL 9

Expert Comment

by:anillucky31
ID: 35745495
Your code looks clean. Make sure that your function  protected void Button1_Click(object sender, EventArgs e) is getting called on button click button.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35745548
need to see you sp
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 8

Expert Comment

by:Anurag Agarwal
ID: 35746177
hello !!

first check in cmd.Parameters.Add(new SqlParameter("@UserId", userid)); from where the userid is being initialised ?? as no information is there in ur code secondly it will be more helpful if u give ur stored procedure for checking !!


0
 

Author Comment

by:centem
ID: 35746795
Thanks for your responses. Below is the sp.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_createUserId](@UserId varchar(25),
@LastN varchar(25),@FirstN varchar(15),@MiddleI varchar(1),
@Title varchar(30))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.UserList (UserId,LastN,FirstN,MiddleI,Title)
VALUES(@UserId,@LastN,@FirstN,@MiddleI,@Title)
END
0
 

Author Comment

by:centem
ID: 35747106
My apologies, I didn't include the userid but it was in the code.
    string fn = txtFN.Text;
        string mi = txtMI.Text;
        string ln = txtLN.Text;  
        string title = txtTitle.Text;
string userid = fn+"."ln;
0
 

Author Comment

by:centem
ID: 35754372
Is that @ sign requred?
0
 
LVL 8

Expert Comment

by:Anurag Agarwal
ID: 35759705
ya @ is necessary
0
 
LVL 8

Accepted Solution

by:
Anurag Agarwal earned 2000 total points
ID: 35759709
Buddy just change a little ...........may b tht should help u !!

con.ConnectionString = strcon;
            con.Open();

            SqlCommand cmd = new SqlCommand("dbo.usp_createUserId", con);
            cmd.CommandType = CommandType.StoredProcedure;

         //   cmd.Parameters.Add(new SqlParameter("@UserId", userid));
          //  cmd.Parameters.Add(new SqlParameter("@LastN", ln));
           // cmd.Parameters.Add(new SqlParameter("@FirstN", fn));
           // cmd.Parameters.Add(new SqlParameter("@MiddleI", mi));
           // cmd.Parameters.Add(new SqlParameter("@Title", title));

command.Parameters.Add("@UserId", SqlDbType.VarChar).Value =userid ;
command.Parameters.Add("@LastN", SqlDbType.VarChar).Value =ln ;
command.Parameters.Add("@FirstN", SqlDbType.VarChar).Value =fn ;
command.Parameters.Add("@MiddleI", SqlDbType.VarChar).Value =mi ;
command.Parameters.Add("@Title", SqlDbType.VarChar).Value =title ;

            com.ExecuteNonQuery();
            con.Close();
            Label1.Text = "success";

0
 
LVL 8

Expert Comment

by:Anurag Agarwal
ID: 35759711
uses SqlDbType.(your db type for that column )
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question