How do obtain Primary Key value from one table aid insert it into another table?

Hi

 

I have one table called User profile and another table called User Reviews, basically I want to write a query or stored procedure( I don’t know what is best) so that when a registered writes a review, their Id in the User_Profile table is inserted into the User_Review Table in the User_Id field. This is to allow a single user to create multiple reviews.

 

Can anyone help me with how the code / SLQ query should look to achieve this?

  User_Profile

Id   int,  ( as primary Identity Key)

Name

City

Country

 

I have a second table called User Reviews

 User_Revews

Revew_Id   int ,  ( as primary Identity Key)

User_Id  int, ( I want this to contain the Id value in the User profile Table)

Review_Details

 

Thanks a lot guys

 

odxsigma

 

0
odxsigma
12/10/2007 1:26:28 PM
asp.net.getting-started 91979 articles. 4 followers. Follow

5 Replies
842 Views

Similar Articles

[PageSpeed] 40

Here's an example of how to retrieve the last identity using simple ADO.

ASPX 

<div style="margin-bottom: 12px;">
	CompanyName:
	<asp:textbox id="txtCompanyName" runat="server" /><br />
	Phone:
	<asp:textbox id="txtPhone" runat="server" />
</div>
<div style="margin-bottom: 12px;">
	<asp:linkbutton id="btnInsert" runat="server" onclick="btnInsert_Click" text="insert" />
</div>
<asp:label id="lblShipperId" runat="server" />

CODE-BEHIND 

protected void btnInsert_Click(object sender, EventArgs e)
{
	SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

	SqlCommand command = new SqlCommand("INSERT INTO Shippers(CompanyName, Phone) VALUES (@CompanyName, @Phone); SELECT SCOPE_IDENTITY();", connection);
	command.Parameters.AddWithValue("CompanyName", txtCompanyName.Text);
	command.Parameters.AddWithValue("Phone", txtPhone.Text);
	command.Connection.Open();

	try
	{
		lblShipperId.Text = String.Concat("New ShipperID: ", command.ExecuteScalar()); 
	}
	finally
	{
		connection.Close();
	}
}

Thanks, Ed

Microsoft MVP - ASP/ASP.NET

0
ecbruck
12/10/2007 1:40:13 PM

Hi Thanks for that info, sorry I should have been clearer in my question, I want the user to be able to be able to return to their account at a later date and then post multiple reviews of different bands they have seen at a later date. I kept the tables in my example very simple so I could get my head around the concept, but generally, I want to connect the Id (PK) value in User_Profile table to the User_Id filed in the User_Review table, so every review that user writes, will be connected directly to their Id.

Any help you could give would be fantastic ( PS. have added band name to the User Review table)

 

Thanks

 

Odxsigma

  User_Profile

Id   int,  ( as primary Identity Key)

Name

City

Country

 

I have a second table called User Reviews

 User_Revews

Revew_Id   int ,  ( as primary Identity Key)

User_Id  int, ( I want this to contain the Id value in the User profile Table)

Band_Name 

Review_Details

 

Thanks a lot guys

 

odxsigma

 


0
odxsigma
12/10/2007 7:34:04 PM

I guess I'm not understanding what the problem is. I showed you how to retrieve the last identity from an INSERT statement. Then, with this ID, you can use it to INSERT your additional subrecords.


Thanks, Ed

Microsoft MVP - ASP/ASP.NET

0
ecbruck
12/10/2007 7:54:51 PM

 

Hi

Nope, its probably me being stupid, what I don’t get is how this works if a user creates a user profile on one day, then decides to create a band review a week later, would the code you suggest mean that they retrieve the last user ID created and not necessarily their own user ID, or is this not the case?

 Thanks for your patients?

 

Odxsigma


 

0
odxsigma
12/11/2007 12:00:08 AM

When the user logs in at a later point, you'll have to retrieve that user's ID somehow, and then you can use that in accessing the other table.


Thanks, Ed

Microsoft MVP - ASP/ASP.NET

0
ecbruck
12/11/2007 1:08:15 PM
Reply:

Similar Artilces:

How do i copy / insert a Primary Key value of one table into another table?
  Hi i have set up  two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile  table with Id set as the Primery Key as Identity) I want the user to be able to be able to return to their account at a later date and then post multiple reviews of different bands they have seen at a later date. I kept the tables in my example very simple so I could get my head around the concept, but generally, I want to connect the Id (PK) value in User_Profile table to the User_Id filed in the User_Review table, so every review th...

insert multiple values from one table into one column from another table?
Hi, I'm attempting to create an insert statement that gets the values from multiple columns and insert them into one column from another table, is there a simple solution to do this?Here is something similar to what i'm wanting done, just it's not the correct way to do it, i want value1, value2 and value3 from one table to be combined and put into the value1 column of another table, what am i doing wrong? INSERT INTO tablename2 (value1 + value2 + value3)SELECT value1FROM tablename1 thanks in advance! INSERT INTO tablename2 (SELECT val1 + val2 + val3 FROM tabl...

How to insert a table into another table and avoid primary key duplication ?
I have a table that I need to add every month to my master table, sometimes I get some rows in the monthly table that already are in the master, so, when I do: "insert into smi select * from smi_temp" I get a message saying that there was a primary key violation and no row is inserted. Is there a way to continue inserting the correct data ? Thank you for your help. >I have a table that I need to add every month to my master table, sometimes >I get some rows in the monthly table that already are in the master, so, >when I do: >"insert into smi sele...

CheckBoxList gets text from one table, send values to another table
I am working on making a CheckBoxList in a DetailsView that allows the user to check the boxes of the services they would like to receive. In my SQLDataSource, I have two tables that are involved in this. Columns "ServiceID" and "Name"are stored in one table, "Services," and the columns "UserID" and "ServiceID" are stored in another table, "UserServices." I want the CheckBoxList to grab the value and the name of all the services available and have a box for each with the name beside each box. Once the user clicks on each box they want and then click "Submit" I want all rows in table "Use...

is there an sql statement to insert data to one table and getting the data from another table
Ok I have a table that have general categories with just name and CatNo. Now what I want to know is when I create a client to move all the data in general categories to another table called categories that have the clientid to associate it with the client. I would appreciate for any help what is your question here??Shravan Addaypally MCP...

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?    The direct answer to your question is: You insert it like this:   ...

Get records from one table and insert into another one
I make that but I did not work, thanks  SqlCommand cmd2;cmd = new SqlCommand("SELECT CS_ID FROM Tbl_Conselheiros WHERE CS_TIPO IN (5,6,7)", conn); dr = cmd.ExecuteReader();  while (dr.Read()) {cmd2 = new SqlCommand("SP_PRES_GRAVAR", conn); cmd2.CommandType = CommandType.StoredProcedure;SqlParameter id = new SqlParameter("@ID_CONSE", SqlDbType.Int, 4);id.Value = Convert.ToInt32(dr[0]); cmd2.Parameters.Add(id);SqlParameter data2 = new SqlParameter("@DATA", SqlDbType.VarChar, 8); data2.Value = FormataData(); cmd2.Parameters.Add(data...

inserting data from one table to another table..
Hi Guys!  I am stuck on inserting data. Hope you will help me. I have two tables name Customer and Address; Customer(cust_id,name,add_fk);    fk=foreign key,  cust_id and add_id are primary key Address(add_id,street,city,zip); Now, how can I retrieve add_id from Address table and insert into Customer table ( in the add_fk field) using stored procedure with parameters? I am using Oracle 9i.  I would appreciate your help. Thanks! Are you using any of this data in between these actions? (eg: Are they going to reside in your page in a grid, or formview?) ...

Passing value from one control to another and inserting that value in a table
Hey all, sorry if the subject is confusing or has already been covered, but I have (obviously) a problem (at least one).This is using:Visual Studio 2005ASP.NET 2.0SQL Server 2005 ExpressVisual Basic I'm building a CRM system for my company and have run into a snag when attempting to add new customer records for a given client. The first thing that any CSR (or admin) must do upon accessing the CRM system is choose which client's customers they are actually working with (we have multiple clients and are ultimately responsible for providing customer service to the clients' end ...

Inserting Data Into Two Tables (Getting ID from Table 1 and inserting into Table 2)
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.  Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)  This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!  Insert Table1(Title,Description,Link,Whatever)Values(@title,@descri...

Insert multiple records from one table to another table
Hello. Got a quick problem. I need to post shopping cart items from a temperary table "ShopCart" to a permanent table "tbl_orders". Problem is, I cannot figure out how to iterate through the recordset to post the unique products from "ShopCart" to "tbl_orders". The code below inserts the first product-- but that's it. If there are 6 different products, it will insert the first product 6 times. Public Sub GetShopCart() Dim ordernumber = session("ordernumber") Dim objConn as new SqlConnection(strConn) Dim strSQL as Str...

inserting primary key of table into another
I am a newbbie, developing an asp.net application, i am using the built in login control though i had to cusmized it using aspnet_regsql.exe. the chanllenge i am having is that users can register quite with my appplication now, but i want to refernce the usersid in the built in asp_uers table to other tables in my database such that when a user logins in his photos which is in another table tbale will show and some other information. thanks You can do something like this certainly.Its hard to tell what level you are at from this post but you should start out with getting used to all the da...

can I compare one record from one table to another record in another table?
can I compare one record from one table to another record in another table? I need to test if this condition is met. My scenario is: i need to check one table for a value and compare it to a list of records. for example, table 1           columnrecord 1- applesrecord 2- orangesrecord 3- grapes table 2            col1   col2   col3    col4record 1- Null , Null, Null, grapes I need to compare table 2 records with a list of records from table 1. the fru...

Stored procedure for insert data from one table to another table
Hi,      I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table. Overall it is like storing the history of the table updation. How do i write a stored procedure for this? Anybody has done this before? Pls help me.   I think it's better to implement this logic in your application rather than from SQL side. Your application can accpect a sequence of parameters, and firstly insert them into the history; if ...

Web resources about - How do obtain Primary Key value from one table aid insert it into another table? - asp.net.getting-started

NHS primary care trust - Wikipedia, the free encyclopedia
An NHS primary care trust (PCT) is a type of NHS trust , part of the National Health Service in England . PCTs commission primary, community ...

Primary Health forecasts profit dip - AdelaideNow Search Search
PRIMARY Health Care forecasts underlying EBITDA and net profit after tax for FY2016 to be 5% below last year due to margin compression caused ...

Primary Health forecasts profit dip
PRIMARY Health Care forecasts underlying EBITDA and net profit after tax for FY2016 to be 5% below last year due to margin compression caused ...

David Muir and Martha Raddatz Will Moderate ABC’s Primary Debates
World News Tonight anchor David Muir and ABC’s chief global affairs correspondent Martha Raddatz will moderate two primary debates in the coming ...

Town that has no cell phone service loses its primary Internet provider
Main Street in Stewart, British Columbia. (credit: District of Stewart ) In Stewart, British Columbia, there is no cell phone service, and aside ...

Polls finds big lead for Van Hollen in Md. Senate primary
... with television advertising, Rep. Chris Van Hollen has opened a significant, double-digit lead in Maryland's closely watched Democratic primary ...

LOSING THE PRIMARY TO WIN THE GENERAL? Why ¡Jeb’s failed campaign hasn’t called it quits
... Bush said early on in his run for president that Republicans need to stop trying to win the argument, and that he would rather lose the primary ...

Even If Trump Disappears From The GOP Primary, There Are Republicans Who Will Support Bernie
... buzz not even 2 months ago about how popular Bernie is with Vermont Republicans and how he's tied with Trump and Carson in the Republican primary ...

New Hampshire Union Leader Endorses Christie for Republican Primary
... Union Leader late on Saturday endorsed New Jersey Governor Chris Christie for the state's first-in-the-nation Republican presidential primary, ...

The Coral announce 'Distance Inbetween,' first LP in 5 years; Bill Ryder-Jones released 'West Kirby County ...
by Bill Pearis The Coral It's been five years since we've heard anything new from The Coral, but they're now back and will release their eighth ...

Resources last updated: 12/3/2015 12:47:13 AM