| uniqueidentifier .net woes RESOLVED!/me suddenly stops doing the "I AM THE MAN" dance... looking around to see if anyone was watching... oh yeah! So, I did this... in SQL Server: successfully built the current satisfactory iteration of the database wherein I can tag and house articles, links and other research related stuff. Inserts, updates, deletes, etc, all works fine. in Visual Web Developer 2008 Express Edition: successfully set up a site, wherein lives a page, to which I can add a connection to aforementioned SQL database, I can add a grid view, details view, etc.
(Side note... if you haven't set a column as primary key in your table, the SQL data source configuration wizard WILL NOT auto generate insert update and deletes for you, when you click on advanced, it will be grayed/greyed out. So go back and add a pk to that table... tch.)
BUT, when I set details view to allow inserts, save, build and render the page, and go to do an insert..
KABOOM
Server Error in '/Research' Application.
Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query. ooh, not only is is ugly, it's painful.
The good news is, this is simple to fix, and only cost me a day's worth of hours and reading through several M$ help desk guy's smug and incomplete answers (which didn't help btw) to get through. So the smuggish sounding guy's answer is here: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=118262 and I'm willing to bet, if that guy had tried to do an INSERT (bonehead...) he would have received the same error I did, above. Updates work just fine (Bonehead!) because you're not inserting a uniqueidentifier. btw, for all of you M$ haters... I'm NOT, this guy could work for anyone, so I'm just dissing the fact that he didn't completely thoroughly test according to the problem which the guy stated, which very clearly included insert, not just update.
so to the easy fix, on the insert line in your code, mine looked like this.... InsertCommand="INSERT INTO [articles] ([article_id], [article_title], [article_date], [article_url], [article_abstract]) VALUES (@article_id, @article_title, @article_date, @article_url, @article_abstract)" Change the VALUES for the uniqueidentifier column (in my case it is "@article_id" to "newid()" save, rebuild, (which may or may not be necessary, I did because I don't know any better) reload the web page in the browser and VOILA!
You should now be able to insert records into your db. added note here...
some further reading indicates that uniqueidentifier may not be the best route, but that M$ has come up with a newsequentialID, which I have not yet played with, but depending on the size and performance requirements of your db, you may want to use that. My guess is getting it to work from .net will require the same fiddling as above. Again, btw, imho that M$ gives you these 2 ways to do unique identifiers is a whole heck of a lot better than 0rakill within which you have to kludge together some sort of unique id by counting, wherein you can jam numbers out of sequence etc... |
