Error in stored procedure that updates a row

I have the following stored procedure:

CREATE PROCEDURE user1122500.sp_modifyOrganization
(
@.Name nvarchar(100)
,@.Location nvarchar(50)
,@.Url nvarchar (250)
,@.Org_Type nvarchar (50)
,@.Par_Org_Id uniqueidentifier
,@.Row_Id uniqueidentifier
,@.Error_Code int OUTPUT
,@.Error_Text nvarchar(768) OUTPUT
)
AS
DECLARE @.errorMsg nvarchar(512)
DECLARE @.spName sysname

SELECT @.spName = Object_Name(@.@.ProcID)
SET @.Error_Code = 0

IF @.Url > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET URL = @.Url
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'URL', @.Url
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Org_Type > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET ORG_TYPE = @.Org_Type
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'ORG_TYPE', @.Org_Type
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Par_Org_Id IS NOT NULL
BEGIN
UPDATE USER1122500.ORGANIZATION
SET PAR_ORG_ID = @.Par_Org_Id
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'PAR_ORG_ID', @.Par_Org_Id
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Name > ' ' OR @.Location > ' '
BEGIN

IF EXISTS (SELECT ROW_ID FROM USER1122500.ORGANIZATION WHERE NAME = @.Name AND LOCATION = @.Location)
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'NAME', @.Name,
N'LOCATION', @.Location
SET @.Error_Code = 55004 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
-- RAISERROR(@.Error_Text, 10, 1, @.spName, @.Error_Code, 'ORGANIZATION', @.errorMsg)
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'sp_name',@.spName))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'err_cd',@.Error_Code))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'tbl_name','ORGANIZATION'))
SELECT @.Error_Text = (SELECT REPLACE(@.Error_Text,'err_msg',@.errorMsg))
RETURN(@.Error_Code)
END

IF @.Name > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET NAME = @.Name
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'PAR_ORG_ID', @.Name
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

IF @.Location > ' '
BEGIN
UPDATE USER1122500.ORGANIZATION
SET LOCATION = @.Location
,UPDATED = GETDATE()
WHERE ROW_ID = @.Row_Id

IF @.@.error <> 0
BEGIN
EXEC user1122500.sp_tagValueList @.errorMsg OUTPUT, N'ROW_ID', @.Row_Id,
N'LOCATION', @.Location
SET @.Error_Code = 51002 -- Error Message as created in the ERROR_LIST table
SELECT @.Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @.Error_Code)
RAISERROR(@.Error_Text, 11, 1, @.spName, @.@.error, 'ORGANIZATION', @.errorMsg)
RETURN(@.@.error)
END
END

END
GO

This is the code that runs it:

string strSP = "sp_modifyOrganization";

SqlParameter[] Params =new SqlParameter;

string strParOrgID =null;

if (this.ddlParentOrg.SelectedItem.Value != "")

{

strParOrgID =this.ddlParentOrg.SelectedItem.Value;

}

Params[0] =new SqlParameter("@.Name", txtName.Text);

Params[1] =new SqlParameter("@.Location",this.txtLocation.Text);

Params[2] =new SqlParameter("@.Url",this.txtURL.Text);

Params[3] =new SqlParameter("@.Org_Type",this.txtOrgType.Text);

//Params[4] = new SqlParameter("@.Par_Org_Id", strParOrgID);

Params[4] =new SqlParameter("@.Par_Org_Id", "CA1FBC83-D978-48F1-BCBC-E53AD5E8A321".ToUpper());

Params[5] =new SqlParameter("@.Row_Id", "688f2d10-1550-44f8-a62c-17610d1e979a".ToUpper());

// Params[5] = new SqlParameter("@.Row_Id", lblOrg_ID.Text);

Params =new SqlParameter("@.Error_Code", -1);

Params[7] =new SqlParameter("@.Error_Text", "");

Params[4].SqlDbType = SqlDbType.UniqueIdentifier;

Params[5].SqlDbType = SqlDbType.UniqueIdentifier;

Params.Direction = ParameterDirection.Output;

Params[7].Direction = ParameterDirection.Output;

try

{

this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);

if (Params.Value.ToString() != "0")

{

lblError.Text = "There was an error: " + Params.Value.ToString()+ "###" + Params[7].Value.ToString();

lblError.Visible =true;

}

}

//catch (System.Data.SqlClient.SqlException ex)

catch (System.InvalidCastException inv)

{

lblError.Text = lblOrg_ID.Text + "<br><br>" + inv.ToString() + inv.Message + inv.StackTrace + inv.HelpLink;

lblError.Visible =true;

}

catch (Exception ex)

{

lblError.Text = lblOrg_ID.Text + "<br><br>" + ex.ToString();

lblError.Visible =true;

// return false;

}

This is the exception being generated:

System.InvalidCastException: Invalid cast from System.String to System.Guid.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\Program Files\_vsNETAddOns\Microsoft Application Blocks for .NET\Data Access v2\Code\VB\Microsoft.ApplicationBlocks.Data\SQLHelper.vb:line 542
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\Program Files\_vsNETAddOns\Microsoft Application Blocks for .NET\Data Access v2\Code\VB\Microsoft.ApplicationBlocks.Data\SQLHelper.vb:line 458
at development.youthleadercert.com.share.ascx.organizationForm.btnAdd_Click(Object sender, EventArgs e) in c:\documents and settings\mark rubin\vswebcache\development.youthleadercert.com\share\ascx\organizationform.ascx.cs:line 352

I have no idea what field is even causing the error, nor do I see that I'm even using a GUID field. I've been stuck on this for 2 days. Any help?

I would guess it's here. You're passing them as strings. You may want to explicitly type them as UniqueIdentifiers. Or, change your proc temporarily and define your parameters as varchars and see what happens. I bet that even though they may look like GUIDs, SQL doesn't see them that way when they're passed in. Just a guess, but that would be where I would start.

Params[4] =new SqlParameter("@.Par_Org_Id", "CA1FBC83-D978-48F1-BCBC-E53AD5E8A321".ToUpper());

Params[5] =new SqlParameter("@.Row_Id", "688f2d10-1550-44f8-a62c-17610d1e979a".ToUpper());

|||

In my code, I already am setting the db type a few rows down...

Params[4].SqlDbType = SqlDbType.UniqueIdentifier;

Params[5].SqlDbType = SqlDbType.UniqueIdentifier;

Error in Merge Agent Replication

Dear All,
while connecting the merge agent after updates in the
subscriber , the following error occured
'the process could not enumerate changes at the subscriber'
Any reason why this error appears?
Waiting for ur feedback.
Thanks in advance
Dalia,
Could you enable logging on the merge agent (-output
c:\somefile.log -outputverboselevel 3) and we can see if
there is any more info to help.
Regards
Paul Ibison
|||does the error message complain about any missing stored procedures?
90% or more of merge replication errors can be clear by simply restart your merge agent. Can you try this to see if it clears the error message.

Error Handling/ Stored Procs

Hi,
I'm doing some fairly basic updates with stored procedures. 99% of them affect one row. I've jsut discovered that I can't get the value of @.@.rowcount and @.@.error to return as output parameters (if I check one, the other one gets reset!). My theory is then to return the rowcount and if it's not = 1, then I know I've had a problem. If I begin a transaction in vb.net and call each proc in the required order and check each step that rowcount = 1, is this a reliable method of ensuring no errors have occurred?
Thanks.As far as dealing with both @.@.ROWCOUNT and @.@.ERROR is concerned, this is what I do:

SELECT @.lError = @.@.ERROR, @.lRowCount = @.@.ROWCOUNT

I select the values into local variables in the stored procedure andthen do whatever is needed based on those values. Note that youmust SELECT both values in the same statement, and I *believe* @.@.ERRORmust appear first because it will be reset when @.@.ROWCOUNT is accessed.
In your case, @.lError should be 0 and @.lRowCount should be 1 when a record is inserted correctly.


|||Thanks, I can now get my two values back!