ASP.NET SQL insert error with primary key field

I am using an InsertCommand to insert into my data table, but when the form gets submitted, I get the following error:

"System.Data.SqlClient.SqlException: Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query." -- The pers_personid field below is type "UniqueIdentifier" and is set as the primary key.  

My InsertCommand:

InsertCommand="INSERT INTO [tblDCPersonTest] ([pers_firstname], [pers_middlename], [pers_lastname], [pers_ssn], [pers_dob], [pers_phonehome], [pers_phonework], [pers_personid]) VALUES (@pers_firstname, @pers_middlename, @pers_lastname, @pers_ssn, @pers_dob, @pers_phonehome, @pers_phonework, @pers_personid)"



How do I fix this?
saturationAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ephraim WangoyaConnect With a Mentor Commented:

How are you creating your unique identifier, you can just use NEWID() instead of passign the @pers_personid variable
InsertCommand="INSERT INTO [tblDCPersonTest] ([pers_firstname], [pers_middlename], [pers_lastname], [pers_ssn], [pers_dob], [pers_phonehome], [pers_phonework], [pers_personid]) VALUES (@pers_firstname, @pers_middlename, @pers_lastname, @pers_ssn, @pers_dob, @pers_phonehome, @pers_phonework, NEWID())"

Open in new window

0
 
cyberkiwiConnect With a Mentor Commented:
change it to

InsertCommand="INSERT INTO [tblDCPersonTest] ([pers_firstname], [pers_middlename], [pers_lastname], [pers_ssn], [pers_dob], [pers_phonehome], [pers_phonework], [pers_personid]) VALUES (@pers_firstname, @pers_middlename, @pers_lastname, @pers_ssn, @pers_dob, @pers_phonehome, @pers_phonework, CONVERT(uniqueidentifier,@pers_personid))"
0
 
saturationAuthor Commented:
Ok, did that.   Now I get

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'pers_personid', table 'tblDCPersonTest'; column does not allow nulls. INSERT fails.

Did I set my primary key to be the wrong data type?   I just want it to start at 1 and increment automatically when a record is inserted.  
0
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
Yes uniqueidentifiers are guids, you should be using an integer column with identity insert set to on and then you won't need to pass in your id as it will be created automatically on insert
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.