Problems with saving Guid type to the database

2009-07-17

Question

_{Friday, July 17, 2009 7:20 PM}

I am trying to save a GUID value to the database. in the database the GUID is defined as 'uniqueidentifier' not null
I pass the GUID into my method and have tried to save it directly. That did not work and so I tried to cast it to
a new Guid. Can someone help me to figure out why I am getting this error?

protected void ProcessRowInsert(String InGuid, int RowNumber)
    {
        JustDeleted = false;
        string connString = ConfigurationManager.ConnectionStrings["EMRConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(connString);
        System.Guid theGuid = new Guid(InGuid);
        string strQuery = "insert into tblTempForEditor values (@tblUserIdParam,@Expression,@SearchTerm,@Conjunction,@UniqueID,@RowIdHidden)";
        SqlCommand cmd = new SqlCommand(strQuery, conn);
        cmd.Parameters.AddWithValue("@tblUserIdParam", "Employee Number");
        cmd.Parameters.AddWithValue("@Expression", "");
        cmd.Parameters.AddWithValue("@SearchTerm", "Asearch term");
        cmd.Parameters.AddWithValue("@Conjunction", "And");
        cmd.Parameters.AddWithValue("@UniqueID", theGuid); // This won't work either?
    /*    cmd.Parameters.AddWithValue("@UniqueID", InGuid); */ this won't work either ?
        cmd.Parameters.AddWithValue("@RowIdHidden", RowNumber);

        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        GridView1.DataBind();

}

Operand type clash: uniqueidentifier is incompatible with int

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Operand type clash: uniqueidentifier is incompatible with int

Source Error:

Line 449:
Line 450:        cmd.Connection.Open();
Line 451:        cmd.ExecuteNonQuery();
Line 452:        GridView1.DataBind();
Line 453:

All replies (5)

_{Saturday, July 18, 2009 4:35 AM ✅Answered}

You are using a variant of the SQL Insert statement which requires the values supplied to match the order of the columns in the table and your table columns are in a different order from the values specified. Generally, it is not a good idea to use this variant as many simple table maintenance operations can make your code break. Try the version which specifies the column names as well as their values. Something like this

INSERT INTO [dbo].[tblTempForEditor]
    (tblUserIdParam, Expression, Conjunction, UniqueID, RowIdHidden, SearchTerm)
VALUES
   (@tblUserIdParam, @Expression, @Conjunction, @UniqueID, @RowIdHidden, @SearchTerm)

_{Friday, July 17, 2009 7:28 PM}

are you sure that you defined a uniqueidentifier in your sql table for the column where you want to insert a guid?

_{Friday, July 17, 2009 7:33 PM}

Could you please try:

cmd.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier).Value =  theGuid;

instead of:

cmd.Parameters.AddWithValue("@UniqueID", theGuid);

_{Friday, July 17, 2009 9:32 PM}

I tried what you just said Remith and I get the same error. Gill, yes it is defined correctly sir:

GO
/****** Object: Table [dbo].[tblTempForEditor] Script Date: 07/17/2009 19:31:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTempForEditor](
[tblUserIdParam] [nvarchar](50) NOT NULL,
[Expression] [nvarchar](20) NOT NULL,
[Conjunction] [varchar](20) NOT NULL,
[UniqueID] [uniqueidentifier] NOT NULL,
[RowIdHidden] [int] NOT NULL,
[SearchTerm] [nchar](200) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

_{Friday, July 17, 2009 10:19 PM}

I guess your GUID is in string variable so you will have to convert it while sending it to DB.

like:

cmd.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier).Value = new Guid(theGUID);