Linq to SQL help please!
-
Hope someone out there can help me………. I’m working on a personal project based upon John Prabhu’s article “Building Multi-Tier Web Application in NET 3.5 Framework Using LINQ to SQL”. The problem I’m having is with two tables I’ve set up to identify a city: OFG_City and OFG_State. City ID (int) Primary Key City (Nchar(40)) State ID (int) State ID (int) Primary Key State (Nchar(40)) I created a LinQ to SQL class using the designer called OFG_DataClassesDataContext which my DAL connects to. I'm having trouble when I try to insert a city record. The exception thrown is: “Cannot insert explicit value for identity column in table 'OFG_City' when IDENTITY_INSERT is set to OFF.” Here is my code DAL Layer - called DAL_Farm '--------------------------------------------------------------------- 'Name : InsertCity 'Desc : Adds a New Record to the DataContext and submits changes to the Database 'Inputs : Table Entity 'Returns: Integer '--------------------------------------------------------------------- Public Function InsertCity(ByVal LocalTable As OFG_City) As Integer Try objDataContext.OFG_Cities.InsertOnSubmit(LocalTable) objDataContext.SubmitChanges() Return LocalTable.City_ID Catch ex As Exception Throw ex End Try End Function BAL Layer called BLF_Farm '--------------------------------------------------------------------- 'Name : SelectCityByIDListable 'Purpose : Call Data Access method to retrieve One record from table 'Input : State - Integer 'Returns : List Collection '-------------------------------------------------------------------- Public Function SelectCityByIDListable(ByVal City_ID As Integer) As List(Of OFG_City) Try Return MobjDataAccess.SelectCityByIDListable(City_ID) Catch ex As Exception Throw ex End Try End Function My UI layer consists of a textbox (txtCityText) and two dropdownboxes.(CityDropDownList, State DropDownList) and a button(Button1) These are some of the subs/functions I’m calling when setting up my page. 'Declare objects to access business logic Private mobjFarms As BFL_Farm '----------------------------------------------------------------- ' Name : onLoad ' Purpose : On load event Calls BindDropDownList procedure to get data ' and bind to DropDownLis
-
Hope someone out there can help me………. I’m working on a personal project based upon John Prabhu’s article “Building Multi-Tier Web Application in NET 3.5 Framework Using LINQ to SQL”. The problem I’m having is with two tables I’ve set up to identify a city: OFG_City and OFG_State. City ID (int) Primary Key City (Nchar(40)) State ID (int) State ID (int) Primary Key State (Nchar(40)) I created a LinQ to SQL class using the designer called OFG_DataClassesDataContext which my DAL connects to. I'm having trouble when I try to insert a city record. The exception thrown is: “Cannot insert explicit value for identity column in table 'OFG_City' when IDENTITY_INSERT is set to OFF.” Here is my code DAL Layer - called DAL_Farm '--------------------------------------------------------------------- 'Name : InsertCity 'Desc : Adds a New Record to the DataContext and submits changes to the Database 'Inputs : Table Entity 'Returns: Integer '--------------------------------------------------------------------- Public Function InsertCity(ByVal LocalTable As OFG_City) As Integer Try objDataContext.OFG_Cities.InsertOnSubmit(LocalTable) objDataContext.SubmitChanges() Return LocalTable.City_ID Catch ex As Exception Throw ex End Try End Function BAL Layer called BLF_Farm '--------------------------------------------------------------------- 'Name : SelectCityByIDListable 'Purpose : Call Data Access method to retrieve One record from table 'Input : State - Integer 'Returns : List Collection '-------------------------------------------------------------------- Public Function SelectCityByIDListable(ByVal City_ID As Integer) As List(Of OFG_City) Try Return MobjDataAccess.SelectCityByIDListable(City_ID) Catch ex As Exception Throw ex End Try End Function My UI layer consists of a textbox (txtCityText) and two dropdownboxes.(CityDropDownList, State DropDownList) and a button(Button1) These are some of the subs/functions I’m calling when setting up my page. 'Declare objects to access business logic Private mobjFarms As BFL_Farm '----------------------------------------------------------------- ' Name : onLoad ' Purpose : On load event Calls BindDropDownList procedure to get data ' and bind to DropDownLis
Check to see which of the columns is set up as an identity column in the city table, and then make sure you aren't actually trying to set that value. The error text is telling you that you are explicitly trying to set an identity value (you haven't set the state id to be an identity column have you?)
Deja View - the feeling that you've seen this post before.
-
Check to see which of the columns is set up as an identity column in the city table, and then make sure you aren't actually trying to set that value. The error text is telling you that you are explicitly trying to set an identity value (you haven't set the state id to be an identity column have you?)
Deja View - the feeling that you've seen this post before.
Thanks for your reply and sorry it took me so long to get back. Time zones eh? I think the problem is with my databases not my code. I'm not explicitaly calling the City ID column and I've not made the State ID column a primary key in my City table, just in it's own State table. I've never worked with databases before. I'm entirely self taught and normally just play around with application programming. I think I've got figured the basics of Linq (what I need it for anyway). It's one of those times where i've had a great idea but sadly lack (at the moment, i hope), the skills to impliment it. What i need to do is set up a 4 tables... I think? One main table (a farm) with values linked to other tables; city, state, and map. Those three other tables need to be linked to each other so I can search by each property and make sure there's no chance of setting up errors. (i.e when setting up a farm, I don't want to have the wrong city linked to state). I thought I had it but obviously not. Does anyone have any pointers or can they spot something wrong in my code? Thanks Again,
-
Thanks for your reply and sorry it took me so long to get back. Time zones eh? I think the problem is with my databases not my code. I'm not explicitaly calling the City ID column and I've not made the State ID column a primary key in my City table, just in it's own State table. I've never worked with databases before. I'm entirely self taught and normally just play around with application programming. I think I've got figured the basics of Linq (what I need it for anyway). It's one of those times where i've had a great idea but sadly lack (at the moment, i hope), the skills to impliment it. What i need to do is set up a 4 tables... I think? One main table (a farm) with values linked to other tables; city, state, and map. Those three other tables need to be linked to each other so I can search by each property and make sure there's no chance of setting up errors. (i.e when setting up a farm, I don't want to have the wrong city linked to state). I thought I had it but obviously not. Does anyone have any pointers or can they spot something wrong in my code? Thanks Again,
Funny it was you that replied! It was reading your busiines guide articles that started me on this.
-
Funny it was you that replied! It was reading your busiines guide articles that started me on this.
Tell you what, send me your database scripts and your source and I'll take a look at them. To send them, send to pete dot ohanlon at gmail dot com.
Deja View - the feeling that you've seen this post before.
-
Funny it was you that replied! It was reading your busiines guide articles that started me on this.
-
Tell you what, send me your database scripts and your source and I'll take a look at them. To send them, send to pete dot ohanlon at gmail dot com.
Deja View - the feeling that you've seen this post before.
Thanks for your kind offer but I cracked it last night. Whoo hoo! Turns out that I hadn't set up the relationships properly in my tables. For some reason the link to sql class designer gives you the option to set them up but doesn't seem to do it. I'll figure out what that is for someday i'm sure. Thanks again. I'll probably be back in here with more questions. James
-
Thanks for your kind offer but I cracked it last night. Whoo hoo! Turns out that I hadn't set up the relationships properly in my tables. For some reason the link to sql class designer gives you the option to set them up but doesn't seem to do it. I'll figure out what that is for someday i'm sure. Thanks again. I'll probably be back in here with more questions. James
Well done you. :laugh:
Deja View - the feeling that you've seen this post before.