我对存储过程非常陌生。
我没有更多的想法了。
我正在尝试使用存储过程将数据插入数据库。
如果存在则返回0,否则插入数据并返回1。
这样我就可以在标签上打印已插入数据或数据已存在的文本。
每次输入都会返回 -1。
这是我的存储过程。是不是不对?
USE [HRMS]
GO
/****** Object: StoredProcedure [dbo].[hrm_AddLanguages2] Script Date: 05/02/2014 10:19:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[hrm_AddLanguages2]
-- Add the parameters for the stored procedure here
@Name varchar(120),
@CreatedOn datetime,
@UpdatedOn datetime=0,
@CreatedBy bigint=0,
@UpdatedBy bigint=0,
@IsDeleted bit=0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRANSACTION
DECLARE @ID int;
SELECT @ID = coalesce((select max(ID) + 1 from Languages), 1)
COMMIT
-- Insert statements for procedure here
IF EXISTS(SELECT Name FROM [dbo].[Languages] WHERE Name = @Name)
Begin
Return 0
End
Else
INSERT INTO [dbo].[Languages](Name, CreatedOn,UpdatedOn,CreatedBy,UpdatedBy,IsDeleted) VALUES(@Name,@CreatedOn,@UpdatedOn,@CreatedBy,@UpdatedBy,@IsDeleted)
Return 1
END
这是我的 Default.aspx.cs 代码-
protected void btnSave_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
btnAdd.Visible = true;
btnDelete.Visible = true;
Label2.Visible = true;
tblAdd.Visible = false;
int valueReturn = add();
if (valueReturn == 1)
{
Label2.Text = "Successfully Added";
}
else
{
Label2.Text = "Already Exist";
}
}
GridView1.DataBind();
txtName.Text = "";
}
public int add()
{
string strcon = ConfigurationManager.ConnectionStrings["Dbconnection"].ConnectionString;
SqlConnection sqlConnection = new SqlConnection(strcon);
SqlCommand command = new SqlCommand("hrm_AddLanguages2", sqlConnection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text;
command.Parameters.Add("@CreatedOn", SqlDbType.DateTime).Value = DateTime.Now;
command.Parameters.Add("@UpdatedOn", SqlDbType.DateTime).Value = DateTime.Now;
command.Parameters.Add("@CreatedBy", SqlDbType.BigInt).Value = 1;
command.Parameters.Add("@UpdatedBy", SqlDbType.BigInt).Value = 1;
command.Parameters.Add("@IsDeleted", SqlDbType.Bit).Value = 0;
sqlConnection.Open();
return command.ExecuteNonQuery();
}