Link to home
Start Free TrialLog in
Avatar of centem
centemFlag for United States of America

asked on

Stored Procedure from C# ASP.net page

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.
Avatar of Om Prakash
Om Prakash
Flag of India image

Your code looks clean. Make sure that your function  protected void Button1_Click(object sender, EventArgs e) is getting called on button click button.
Avatar of Alpesh Patel
need to see you sp
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 !!


Avatar of centem

ASKER

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
Avatar of centem

ASKER

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;
Avatar of centem

ASKER

Is that @ sign requred?
ya @ is necessary
ASKER CERTIFIED SOLUTION
Avatar of Anurag Agarwal
Anurag Agarwal
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
uses SqlDbType.(your db type for that column )