I'm having a tough time translating the instructions I've seen online for utilizing
LAST_INSERT_ID()
in a .Net framework in WebMatrix.
Can someone show me how to use it with the specific code snippet below?
Each of my table's id's needs to connect to user_id on the customer_info1 table in order for me to pull up information by customer, so I know that I may also need to create something to help me carry it over from table to table.
I was able to get the customer_info1_user_id in the fit1 database to equal the user_id on the customer_info1 table but I ran into some difficulty with Response.Redirect, and no matter what I tried I couldn't get it to work for the subsequent pages (which
all have the Foreign Key of customer_info1_user_id) and would get the following error:
I had to change the code you suggested a bit in order for the first page to work (fit.cshtml which inserts into the fit1 database), but this did update the database properly and Redirect to the correct page.
var db = Database.Open("Demo1");
var customer_info1_user_id = db.GetLastInsertId();
var clothingsize="";
if(IsPost){
clothingsize =Request["clothingsize"];
var SQLINSERT = "INSERT INTO fit1 (customer_info1_user_id, clothingsize) VALUES (@0, @1)";
db.Execute(SQLINSERT, customer_info1_user_id, clothingsize);
Response.Redirect("flatter.cshtml");
I replaced Response.Redirect("flatter/" + customer_info1_user_id) because otherwise it was generating an unformatted page which wouldn’t accept any input (navigate to links or add any data to database). I also tried Response.Redirect("flatter.cshtml" +
customer_info1_user_id), but that resulted in an HTTP ERROR 404.0 - Not Found.
How would you suggest I modify my code so that I can I properly update the customer_info1_user_id Foreign Keys in every table to each equal the user_id on the customer_info1 table?
I didn't realisse you were doing things that way round... You should execute Database.GetLastInsertId() immediately after you have created a new record that has an IDENTITY column as its primary key. That way you get the value of the most recently created
record - the one you just created yourself. If you execute this on another page entirely, you won't get the value you expect if another new record was created in the meantime. Once you have the value for the new user, you can pass that around the application
using UrlData or similar as I said before.
Basically, you should already have obtained the user_id from the customer_info1 table when you created that user, and then passed the value to the page you are working with above.
Am I going about this the wrong way? From what I gathered from the way Workbench created the foreign keys it looked like each table has to have a way of knowing what the user_id is on the customer_info1 in order to connect. Should I instead be connecting
the customer_info1_user_id on each page to the id on the page before (i.e. fit1 to customer_info1, flatter1 to fit1, taste1 to flatter1, etc.? I can do that with my existing code, but is that what I should be doing here?
If not, and assuming that I somehow need to set the customer_info1_user_id on each page to equal user_id FROM customer_info1, how do I do that? I tried figuring out how to use UrlData to select the id#, but the instructions I found were about identifying
the URL name for SEO purposes (including one you wrote ;-) ), but I couldn't figure out how to apply that to my code.
I also tried using a query to select the info by using the db.GetLastInsertId() to select that specific customer's info from the previous page (i.e. fit1_id from
the flatter1 page), and then set the field customer_info1_user_id FROM flatter1 to equal customer_info1_user_id FROM fit1, but I'm still so new to this that I couldn't get my query to work properly.
var customer_info1_user_id = "select customer_info1_user_id FROM fit1 WHERE fit1_id=db.mysql_insert_id()";
I'm so sorry to ask you what I know is probably a basic question, but would you mind showing me how to implement whatever the best method is? I hadn't realized it would be this complicated to connect the tables to the user_id on the customer_info1 table
in order for me to pull up information by customer, and so far none of the tutorials I've used have shown me how to do it.
Each user is stored in a table that has a primary key which is autogenerated when you create their record. That value will act as a foreign key in any table that contains information related to the user. When you create a user, you need to know their ID
if you want to use that value to create related records in a separate table. You might have a page where you create a new user, and immediately transfer them to another page where they make some choices that you store in a separate table. In that case, your
code in the first page might look something like this:
var db = Database.Open("myDB");
db.Execute("Insert into users values (@0, @1, @2)", name, town, email);
var id = db.GetLastInsertId(); //got the id of the record you just added
Response.Redirect("~/otherpage/" + id);
Now in otherpage.cshtml, you get the id for the current user from UrlData and use this to create related records:
var id = UrlData[0];
var db = Database.Open("myDB");
db.Execute("Insert into choices values (@0, @1, @2)", id, choice1, choice2);
The table called choices will have it's own primary key column, but the second column is the UserId, which is a foreign key. This relates the records in choices to a record in users.
ChayaCooper
Member
121 Points
65 Posts
How do I use LAST_INSERT_ID() in Webmatrix to connect the tables
Dec 22, 2011 09:12 PM|LINK
I'm having a tough time translating the instructions I've seen online for utilizing LAST_INSERT_ID() in a .Net framework in WebMatrix.
Can someone show me how to use it with the specific code snippet below?
Each of my table's id's needs to connect to user_id on the customer_info1 table in order for me to pull up information by customer, so I know that I may also need to create something to help me carry it over from table to table.
@{
var clothingsize="";
if(IsPost){
clothingsize =Request["clothingsize"];
var SQLINSERT = "INSERT INTO fit1 (clothingsize) VALUES (@0)";
var db = Database.Open("Demo1");
db.Execute(SQLINSERT, clothingsize);
Response.Redirect("flatter.cshtml");
}
}
The specific settings (in MySqlWorkbench) for table 'fit1' are:
PRIMARY KEY (`fit_id`, `customer_info1_user_id`) ,
UNIQUE INDEX `customer_id_UNIQUE` (`fit_id` ASC) ,
INDEX `fk_fit1_customer_info1` (`customer_info1_user_id` ASC) ,
CONSTRAINT `fk_fit1_customer_info1`
FOREIGN KEY (`customer_info1_user_id` )
REFERENCES `c2f_122011`.`customer_info1` (`user_id` )
ON DELETE CASCADE
ON UPDATE CASCADE
Mikesdotnett...
All-Star
154864 Points
19861 Posts
Moderator
MVP
Re: How do I use LAST_INSERT_ID() in Webmatrix to connect the tables
Dec 23, 2011 04:41 AM|LINK
GetLastInsertId will return the ID of the most recently created record. You can get the new fit1 ID value like this:
var db = Database.Open("Demo1"); db.Execute(SQLINSERT, clothingsize); var Id = db.GetLastInsertId();Then, if you wanted to do something with that Id in flatter.cshtml, you can add it as UrlData or a querystirng value:
Response.Redirect("flatter/" + Id);You can get the value in flatter.cshtml fron UrlData[0]
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
ChayaCooper
Member
121 Points
65 Posts
Re: How do I use LAST_INSERT_ID() in Webmatrix to connect the tables
Dec 23, 2011 10:23 PM|LINK
I was able to get the customer_info1_user_id in the fit1 database to equal the user_id on the customer_info1 table but I ran into some difficulty with Response.Redirect, and no matter what I tried I couldn't get it to work for the subsequent pages (which all have the Foreign Key of customer_info1_user_id) and would get the following error:
I had to change the code you suggested a bit in order for the first page to work (fit.cshtml which inserts into the fit1 database), but this did update the database properly and Redirect to the correct page.
var db = Database.Open("Demo1"); var customer_info1_user_id = db.GetLastInsertId(); var clothingsize=""; if(IsPost){ clothingsize =Request["clothingsize"]; var SQLINSERT = "INSERT INTO fit1 (customer_info1_user_id, clothingsize) VALUES (@0, @1)"; db.Execute(SQLINSERT, customer_info1_user_id, clothingsize); Response.Redirect("flatter.cshtml");I replaced Response.Redirect("flatter/" + customer_info1_user_id) because otherwise it was generating an unformatted page which wouldn’t accept any input (navigate to links or add any data to database). I also tried Response.Redirect("flatter.cshtml" + customer_info1_user_id), but that resulted in an HTTP ERROR 404.0 - Not Found.
How would you suggest I modify my code so that I can I properly update the customer_info1_user_id Foreign Keys in every table to each equal the user_id on the customer_info1 table?
Mikesdotnett...
All-Star
154864 Points
19861 Posts
Moderator
MVP
Re: How do I use LAST_INSERT_ID() in Webmatrix to connect the tables
Dec 24, 2011 07:46 AM|LINK
I didn't realisse you were doing things that way round... You should execute Database.GetLastInsertId() immediately after you have created a new record that has an IDENTITY column as its primary key. That way you get the value of the most recently created record - the one you just created yourself. If you execute this on another page entirely, you won't get the value you expect if another new record was created in the meantime. Once you have the value for the new user, you can pass that around the application using UrlData or similar as I said before.
Basically, you should already have obtained the user_id from the customer_info1 table when you created that user, and then passed the value to the page you are working with above.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
ChayaCooper
Member
121 Points
65 Posts
Re: How do I use LAST_INSERT_ID() in Webmatrix to connect the tables
Dec 24, 2011 02:47 PM|LINK
Am I going about this the wrong way? From what I gathered from the way Workbench created the foreign keys it looked like each table has to have a way of knowing what the user_id is on the customer_info1 in order to connect. Should I instead be connecting the customer_info1_user_id on each page to the id on the page before (i.e. fit1 to customer_info1, flatter1 to fit1, taste1 to flatter1, etc.? I can do that with my existing code, but is that what I should be doing here?
If not, and assuming that I somehow need to set the customer_info1_user_id on each page to equal user_id FROM customer_info1, how do I do that? I tried figuring out how to use UrlData to select the id#, but the instructions I found were about identifying the URL name for SEO purposes (including one you wrote ;-) ), but I couldn't figure out how to apply that to my code.
I also tried using a query to select the info by using the db.GetLastInsertId() to select that specific customer's info from the previous page (i.e. fit1_id from the flatter1 page), and then set the field customer_info1_user_id FROM flatter1 to equal customer_info1_user_id FROM fit1, but I'm still so new to this that I couldn't get my query to work properly.
I'm so sorry to ask you what I know is probably a basic question, but would you mind showing me how to implement whatever the best method is? I hadn't realized it would be this complicated to connect the tables to the user_id on the customer_info1 table in order for me to pull up information by customer, and so far none of the tutorials I've used have shown me how to do it.
Mikesdotnett...
All-Star
154864 Points
19861 Posts
Moderator
MVP
Re: How do I use LAST_INSERT_ID() in Webmatrix to connect the tables
Dec 24, 2011 04:54 PM|LINK
Each user is stored in a table that has a primary key which is autogenerated when you create their record. That value will act as a foreign key in any table that contains information related to the user. When you create a user, you need to know their ID if you want to use that value to create related records in a separate table. You might have a page where you create a new user, and immediately transfer them to another page where they make some choices that you store in a separate table. In that case, your code in the first page might look something like this:
var db = Database.Open("myDB"); db.Execute("Insert into users values (@0, @1, @2)", name, town, email); var id = db.GetLastInsertId(); //got the id of the record you just added Response.Redirect("~/otherpage/" + id);Now in otherpage.cshtml, you get the id for the current user from UrlData and use this to create related records:
var id = UrlData[0]; var db = Database.Open("myDB"); db.Execute("Insert into choices values (@0, @1, @2)", id, choice1, choice2);The table called choices will have it's own primary key column, but the second column is the UserId, which is a foreign key. This relates the records in choices to a record in users.
Doe that help?
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
ChayaCooper
Member
121 Points
65 Posts
Re: How do I use LAST_INSERT_ID() in Webmatrix to connect the tables
Dec 24, 2011 08:20 PM|LINK
You're awesome! That makes perfect sense and I was finally able to get it to work :-D
The only problem I'm having now is that it's totally ignoring my beautiful layout and other dynamic script which is all referenced via
@{ Layout = "/Shared/_siteLayout.cshtml"; }Any idea how I can get both of these work at the same time?
Mikesdotnett...
All-Star
154864 Points
19861 Posts
Moderator
MVP
Re: How do I use LAST_INSERT_ID() in Webmatrix to connect the tables
Dec 25, 2011 06:38 AM|LINK
I can't see how the two issues are connected. Read this: http://www.asp.net/web-pages/tutorials/working-with-pages/3-creating-a-consistent-look and if that doesn't help, you should start a new thread.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter