Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2008-02-05 : 22:01:14
|
| Hello,I am creating a table has follows:create table dbo.Tags( TagID uniqueidentifier not null default NewID() constraint PK_Tag primary key clustered, TagText nvarchar(100) not null)I am using a LinqDataSource and a ListView to create records in this table. I am able to delete and update records but when I insert a record I get the error:Violation of PRIMARY KEY constraint 'PK_Tag'. Cannot insert duplicate key in object 'dbo.Tags'. The statement has been terminated.In fact I am able to insert ONE record and its ID becomes:00000000-0000-0000-0000-000000000000So when I insert a second one, I suppose, the ID is 000... again and this is why I get the error ...What am I doing wrong?Thanks,Miguel |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-05 : 22:03:40
|
| What insert statement are you using? You need to exclude your TagID column from the column list in order for the default to be used.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2008-02-06 : 08:53:34
|
| Hello,I did excluded my TagID column.I created this at DesignView with VS 2008. I am trying to make this work before I create my ListView and LinqDataSource at runtime because I was having a problem with that.Anyway, I am posting my entire code.Do you know what I am doing wrong?<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ListViewDesign.aspx.vb" Inherits="ListViewDesign" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:ListView ID="ListView1" runat="server" DataSourceID="LinqDataSource1" DataKeyNames="TagID" InsertItemPosition="FirstItem"> <AlternatingItemTemplate> <tr style=""> <td> <asp:Label ID="TextLabel" runat="server" Text='<%# Eval("Text") %>' /> </td> </tr> </AlternatingItemTemplate> <LayoutTemplate> <table runat="server"> <tr runat="server"> <td runat="server"> <table ID="itemPlaceholderContainer" runat="server" border="0" style=""> <tr runat="server" style=""> <th runat="server"> Text</th> </tr> <tr ID="itemPlaceholder" runat="server"> </tr> </table> </td> </tr> <tr runat="server"> <td runat="server" style=""> <asp:DataPager ID="DataPager1" runat="server"> <Fields> <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" ShowLastPageButton="True" /> </Fields> </asp:DataPager> </td> </tr> </table> </LayoutTemplate> <InsertItemTemplate> <tr style=""> <td> <asp:Button ID="InsertButton" runat="server" CommandName="Insert" Text="Insert" /> <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Clear" /> </td> <td> <asp:TextBox ID="TextTextBox" runat="server" Text='<%# Bind("Text") %>' /> </td> </tr> </InsertItemTemplate> <SelectedItemTemplate> <tr style=""> <td> <asp:Label ID="TextLabel" runat="server" Text='<%# Eval("Text") %>' /> </td> </tr> </SelectedItemTemplate> <EmptyDataTemplate> <table runat="server" style=""> <tr> <td> No data was returned.</td> </tr> </table> </EmptyDataTemplate> <EditItemTemplate> <tr style=""> <td> <asp:Button ID="UpdateButton" runat="server" CommandName="Update" Text="Update" /> <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Cancel" /> </td> <td> <asp:TextBox ID="TextTextBox" runat="server" Text='<%# Bind("Text") %>' /> </td> </tr> </EditItemTemplate> <ItemTemplate> <tr style=""> <td> <asp:Label ID="TextLabel" runat="server" Text='<%# Eval("Text") %>' /> </td> <td> <asp:LinkButton ID="EditButton" runat="Server" Text="Edit" CommandName="Edit" /> <asp:LinkButton ID="DeleteButton" runat="Server" Text="Delete" CommandName="Delete" /> </td> </tr> </ItemTemplate> </asp:ListView> <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="Code.Data.Linq.CodeDataContext" TableName="Tags" EnableDelete="True" EnableInsert="True" EnableUpdate="True" > </asp:LinqDataSource> </div> </form></body></html>Thanks,Miguel |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2008-02-06 : 10:47:28
|
| One more thing:When I use query to insert the record it works fine.The database creates a new ID of type Guid.But when using the ListView I always get the same problem.I have been inspecting my code and comparing to others code examples in various articles and I can't find what I am doing wrong.Thanks,Miguel |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2008-02-06 : 21:39:47
|
| Hi,I also tried to create my table as follows:create table dbo.Tags( TagID uniqueidentifier ROWGUIDCOL not null default NewID() constraint PK_Tag primary key clustered, [Text] nvarchar(100) not null)But I still have the same error.Any idea? I am running out of ideas ...Thanks,Miguel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-06 : 22:08:16
|
| Since it works when you run an insert statement directly but not from your ListView, I'd suggest posting your question on a forum that specifically deals with the programming language that you are using. The reason for this is that the problem is not within the database but rather from your application code.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2008-02-07 : 10:15:30
|
| I found the solution!Here it is for future reference:TagId should be marked as IsDbGenerated in the class model. Something like:[Column(Name = "TagId", IsPrimaryKey = true, IsDbGenerated=true,AutoSync=AutoSync.OnInsert)]The Autosync that instructs Linq To Sql when to sync this column withthe database.Cheers,Miguel |
|
|
|
|
|
|
|
|
Error on Insert