Assigning foreign keys in code.

I am quite new, and though I have purchased and viewed several videos, I cannot figure out the answer to this embarrasingly simple data problem.  I have changed the names of the 2 tables and fields for ease.  This is all for a button click event:

SqlDataSource1.InsertCommand = "INSERT INTO [orders] ([o_detail1], [o_detail2]) VALUES (@o_detail1, @o_detail2)";

SqlDataSource1.InsertParameters.Add("o_detail1", textbox1.text);SqlDataSource1.InsertParameters.Add("o_detail2", textbox2.text);

SqlDataSource1.Insert();

// The above code works fine.  I've done this sort of thing several times.

// The above code also has a primary key I didn't mention because it is automatically incremented/created somehow by VWD.

// I called that primary key column OrderID

// Then I want to do this SAME code below for a different table,the only difference being, I want it to have a foreign key relating back to the OrderID primary key.

SqlDataSource2.InsertCommand = "INSERT INTO [productname] ([pn_detail1], [pn_detail2], [OrderID]) VALUES (@pn_detail1, @pn_detail2, @OrderID)";

SqlDataSource2.InsertParameters.Add("pn_detail1", textbox3.text);

SqlDataSource2.InsertParameters.Add("pn_detail2", textbox4.text);

SqlDataSource2.InsertParameters.Add("Order_ID", ... ???????

// I don't understand how to make the foreign key here called Order_ID assume the value of the automatically incrementing Primary Key called OrderID from the table called Orders!  Otherwise everything would be fine.

SqlDataSource2.Insert();

0
matthewh
4/7/2009 11:09:15 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
517 Views

Similar Articles

[PageSpeed] 9

If you want to insert into two tables, I suggest you to create a stored procedure with all your fields as parameters. In this stored procedure you will do something like:

BEGIN TRANSACTION

   insert into FirstTable ...

   set @NewOrder = scope_identity()

  insert into OrderDetails ...

COMMIT TRANSACTION

In SQL Server 2005 and up you can wrap it in TRY/CATCH block. I posted examples of my SPs here before using this technique.

See http://msdn.microsoft.com/en-us/library/ms188929.aspx


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
4/7/2009 11:39:17 PM

I found sample I posted before in my gmail

http://forums.asp.net/p/1373630/2880551.aspx#2880551


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
4/7/2009 11:40:41 PM
Reply:

Similar Artilces:

Attempt to over-write foreign key column "Code" where table A has 3 foreign keys to table X
Generating from CDM to Logical. PD 9.5.2.889 This is a weird one - 4 tables in my model have more than one foreign key reference to table COROrg. By default a foreign ket to COROrg generates as COROrgKy. I have renamed the other foreign keys appropriately - e.g. COROrgKyEmpGrp. After the rename when I generate from CDM to Logical everything's fine for all but on of the foreign key columns (i.e. the new name/code are maintained in all but one). The list of changes to happen (obtained from Merge dialog -> Merge Preview) says it's going to 'Modify Column "...

Clustered Primary Key and Foreign Key: T-SQL
I am having a little trouble getting this to work right, but have come a ways since I started this.......other tables created first and with no problems..... then these two with the last table being the problemI need to set one foreign key in the second table referencing the first table.But, the primary key is clustered with the two foreign keys and I get the error....There are no primary or candidate keys in the referenced table 'courseScores' that match the referencing column list in the foreign key 'FK_course'.CREATE TABLE dbo.courseScores   (     course...

SQL statement failed, no primary key value for foreign key ... in table ...
Problem: SQL statement failed, no primary key value for foreign key ... in table ... After deleting or changing primary key values in either consolidated or remote database. Question: Are there any triggers that can resolve inserting a record that has a foreign key value with no existing primary key value in primary table? Description: We have a serious problem with the dbremote program. I think it's a general problem that could appear in every database with related tables. Everytime the problem turns up we must create new remote databases from the consolidated datab...

SQL statement failed: (-194) No primary key value for foreign key
I'm getting this error message sporadically when my remote sites are replicating to my central server. The odd thing is the record that is the primary key value does exist! I'm at a lost...Does any have any idea, why this may be happening? Thanks Tasha Did the record exist at the time the error was reported? I have seen cases before where the Parent record is physically sent to the remote *after* the Child record. This would explain why the error was legitimately reported but when you look at the database later the Parent record now exists. -- -------------...

Sql Server Script File(.sql) Execution by Vb.net Code
I have a problem. I m working on "light weight sql server"  project.   and i want to execute .sql file through vb.net code with the help of sqldmo library and sqlns namespace. but i donot know any method to directly execute the .sql file. i am successfully making the full script of select database of sql server.   please help me.... thanks.... Hi, You can use SQL-DMO object in your .net projects by referencing Sqldmo.dll  file which is in  C:\Program Files\Microsoft SQL Server\80\Tools\Binn  folder is default installation parameters are used. I...

Code builders INSERT Data Method
PLEASE HELP I would like to insert the 'primary key' value into a foreign key table each time a record is insert using the: Web Matrix-Code builders-INSERT Data Method All help greatly appreciated......

.net code for sql
how to use .net code from sql function. i have sql function - select * from funcname('delta','1/1/2007,'1/1/2008')  but i want to .net code for this function. how do i get it? can anybody guide me i don't know about .net. Thanks for ue help.        Take a look at this article: http://msdn.microsoft.com/en-us/library/ms131103.aspx?ppud=4Hope this provides some insight.- Jesse Jesse WilliamsPlease mark 'Answered' if my post was able to assist you. This may help:http://www.codeproject.com/KB/database/sql_in_csharp.aspx Regards,...

Table design question (to foreign key or not to foreign key)
Our application is a message board and is full of messages, threads, and forums. A message is an individual entry (like the one I'm typing now ;)). Our software gives the user the ability to upload a single file attachment to each message. I can think of a couple different ways to design the two tables: message( messageid numeric identity, attachmentid numeric null, messagetext text, other, message, stuff... ) attachment( attachmentid numeric identity, title varchar, other, stuff ) or message( messageid, messagetext text, other, stuff ) attachment( messageid numeric foreig...

How to convert C#.Net code to VB.Net code ???...
HI, i was searching something new, then i suddenly find some tips to convert C#.Net code to VB.Net but i did not do well. i am puttint its url here plz tell me how to convert C#.Net code to VB.Net. http://www.dotnetspider.com/code/C-273-Convert-C-VB-NET.aspx Jasim...Please remember to click “Mark as Answer” on the post that helps youJasim AkhtarNew Delhi ( INDIA ) It would be better if you write the problem you having, while converting from C# to VB.NET. You cannot completely rely on online converters. ThanksMark post(s) as "Answer" that helped youElectronic ScrewWebsite||Bl...

Translate c#.net code to vb.net code
Hi I can usually do the translation myself, but this time I am a bit stumped  Let us consider to rewrite Urls (more than 16000 links) using a table. Save original url and rewritten url in a table. Now add a class file in App_Code folder. class UrlRewriteModule : IHttpModule{    public void Dispose()    {        // Nothing to dispose    }    public void Init(HttpApplication context)    {        context.BeginRequest += new EventHandler(context...

assign SQL to datasource dynamically
Hello,I have a default SQL statement assigned to the datasource: select * from customers. But when I want to dynamically change this at runtime, e.g.: accessdatasource.selectcommand="select * from categories where custCity="&  lblCity.Text (lblcity is in this case NY of which there are plenty fields in my DB) it appears that after the postback this command is lost, the gridview linked to the datasource disappears from the screen....This is my problem and this is what I heard before as an answer:"You only specify the SelectCommand when the button is pressed. When you don't press...

Assign a foreign key as a parameter
hi everybody I have the following asp.net2.0 codeSelectCommand = "Select IDEmp, FirstName,MiddleName,LastName, Date,HoursNumber, Description From Employee, WorkOnCategory , CatDesignItemReference where IDEmp =IDEmplWork AND FirstName = @FirstName AND Category = @AnyCategory AND ">   <SelectParameters> <asp:ControlParameter ControlID="txtEmployeeName" DefaultValue="" Name="FirstName" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="DropDownList1" DefaultValue="" N...

SQL Datasource with code behind:
Hi all, I am trying to use a query into the code behind like that: "SELECT SELECT * FROM [ListePage] order by @id desc" the problem is that the errors says that its a non declared for the @id ------------------------------------------------- System.Data.SqlClient.SqlException: Must declare the variable '@id'. ------------------------------------------------- If I try to do this: SqlDataSource1.SelectParameters.Add(new Parameter("@id", TypeCode.String)); SqlDataSource1.SelectCommand = "SELECT * FROM [ListePage] order by @id desc"; In this ...

SQL triggers .net code?
Is it possible to call .net code from an sql trigger? I have a reporting windows service that cycles through reports stored in my db at a designated time interval.  These reports don't change very often, but I'm forced to load from the database frequently so that when something does change it takes effect quickly.  So I was thinking that I could put a trigger on the report table, when something changes, I could use .net remoting to reload the service's Reports object model.  That way the effect would take effect right away and I wouldn't have to reload from t...

Web resources about - Assigning foreign keys in code. - asp.net.sql-datasource

South Carolina Legislators Vote to Cut University Funding for Assigning LGBT Books
Republican legislators in South Carolina want to teach two state universities a lesson for assigning books with LGBT themes.

Pitfall of Scrum: Assigning Tasks
Learn more about our Scrum and Agile training sessions on WorldMindware.com Even though the concept of self-organizing teams has been around ...

Google celebrates Teacher Appreciation Week by assigning new features to Classroom
The first full week of May is designated in the U.S. as Teacher Appreciation Week. It happens to be this week. And in honor of the occasion, ...

Assigning Odds to Every Potential Buccaneers 1st-Round Pick
The NFL draft is the most elaborate gambling enterprise in existence. Obviously, there is considerable skill involved in developing an NFL prospect, ...

Assigning Blame
Never forget: conservatives tortured Jesus and then murdered him Conservatives' infatuation with torture has a long sordid history. Sunday when ...

Assigning blame for new, more costly medical plan, Corporate points to Washington's favorite piñata
I'm always skeptical when companies make broad statements without citing dollar amounts or other hard numbers to back them up. That's why Corporate's ...

Programatically assigning a Customer Group in Magento
Magento’s Customer Groups give you the ability to offer different pricing and promotions to customers based upon their grouping. This grouping ...

Selfie With Parents’ Sex Toys? Alameda Teacher Accused Of Assigning Bizarre Extra Credit For Sophomores ...
Parents of students at an Alameda high school want a teacher fired after they say he sent them home with an extra credit assignment of finding ...


RG3 confirms progress, avoids assigning blame
In an exclusive interview with Redskins Nation, RG3 discusses his recovery and his desire to move past assigning blame.

Resources last updated: 12/15/2015 12:11:11 AM