centem
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_create UserId", con);
cmd.CommandType = CommandType.StoredProcedur e;
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.
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;
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_create
cmd.CommandType = CommandType.StoredProcedur
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.
Your code looks clean. Make sure that your function protected void Button1_Click(object sender, EventArgs e) is getting called on button click button.
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 !!
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 !!
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,Middl eI,Title)
VALUES(@UserId,@LastN,@Fir stN,@Middl eI,@Title)
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_createUserId](@
@LastN varchar(25),@FirstN varchar(15),@MiddleI varchar(1),
@Title varchar(30))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.UserList (UserId,LastN,FirstN,Middl
VALUES(@UserId,@LastN,@Fir
END
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;
string fn = txtFN.Text;
string mi = txtMI.Text;
string ln = txtLN.Text;
string title = txtTitle.Text;
string userid = fn+"."ln;
ASKER
Is that @ sign requred?
ya @ is necessary
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
uses SqlDbType.(your db type for that column )
http://www.c-sharpcorner.com/UploadFile/gtomar/storedprocedure12052007003126AM/storedprocedure.aspx