June 23, 2009 at 3:53 am
hello, Let me first introduce to you the entities....
1. User defined table type
USE [EMAIL_APPLICATION]
GO
/****** Object: UserDefinedTableType [dbo].[FIELD_VALUES] Script Date: 06/23/2009 15:19:43 ******/
CREATE TYPE [dbo].[FIELD_VALUES] AS TABLE(
[CLIENT_ID] [int] NULL,
[FIELD_ID] [int] NULL,
[VALUE] [varchar](2000) NULL
)
GO
2. stored procedure having the table valued parameter
USE [EMAIL_APPLICATION]
GO
/****** Object: StoredProcedure [dbo].[sp_InsertIntoClientFields] Script Date: 06/23/2009 11:19:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:SANJEET
-- Create date: 15th JUNE,2009
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertIntoClientFields]
-- Add the parameters for the stored procedure here
@FieldTable FIELD_VALUES READONLY
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRANSACTION
BEGIN TRY
DECLARE @temp table(rowid int IDENTITY(1,1),client_id int,field_id int,value varchar(2000))
INSERT INTO @temp(client_id,field_id,value)(SELECT [CLIENT_ID],[FIELD_ID],[VALUE] FROM @FieldTable)
DECLARE @rowcount int=@@ROWCOUNT;
DECLARE @rowid int=1;
DECLARE @client_id int;
DECLARE @field int;
DECLARE @value varchar(2000);
WHILE @rowid<=@rowcount
BEGIN
SET @client_id=(SELECT client_id FROM @temp WHERE rowid=@rowid)
SET @field=(SELECT field_id FROM @temp WHERE rowid=@rowid)
SET @value=(SELECT value FROM @temp WHERE rowid=@rowid)
IF EXISTS(SELECT * FROM [CRM.Clients.FieldValues] WHERE Client_id=@client_id AND Field_id=@field)
BEGIN
UPDATE [CRM.Clients.FieldValues] SET value=@value WHERE Client_id=@client_id AND Field_id=@field
END
ELSE
BEGIN
INSERT INTO [CRM.Clients.FieldValues](Client_id,Field_id,Value)VALUES(@client_id,@field,@value)
END
SET @rowid+=1;
END
COMMIT TRANSACTION
RETURN 1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN -1
END CATCH
END
[/code]
3. ASP.NET function which calls the sored procedure
[code]
public string fn_saveCustomFields(CoreWebList
{
try
{
DataTable objTable = new DataTable();
objTable.Columns.Add("CLIENT_ID", System.Type.GetType("System.String"));
objTable.Columns.Add("FIELD_ID", Type.GetType("System.Int32"));
objTable.Columns.Add("VALUE", Type.GetType("System.Int32"));
for (int i = 0; i < objList.Count; i++)
{
DataRow row = objTable.NewRow();
row["CLIENT_ID"] = objList[i].iClientId.ToString();
row["FIELD_ID"] = objList[i].iFieldId.ToString();
row["VALUE"] = objList[i].sValue;
row.AcceptChanges();
objTable.Rows.Add(row);
}
objConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["EmailConnectionString"].ConnectionString);
objConnection.Open();
objCommand = new SqlCommand("sp_InsertIntoClientFields", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@FieldTable", objTable);
param.SqlDbType = SqlDbType.Structured;
if (objCommand.ExecuteNonQuery() > 0)
{
return "SUCCESS : Record has been inserted";
}
else
{
return "ERROR : SQL Exception";
}
}
catch (Exception ex)
{
ErrorClass objError = new ErrorClass();
objError.fn_LogError(ex.Message, ex.StackTrace, 1);
return "ERROR : " + ex.Message;
}
finally
{
if (objConnection != null)
{
objConnection.Close();
}
}
}
4. the query used to test the stored procdure
USE [EMAIL_APPLICATION]
GO
DECLARE@return_value int
DECLARE @table FIELD_VALUES
INSERT INTO @table(CLIENT_ID,FIELD_ID,VALUE)VALUES(16,1,5000)
INSERT INTO @table(CLIENT_ID,FIELD_ID,VALUE)VALUES(16,2,4000)
INSERT INTO @table(CLIENT_ID,FIELD_ID,VALUE)VALUES(16,3,5000)
--SELECT * FROM @table
EXEC@return_value = [dbo].[sp_InsertIntoClientFields]
@FieldTable = @table
SELECT'Return Value' = @return_value
GO
ok..so now that we have all the entities, let me tell you the problem.........its simple......the stored procedure works perfectly when executed using the query(entity 4). however it fails to to get desired result when called from the ASP.net function...can neone help
June 23, 2009 at 6:04 am
Hi Sanjeet,
Could you please provide the definition of class "CLIENT_FIELDS", it would be easier to simulate your problem.
I think you are using SQL SERVER 2008.
Please suggest the ASP.NET version.
You said that your ASP.NET call to SP is failing at 4th entity (considering you have around 100 records in "FIELD_VALUES" table), please correct if I am wrong.
June 23, 2009 at 6:18 am
well i am using SQL 2008..and ASP.NET 3.5......
the problem is the stored procedure works fine when i execute the 'query' given in 4th code of my original Post. but when i call it from asp.net it fails to get the desired result.....plus there are no exceptions throwed even when i remove the try catch from the stored procedure
June 23, 2009 at 6:46 am
problem solved.......actually very stupid of me......i didn't link the sqlCommand to the sqlParameter..........those were just two independent entities.......new ways thanks for your time....
June 24, 2009 at 12:07 am
Ok,
Even I didn't see the code here at the forum in detail. Because I used to review the code in its specific IDE (VS IDE in this case). So, I was planning to paste your code, once I got the proper ASP.NET version.
Hope next you and your team would not repeat the same.
Enjoy.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply