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?

Id   int,  ( as primary Identity Key)

Name

City

Country

I have a second table called User Reviews

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

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();
	}
}

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

Id   int,  ( as primary Identity Key)

Name

City

Country

I have a second table called User Reviews

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

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.

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

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.